New column in DataFrame based on rows and columns from another DataFrame

Question

I have two pandas DataFrames.

df1

    col1    col2
0   E8      K4
1   E6      K3
2   E8      K4
3   E8      K4
4   E8      K2

df2

    group   K1  K2  K3  K4
0   E6      -2  -90 24  -23
1   E7      94  -34 3   22
2   E8      7   30  100 -9

In df2, for E8 K4, the value is

df2.loc[df2['group'] == 'E8']['K4'].item()
-9

But, how could I create a new column in df1 based on df2?

The result will look like this:

    col1    col2    col3
0   E8      K4      -9
1   E6      K3      24
2   E8      K4      -9
3   E8      K4      -9
4   E8      K2      30

jezrael · Accepted Answer · 2021-01-19 08:38:40Z

4

Use DataFrame.join with reshaped values by DataFrame.set_index and DataFrame.stack for MultiIndex Series:

df = df1.join(df2.set_index('group').stack().rename('col3'), on=['col1','col2'])
print (df)
  col1 col2  col3
0   E8   K4    -9
1   E6   K3    24
2   E8   K4    -9
3   E8   K4    -9
4   E8   K2    30

Another idea with DataFrame.merge, DataFrame.melt and rename:

df = df1.merge(df2.melt('group', var_name='col2', value_name='col3')
                  .rename(columns={'group':'col1'}), on= ['col1','col2'], how='left')
      
print (df)
  col1 col2  col3
0   E8   K4    -9
1   E6   K3    24
2   E8   K4    -9
3   E8   K4    -9
4   E8   K2    30

edited Jan 19, 2021 at 8:38

answered Jan 19, 2021 at 8:08

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ehsan Tabatabaei · Accepted Answer · 2021-01-19 08:26:20Z

As Pandas documentation suggests, you can use melt function for df2 to get your desired output easily:

df2 = pd.DataFrame({'group': {0: 'E6', 1: 'E7', 2: 'E8'},
               'K1': {0: -2, 1: 94, 2: 7},
               'K2': {0: -90, 1: -34, 2: 30},
               'K3': {0: 24, 1: 3, 2: 100},
               'K4': {0: -23, 1: 22, 2: -9}})

pd.melt(df2, id_vars=['group'], value_vars=['K1', 'K2', 'K3', 'K4'])

returns:

group variable  value
0     E6       K1     -2
1     E7       K1     94
2     E8       K1      7
3     E6       K2    -90
4     E7       K2    -34
5     E8       K2     30
6     E6       K3     24
7     E7       K3      3
8     E8       K3    100
9     E6       K4    -23
10    E7       K4     22
11    E8       K4     -9

Collectives™ on Stack Overflow

New column in DataFrame based on rows and columns from another DataFrame

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related