CODEWITHSUNDEEP
pythonpandasdataframejoinmerge
Chakrit Udomsilp
Chakrit Udomsilp

Reputation: 33

Pandas DataFrame: How to merge dataframe with multiple index and single index

I've a problem with merging 2 dataframe with multiple index in dataframe1 and single index in dataframe2. I have dataframe1

ID    description
1          A
1          B
1          C
2          D
2          E

dataframe2

ID     price
1       100
2       50

expected output

ID    description    price
1         A           100
1         B           NaN
1         C           NaN
2         D           50
2         E           NaN

Thank you and sorry for my English.

Upvotes: 3

Views: 118

Answers (1)

Umar.H
Umar.H

Reputation: 23099

you can use .loc with a boolean to drop duplicates and use map to find the values from df2

This approach assumes you only have one target column.

df1.loc[df1.drop_duplicates(subset=['ID'],keep='first').index, 
                            'price'] =  df1['ID'].map(df2.set_index('ID')['price'])


   ID description  price
0   1           A  100.0
1   1           B    NaN
2   1           C    NaN
3   2           D   50.0
4   2           E    NaN

.groupby('id').cumcount() & .merge

Another, more dynamic method would be to create a sequence on your ID then join on both keys.

df1['sequence'] = df1.groupby('ID').cumcount()
df2['sequence'] = df2.groupby('ID').cumcount()

df3 = pd.merge(df1,df2,on=['ID','sequence'],how='left').drop('sequence',1)

   ID description  price
0   1           A  100.0
1   1           B    NaN
2   1           C    NaN
3   2           D   50.0
4   2           E    NaN

print(df1)

   ID description  sequence
0   1           A         0
1   1           B         1
2   1           C         2
3   2           D         0
4   2           E         1

print(df2)

   ID  price  sequence
0   1    100         0
1   2     50         0

Upvotes: 3

Related Questions