CODEWITHSUNDEEP
pythonpandasdataframe
asutherland
asutherland

Reputation: 2969

Set values of pandas dataframe that match another dataframe on a specific column

I have dataframe A that has columns like "id, Title, Author, Publish Date, Copies Sold" and contains a list of all the books, but the copies sold column is set to 0 for every book.

Then dataframes B,C,D,E,etc has columns "id, Copies Sold", with the true copies sold value but each only have them for a subset of the books.

I'd like to populate dataframe A with the "copies sold" from each of the dataframes B,C,D,E so that dataframe A has all of the data on copies sold tabulated in a single table.

However, every assignment from B,C,D,E to A that I try has A's copies sold field showing as NaN.

What is the proper way to do this assignment where the "copies sold" field in A is written with the value of the copies sold field in B for each id in B that matches an id in A?

Upvotes: 1

Views: 1206

Answers (2)

Ajay A
Ajay A

Reputation: 1068

Try this, this is updation to @IoaTzimas's answer

A = A.merge(pd.concat([B,C,D,E]), on='id', how='left')

Upvotes: 1

IoaTzimas
IoaTzimas

Reputation: 10624

You can concat B,C,D,E and then merge with A.See below. If B,C,D,E include other values except from the ones that have 'copies sold' as not nan, you can filter them (let me know in that case).

A.merge(pd.concat([B,C,D,E]), on='id')

From this result, just select the columns that you want, in order to get the final desired output

Upvotes: 3

Related Questions