CODEWITHSUNDEEP
pythonpandas
whatf
whatf

Reputation: 6458

Pandas Populate all rows that match with index of another dataframe

Consider a data frame

Major = Date, Sales Cost, Sales Channel

There are multiple types of Sales Channels.

There is another data frame

Minor = Date, Agent Type, Agent Id

For a given date there will be multiple columns in Major.

However, only one column corresponding to a date in Minor.

Two new columns need to be added to Major, based on Date.

Here is what I have tried

Major.join(Minor.set_index('Date'), on='Date')
print(Major.columns)

This does not add new columns to Major, based on those that are present in Minor.

To simplify : For all date in a larger dataframe, add new columns from a smaller data frame, when date is matching.

Major

Date    Sales Cost  Sales Channel
01/12/2020  5000    Alpha
01/12/2020  10000   Alpha
01/13/2020  13000   Beta
01/14/2020  23000   Beta
01/15/2020  24000   Gamma
01/16/2020  1200    Alpha

Minor

Date    Agent Type  Agent Id
01/12/2020  Online  1234
01/12/2020  Offline 1233
01/13/2020  Online  1245
01/14/2020  Online  1232
01/15/2020  Offline 1256
01/16/2020  Offline 1234

Merged for first date

Date    Sales Cost  Sales Channel   Agent Type  Agent Id
01/12/2020  5000    Alpha   Online  1234
01/12/2020  5000    Alpha   Online  1234
01/12/2020  5000    Alpha   Offline 1233
01/12/2020  5000    Alpha   Offline 1233

Upvotes: 0

Views: 872

Answers (1)

IoaTzimas
IoaTzimas

Reputation: 10624

Just merge the datasets:

result = Major.merge('Minor', on='Date', how='left')
result.set_index('Date')

Upvotes: 1

Related Questions