How to map two data frames in python?

Question

I have a data frame which can has n number of P_Id values for each Id.

X = Id    P_Id     Value
    1      a_1      56
    1      a_2      76
    2      a_1      67
    2      a_2      78
    2      a_3      98

I have another data frame

Y =  Id  Name 
     1    Erwin
     2    Joseph
     3     Nik

I want the out put data frame to be

Output =  Id  a_1  a_2   a_3  Name
          1    56   76   nan   Erwin
          2    67   78   98    Joseph

ThePyGuy · Accepted Answer · 2021-06-17 13:31:18Z

3

Inner merge x, and y, then pivot this merged dataframe,then merge back to the second dataframe, finally, you can reset the index, .

x.merge(y, how='inner', on='Id').pivot(columns='P_Id', values='Value').merge(y, on='Id').reset_index()

OUTPUT:

   Id   a_1   a_2   a_3    Name
0   1  56.0  76.0   NaN   Erwin
1   2  67.0  78.0  98.0  Joseph

edited Jun 17, 2021 at 13:31

answered Jun 17, 2021 at 13:04

ThePyGuy

18.5k5 gold badges24 silver badges55 bronze badges

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

2 Comments

Andrew Over a year ago

Error - No common columns to perform merge on. Merge options: left_on=None, right_on=None, left_index=False, right_index=False

ThePyGuy Over a year ago

That's because Id is index in both the dataframes, if that's the case, either reset the indices of both the dataframes, or pass on='Id' to the merge function.

Collectives™ on Stack Overflow

How to map two data frames in python?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related