I have two multi level column dataframes.
import pandas as pd
df1 = pd.DataFrame({'col1':[1,1,2,2],'col2':[10,10,20,20]})
df1.columns = pd.MultiIndex.from_product([['df1_labels'],df1.columns])
df1
df1_labels
col1 col2
0 1 10
1 1 10
2 2 20
3 2 20
df2 = pd.DataFrame({'col3':[100,200],'col4':[10,20]})
df2.columns = pd.MultiIndex.from_product([['df2_labels'],df2.columns])
df2
df2_labels
col3 col4
0 100 10
1 200 20
I would like to merge them on the values in colunm 'df1_labels','col2' in df1 and column 'df2_labels','col2' in df2. My expected result would be:
df1_labels df2_labels
col1 col2 col3 col4
0 1 10 100 10
1 1 10 100 10
2 2 20 200 20
3 2 20 200 20
I have tried this:
df3 = pd.merge(df1,df2, left_on=('df1_labels','col2'), right_on=('df2_labels','col4'))
df3
And this:
df3 = pd.merge(df1,df2, left_on=['df1_labels','col2'], right_on=['df2_labels','col4'])
df3
Both giving me the following error:
ValueError: The column label 'df2_labels' is not unique. For a multi-index, the label must be a tuple with elements corresponding to each level.
I must be doing something wrong syntactically. With single column levels it works:
pd.merge(pd.DataFrame({'col1':[1,1,2,2],'col2':[10,10,20,20]}),
pd.DataFrame({'col3':[100,200],'col4':[10,20]}),
left_on='col2',right_on='col4')
col1 col2 col3 col4
0 1 10 100 10
1 1 10 100 10
2 2 20 200 20
3 2 20 200 20
Any help would be welcomed!
np.repeat?