I’m new to Python Pandas and struggling with the following problem for a while now.
The following dataframe df1 values show the indices that are coupled to the values of df2 that should be called
JavaScript
x
5
1
Name1 Name2 Name160 Name1612
0 62 18 NaN 753
1 79 46 NaN 54
2 3 26 NaN 05
df2 contains the values that belong to the indices that have to be called.
JavaScript
1
13
13
1
Name1 Name2 Name160 Name1612
0 152.0 204.0 NaN 164.03
1 175.0 308.0 NaN 571.04
2 252.0 695.0 NaN 577.05
3 379.0 722.0 NaN 655.06
4 398.0 834.0 NaN 675.07
.. 8
213 NaN NaN NaN NaN9
214 NaN NaN NaN NaN10
215 NaN NaN NaN NaN11
216 NaN NaN NaN NaN12
217 NaN NaN NaN NaN13
For example, df1 shows the value ‘0’ in column ‘Name161’. Then df3 should show the value that is listed in df2 with index 0. In this case ‘164’.
Till so far, I got df3 showing the first 3 values of df2, but of course that not what I would like to achieve.
JavaScript
1
14
14
1
Input:2
3
df3 = df1*04
for c in df1.columns:5
df3[c]= df2[c]6
print(df3)7
8
Output:9
10
Name1 Name2 Name160 Name16111
0 152.0 204.0 NaN 164.012
1 175.0 308.0 NaN 571.013
2 252.0 695.0 NaN 577.014
Any help would be much appreciated, thanks!
Advertisement
Answer
Use DataFrame.stack with Series.reset_index for reshape both DataFrames, then merging by DataFrame.merge with left join and last pivoting by DataFrame.pivot:
JavaScript
1
24
24
1
#change index values for match by sample data in df22
print (df1)3
Name1 Name2 Name160 Name1614
0 2 4 NaN 45
1 0 213 NaN 2166
2 3 2 NaN 07
8
df11 = df1.stack().reset_index(name='idx')9
df22 = df2.stack().reset_index(name='val')10
11
df = (df11.merge(df22, 12
left_on=['idx','level_1'], 13
right_on=['level_0','level_1'], 14
how='left')15
.pivot('level_0_x','level_1','val')16
.reindex(df1.columns, axis=1)17
.rename_axis(None)18
)19
print (df)20
Name1 Name2 Name160 Name16121
0 252.0 834.0 NaN 675.022
1 152.0 NaN NaN NaN23
2 379.0 695.0 NaN 164.024