Pandas - Map multiple rows from another DF to multiple columns

Question

I have two dataframes and I'm trying to migrate data from one df1 to my main df.

They share a common key - and I want to store the values from a df1 row into a df column. This I could do.. however df1 can have multiple rows (max 5) that share the common key and I would like to store each row in an individual column.

Using an example:

df

index  key   datacol 
  1    1AA    data1 
  2    1AB    data2
  3    1AC    data3

df1

index  key   newdata 
  1    1AA    new1
  2    1AB    new2
  3    1AB    new3
  4    1AB    new4 
  5    1AC    new5
  6    1AC    new6

Output:

index  key   datacol newcol1 newcol2 newcol3
  1    1AA    data1   new1
  2    1AB    data2   new2    new3    new4
  3    1AC    data3   new5    new6

Appreciate your assistance.

rafaelc · Accepted Answer · 2018-07-25 01:36:01Z

1

IIUC, can do

d = df2.groupby('key', as_index=False).agg(list)
x = pd.concat([d.newdata.apply(pd.Series), d.key],1).set_index('key')
pd.merge(df.set_index('key'),x, right_index=True, left_index=True)

        index   datacol  0      1       2
key                 
1AA      1      data1    new1   NaN     NaN
1AB      2      data2    new2   new3    new4
1AC      3      data3    new5   new6    NaN

answered Jul 25, 2018 at 1:36

rafaelc

59.4k15 gold badges64 silver badges87 bronze badges

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

Comments

rafaelc · Accepted Answer · 2018-07-25 02:01:42Z

0

You can merge firstly

newdf=df.merge(df1,how='right')

Then using cumcount create the help key, then the question is looks like pivot

finaldf= newdf.assign(helpkey=newdf.groupby('key').cumcount()).set_index(['key','datacol','helpkey']).newdata.unstack(fill_value='')
finaldf
Out[410]: 
helpkey         0     1     2
key datacol                  
1AA data1    new1            
1AB data2    new2  new3  new4
1AC data3    new5  new6

edited Jul 25, 2018 at 2:01

rafaelc

59.4k15 gold badges64 silver badges87 bronze badges

answered Jul 25, 2018 at 1:32

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

Pandas - Map multiple rows from another DF to multiple columns

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related