I have two dfs:
df1 = pd.DataFrame.from_dict({'1': [3, 2, 1, 0], '2': [5, 6, 7, ""]})
df2 = pd.DataFrame.from_dict({'index': [5, 6, 3, 2, 1, 0, 7], 'something': ['a', 'b', 'c', 'd', 'a1', 'b2', 'x']})
I would like to add colname from df1 based on if ‘index’ is in column 1 or 2 in df1. Desired output is:
result = pd.DataFrame.from_dict({'index': [5, 6, 3, 2, 1, 0, 7], 'something': ['a', 'b', 'c', 'd', 'a1', 'b2', 'x'], 'col_df1' : [2, 2, 1, 1, 1, 1, 2]})
Is there an elegant way, how to do this?
df1
1 2
0 3 5
1 2 6
2 1 7
3 0
df2
index something
0 5 a
1 6 b
2 3 c
3 2 d
4 1 a1
5 0 b2
6 7 x
result
index something col_df1
0 5 a 2
1 6 b 2
2 3 c 1
3 2 d 1
4 1 a1 1
5 0 b2 1
6 7 x 2
>Solution :
You can merge df2 with the result of melt on df1:
# first let's ensure that the column indices are integers
df1.columns = df1.columns.astype(int)
# then melt and merge
df2.merge(df1.melt(var_name='col_df1', value_name='index'), on='index')
output:
index something col_df1
0 5 a 2
1 6 b 2
2 3 c 1
3 2 d 1
4 1 a1 1
5 0 b2 1
6 7 x 2