How to combine multiple dataframe values having set of common column values?

Question

I have a dataframe df1 which consists of string values in ColA, These ColA values will have duplicates as shown.

Now my df2 will have same set of values in ColA, which are not repeated. So I want to append the ColD values of df2 to my original df1. (60 will repeat twice for app, 20 will repeat thrice..and so on..). Similarly I want to append ColE values of df3 to my df1

Can it be done by transposing ColA so it becomes column names and then can we append values to it, or can the ColA be set as indexes and then we need to append values, or is there any other way?

import numpy as np
import pandas as pd
import io

data1='''
ColA,ColB,ColC
app,5,2
app,6,3
book,10,6
book,4,8
book,9,0
car,7,8
car,1,3
'''

data2='''
ColA,ColD
app,60
book,20
car,8
'''

data3='''
ColA,ColE
app,3
book,6
car,9
'''

df1=pd.read_csv(io.StringIO(data1))
df2=pd.read_csv(io.StringIO(data2))
df3=pd.read_csv(io.StringIO(data3))

Expected output: df1

ColA ColB ColC ColD ColE
app     5    2   60    3
app     6    3   60    3
book   10    6   20    6
book    4    8   20    6
book    9    0   20    6
car     7    8    8    9
car     1    3    8    9

I think Jerzael's answer from this post would do the trick actually. https://stackoverflow.com/a/34674499/9375102 — Umar.H, Jun 01 '20 at 16:56

score 3 · Accepted Answer · answered Jun 01 '20 at 16:54

You can merge:

df1.merge(df2, on='ColA', how='left').merge(df3, on='ColA', how='left')

Output:

   ColA  ColB  ColC  ColD  ColE
0   app     5     2    60     3
1   app     6     3    60     3
2  book    10     6    20     6
3  book     4     8    20     6
4  book     9     0    20     6
5   car     7     8     8     9
6   car     1     3     8     9

How to combine multiple dataframe values having set of common column values?

1 Answers1