Creating nested dataframes with multiple dataframes

Question

I have multiple dataframes, the following are only 2 of them:

print(df1)

Date                 A  B  C  
2019-10-01 00:00:00  2  3  1  
2019-10-01 01:00:00  5  1  6  
2019-10-01 02:00:00  8  2  4  
2019-10-01 03:00:00  3  6  5  



print(df2)

Date                 A  B  C  
2019-10-01 00:00:00  9  4  2  
2019-10-01 01:00:00  3  2  4  
2019-10-01 02:00:00  6  5  2  
2019-10-01 03:00:00  3  6  5

All of them have same index and columns. I want to create dataframe like this:

    Date                df1  df2
A   2019-10-01 00:00:00   2    9 
    2019-10-01 01:00:00   5    3  
    2019-10-01 02:00:00   8    6
    2019-10-01 03:00:00   3    3  
B   2019-10-01 00:00:00   3    4  
    2019-10-01 01:00:00   1    2  
    2019-10-01 02:00:00   2    5  
    2019-10-01 03:00:00   6    6  
C   2019-10-01 00:00:00   1    2  
    2019-10-01 01:00:00   6    4  
    2019-10-01 02:00:00   4    2  
    2019-10-01 03:00:00   5    5

I have to apply this process to 30 dataframes(their index and columns are same), so I want to write a for loop in order to achieve this dataframe. How can I do that?

jezrael · Accepted Answer · 2019-12-20 11:26:49Z

1

Reshape each DataFrame of list of DataFrames by DataFrame.set_index with DataFrame.unstack and then concat, last change columns names with lambda function:

dfs = [df1,df2]
df = (pd.concat([x.set_index('Date').unstack() for x in dfs], axis=1)
       .rename(columns=lambda x: f'df{x+1}'))
print (df)
                       df1  df2
  Date                         
A 2019-10-01 00:00:00    2    9
  2019-10-01 01:00:00    5    3
  2019-10-01 02:00:00    8    6
  2019-10-01 03:00:00    3    3
B 2019-10-01 00:00:00    3    4
  2019-10-01 01:00:00    1    2
  2019-10-01 02:00:00    2    5
  2019-10-01 03:00:00    6    6
C 2019-10-01 00:00:00    1    2
  2019-10-01 01:00:00    6    4
  2019-10-01 02:00:00    4    2
  2019-10-01 03:00:00    5    5

If want some custom columns names in final DataFrame create list with same size like length of dfs and add parameter keys:

dfs = [df1,df2]
names = ['col1','col2']

df = pd.concat([x.set_index('Date').unstack() for x in dfs], keys=names, axis=1)
print (df)
                       col1  col2
  Date                           
A 2019-10-01 00:00:00     2     9
  2019-10-01 01:00:00     5     3
  2019-10-01 02:00:00     8     6
  2019-10-01 03:00:00     3     3
B 2019-10-01 00:00:00     3     4
  2019-10-01 01:00:00     1     2
  2019-10-01 02:00:00     2     5
  2019-10-01 03:00:00     6     6
C 2019-10-01 00:00:00     1     2
  2019-10-01 01:00:00     6     4
  2019-10-01 02:00:00     4     2
  2019-10-01 03:00:00     5     5

edited Dec 20, 2019 at 11:26

answered Dec 20, 2019 at 11:13

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

JuniorESE Over a year ago

What if the column names are different? I mean dfs = [Buying, Selling, Amount,..] something like that.

jezrael Over a year ago

@JuniorESE - do you think different for each DataFrame?

JuniorESE Over a year ago

No, all dataframe has same columns but only their names are different. Also column names should be df 's names not col1 and col2.

jezrael Over a year ago

@JuniorESE - Unfortunately in python is necessary specify this columns names like in second solution in list, here called names

JuniorESE Over a year ago

I want to resample Date column as monthly. Missing months will be zero. How can I do that? @ jezrael

Collectives™ on Stack Overflow

Creating nested dataframes with multiple dataframes

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related