CODEWITHSUNDEEP
pythonpandas
stormtrooper12
stormtrooper12

Reputation: 381

Join two dataframes. Add a column value if index value of first matches the second

I have two dataframes, call them 1st and 2nd. 1st has index - 'customer'. 2nd also has index - 'customer' with some similar and some different values. 1st has columns 'P' and 'Q'. 2nd also has columns by the name 'P' and 'Q'. I want to join both dataframes like, if any index value of 1st matches index value of 2nd, then add corresonding values of 'P' and 'Q' from B to 'P' and 'Q' of 1st. Return dataframe 1st

example 1st dataframe is:

customer    P   Q
A         0.5   4
B         0.4   6
C         0.3   5
D         0.7   7

2nd dataframe is:

customer    P   Q
B           4   20
D           5   21
E           6   22
F           7   23

Output should be:

Customer    P   Q   
A          0.5  4   
B          4.4  26  (6+20)
C          0.3  5   
D          5.7  28  (7+21)

Upvotes: 0

Views: 66

Answers (1)

piRSquared
piRSquared

Reputation: 294258

np.random.seed([3,1415])
A = pd.DataFrame(np.random.rand(3, 2),
                 pd.Index(list('abc'),name='customer'),
                 list('PQ'))
B = pd.DataFrame(np.random.rand(3, 2),
                 pd.Index(list('bcd'),name='customer'),
                 list('PQ'))

A

enter image description here

B

enter image description here

(A + B).dropna()

enter image description here

Solution

IIUC you want to add to A, the values of B where B has a common index, otherwise just take values of A.

A.add(B, fill_value=0).reindex_like(A)

enter image description here

Upvotes: 1

Related Questions