I have two pandas dataframes and I am trying to assign values from the second one to a new column of the first one. The problem is that my first dataset has an extra level of multiindex compared to the second one. I would like the assignement to "ignore" this extra axis by assigning multiple times the same values if the common index match.
As shown in this example, it is actually working when both dataframes are multiindexed, but not when one is multiindexed and the other one not. What can I do ? Is there an easy way to transform an index to a MultiIndex ? My dataframes are quite big and I would obviously love to avoid loops.
Thank you in advance
import pandas as pd
index1 = [["foo", "foo", "foo", "foo", "bar", "bar", "bar", "bar"], ["x", "x","z","z","x","x","z","z"], ["a","b","a","b","a","b","a","b"]]
df1 = pd.DataFrame(range(1,9), index=index1)
index2 = [["foo", "foo", "bar", "bar"], ["x","z","x","z"]]
df2 = pd.DataFrame(range(1,5), index=index2)
index3 = ["foo", "bar"]
df3 = pd.DataFrame(range(1,3), index=index3)
df1[1] = df2[0] #works
df2[1] = df3[0] #does not work !
Output:
df1:
0
foo x a 1
b 2
z a 3
b 4
bar x a 5
b 6
z a 7
b 8
df2:
0
foo x 1
z 2
bar x 3
z 4
df3:
0
foo 1
bar 2
df1[1] = df2[0] #ok:
0 1
foo x a 1 1
b 2 1
z a 3 2
b 4 2
bar x a 5 3
b 6 3
z a 7 4
b 8 4
df2[1] = df3[0]: #not ok
0 1
foo x 1 NaN
z 2 NaN
bar x 3 NaN
z 4 NaN