CODEWITHSUNDEEP
pythonpandas
vojtam
vojtam

Reputation: 1225

How to merge in pandas based on column names

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

Upvotes: 0

Views: 62

Answers (1)

mozway
mozway

Reputation: 260470

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

Upvotes: 2

Related Questions