Merging multiple data frames from a list into a single data frame

There are instances where we need to store the result of an operation into a list and then consolidate the results into a single data frame.In this blog we will look at how to perform this¶

Step 1: Installing libraries¶

In [1]:

import pandas as pd
import numpy as np

Step 2: Creating dummy data frames¶

In [7]:

df1=pd.DataFrame([["Day1","Week 1",np.random.rand()],
                 ["Day2","Week 1",np.random.rand()],
                 ["Day3","Week 1",np.random.rand()],
                 ["Day4","Week 1",np.random.rand()],
                 ["Day5","Week 1",np.random.rand()]])
df1.columns=['Day','Week Number','Sales_Vs_Inventory']
df1

Out[7]:

	Day	Week Number	Sales_Vs_Inventory
0	Day1	Week 1	0.104135
1	Day2	Week 1	0.754042
2	Day3	Week 1	0.539883
3	Day4	Week 1	0.072749
4	Day5	Week 1	0.127232

In [8]:

df2=pd.DataFrame([["Day1","Week 2",np.random.rand()],
                 ["Day2","Week 2",np.random.rand()],
                 ["Day3","Week 2",np.random.rand()],
                 ["Day4","Week 2",np.random.rand()],
                 ["Day5","Week 2",np.random.rand()]])
df2.columns=['Day','Week Number','Sales_Vs_Inventory']
df2

Out[8]:

	Day	Week Number	Sales_Vs_Inventory
0	Day1	Week 2	0.015982
1	Day2	Week 2	0.000074
2	Day3	Week 2	0.304911
3	Day4	Week 2	0.513745
4	Day5	Week 2	0.196606

Step 3:Storing df1 and df2 in a list¶

In [10]:

l1=[df1,df2]
l1

Out[10]:

[    Day Week Number  Sales_Vs_Inventory
 0  Day1      Week 1            0.104135
 1  Day2      Week 1            0.754042
 2  Day3      Week 1            0.539883
 3  Day4      Week 1            0.072749
 4  Day5      Week 1            0.127232,
     Day Week Number  Sales_Vs_Inventory
 0  Day1      Week 2            0.015982
 1  Day2      Week 2            0.000074
 2  Day3      Week 2            0.304911
 3  Day4      Week 2            0.513745
 4  Day5      Week 2            0.196606]

Step 4: Merging the data frames within l1¶

In [21]:

pd.concat(l1,axis=0)

Out[21]:

	Day	Week Number	Sales_Vs_Inventory
0	Day1	Week 1	0.104135
1	Day2	Week 1	0.754042
2	Day3	Week 1	0.539883
3	Day4	Week 1	0.072749
4	Day5	Week 1	0.127232
0	Day1	Week 2	0.015982
1	Day2	Week 2	0.000074
2	Day3	Week 2	0.304911
3	Day4	Week 2	0.513745
4	Day5	Week 2	0.196606