CODEWITHSUNDEEP
pythonpandas
Jay Hack
Jay Hack

Reputation: 139

Joining MultiIndexed Pandas Dataframes for Another MultiIndexed Dataframe

I have two pandas dataframes, each of which has a multi-index for the columns, like so:

d1 = {('a', 'c'):1, ('a', 'd'):1, ('b', 'c'):1, ('b', 'd'):1}
d2 = {('a', 'c'):2, ('a', 'd'):2, ('b', 'c'):2, ('b', 'd'):2}
df1 = pd.DataFrame ([d1, d2])
df2 = pd.DataFrame ([d2, d2]).map (lambda x: x*2)

df1.columns = pd.MultiIndex.from_tuples (df1.columns)
df2.columns = pd.MultiIndex.from_tuples (df2.columns)

In [62]: df1
Out[62]: 
   a     b   
   c  d  c  d
0  1  1  1  1
1  1  1  1  1

[2 rows x 4 columns]

In [63]: df2
Out[63]: 
   a     b   
   c  d  c  d
0  2  2  2  2
1  2  2  2  2

[2 rows x 4 columns]

I would like to merge the two dataframes together to achieve a heirarchical index on the columns where the top level is the name of the dataframe it originated from, followed by the current column names. That is,

In [64]: merged_df.df1.a.c
Out[64]: 
0    1
1    1
Name: c, dtype: int64

In [64]: merged_df.df2.a.c
Out[64]: 
0    2
1    2
Name: c, dtype: int64

How might I accomplish this? Thanks!

Upvotes: 1

Views: 128

Answers (1)

Jeff
Jeff

Reputation: 128948

You shouldn't use map on a frame, FYI (which doesn't exist anyhow), you could use applymap, but MUCH more efficient to use a vectorized operation

In [12]: df2 = pd.DataFrame ([d2, d2])**2

In [14]: df2.columns = pd.MultiIndex.from_tuples (df2.columns)

In [15]: df2
Out[15]: 
   a     b   
   c  d  c  d
0  4  4  4  4
1  4  4  4  4

[2 rows x 4 columns]

In [16]: df1
Out[16]: 
   a     b   
   c  d  c  d
0  1  1  1  1
1  2  2  2  2

[2 rows x 4 columns]

Concat together and use the keys argument

In [18]: concat([df1,df2],keys=['df1','df2'],axis=1)
Out[18]: 
   df1           df2         
     a     b       a     b   
     c  d  c  d    c  d  c  d
0    1  1  1  1    4  4  4  4
1    2  2  2  2    4  4  4  4

[2 rows x 8 columns]

In [19]: concat([df1,df2],keys=['df1','df2'],axis=1).df2.a.c
Out[19]: 
0    4
1    4
Name: c, dtype: int64

Upvotes: 3

Related Questions