0

I have two dataframes

DF1:

EmployeeID CollectiveBargainingUnit BusinessGroup ProfitCenter Eligible
1          12A                      A12           UGZ.         
2          17A                      B12           MGZ.         
3          18A                      C12           DGZ.         
4          19A                      D12           XGZ.

DF2:

CollectiveBargainingUnit BusinessGroup ProfitCenter Eligible
12A                      A12           UGZ.         True
17A                      B12           MGZ.         False
18A                      C12           DGZ.         False
19A                      D12           XGZ.         True
12A                      A13           UGZ.         False
27A                      C12           MKZ.         True
32A                      C22           DGZ.         True
19A                      D99           XGZ.         False

What would I would like to accomplish is this

EmployeeID CollectiveBargainingUnit BusinessGroup ProfitCenter Eligible
1          12A                      A12           UGZ.         True
2          17A                      B12           MGZ.         False
3          18A                      C12           DGZ.         False
4          19A                      D12           XGZ.         True

Based off of multiple columns between the two Dataframes (CollectiveBargainingUnit BusinessGroup ProfitCenter) I'd like to access it's eligibility in DF2 and update it in DF1

Where I am left off is:

eligibility_map=df2.groupby(["BusinessGroup","ProfitCenter","CollectiveBargainingUnit"]).first()["Eligible"]

df1["Eligible"] = df1["BargainingUnit"].map(eligibility_map).fillna(df1["Eligible"])

I can't map it to only one column. I need to map to multiple columns at once.

Joe Ferndz
  • 8,417
  • 2
  • 13
  • 33
Gimmy
  • 3
  • 1
  • Does this answer your question? [Manipulate values in pandas DataFrame columns based on matching IDs from another DataFrame](https://stackoverflow.com/questions/44973255/manipulate-values-in-pandas-dataframe-columns-based-on-matching-ids-from-another) – Joe Ferndz Jan 08 '21 at 19:42

1 Answers1

0

You can merge df1 with df2 based on the required columns. Also, you have to drop Eligible in df1 before merging as it is not required.

(df1.drop('Eligible', axis=1)
 .merge(df2, on=['CollectiveBargainingUnit', 'BusinessGroup','ProfitCenter'], how='inner'))

enter image description here

ggaurav
  • 1,764
  • 1
  • 10
  • 10
  • 1
    Amazing. I actually just found the solution on another StackOverflow question which is exactly your answer: https://stackoverflow.com/questions/63347225/pandas-update-merge-2-dataframes-based-on-multiple-matching-column-values. Thank you very, very much, ggaurav. – Gimmy Jan 08 '21 at 18:32