python - combine dataframes and match unique ID and address

Question

I have two dataframes in pandas. Each of the dataframes are from 2 excel files that Im using that I've imported. DF1 has 29+ columns and DF2 has 7. DF has address info and Zip codes and a unique identifier and I need to combine both data frames and provide the unique identifier on the 2nd one. I wrote a function in python that cleans up the zip codes in both DFs. and I've named the column "zip code_fixed" in both columns.

def_zip(series):
       return series.astype(str).str[:5].str.zfill(5)  

I then merged the two dataframes and it looks like it appears to have worked! Here's the code that I used:

df7 = pd.merge(DF1, DF2)[['Customer Supplier Number', 'State_fixed', 'Zip Code_fixed, 'Address Line 1_fixed"]]

I did a few tests and it appears to have worked! the unique ID is correct after the merge. Now I wanted to take that DF7 and merge it to my original dataframe so that I can have the matched results line up with the results on the DF1.

I tried:

result = pd.concat([DF2, df7], axis = 1, ignore_index = True, sort=False

The two dataframes merged but the unique IDs were not correct. I feel like Im missing a step somewhere. Does anyone have any tips?

Thanks!

question from:https://stackoverflow.com/questions/66056935/combine-dataframes-and-match-unique-id-and-address

Answer 1

Waitting for answers