Combining two dataframes so that the values in one dataframe become headers in the other

user_11917441 0
Viewed 33

My first data frame d1 is something like this.

    num value
0   1   229
1   2   203
2   3   244

The second one, d2:

    num person  cash
0   1   person1 29
1   1   person2 81
2   2   person1 17
3   2   person2 75
4   3   person1 62
5   3   person3 55

And I would like to combine them based on num in a way that inputs of person become headers of new columns in d1. And the new columns are filled with the values cash from d2.

    num value person1 person2 person 3
0   1   229   29      81      0
1   2   203   17      75      0
2   3   244   62      0       55

Is it some kind of combination between merge() and unstack()? The example seems trivial, most likely I was not able to describe it sufficiently well when googling the answer.

pandas dataframe python
1 Answers

Try pivot df2 and merge:

df1.merge(df2.pivot('num','person','cash'),
          on='num')

Output:

   num  value  person1  person2
0    1    229       29       81
1    2    203       17       75
2    3    244       62       55

Edit: For the updated data, same idea but use set_index().unstack() instead of pivot. This helps fill the missing values with 0 easier.

df1.merge(df2.set_index(['num','person'])['cash']
              .unstack(fill_value=0),
          on='num')

Output:

   num  value  person1  person2  person3
0    1    229       29       81        0
1    2    203       17       75        0
2    3    244       62        0       55
user_42384080
Related