Viv Viv -4 years ago 105
MySQL Question

How to use Python pandas Df to merge csvs with more than 1 same column and add only different columns

This question is similar to the simple mysql operation -

UPDATE hpaai_month_div t, fahafa_monthly s 
SET t.col1=s.col1 WHERE t.col2=s.col2 AND t.year=s.year AND t.month=s.month;


Data :

CSV A
month year col2 col1
abc 2000 DEFSSDS 190
def 2001 GHISFDS 210
ghi 2002 SJDYHGF 910

CSV B
month year col2 col1 stat_fips
abc 2000 DEFSSDS 0 a
def 2001 GHISFDS 0 b
ghi 2002 SJDYHGF 0 c


Resulting CSV :

month year col2 col1 stat_fips
abc 2000 DEFSSDS 190 a
def 2001 GHISFDS 210 b
ghi 2002 SJDYHGF 910 c


Code so far : (not working as desired)

df_a = pd.read_csv('a.csv')
df_b = pd.read_csv('b.csv')
merged_df = pd.merge(df_a, df_b, on="col1", how="left")

merged_df = pd.concat([merged_df], axis=1)
merged_df.to_csv('final_output.csv', encoding='utf-8', index=False)
print open('final_output.csv').read()


How to get data as the result csv

Answer Source

If you drop 'col1' from 'df_b' ahead of time, you can let merge work with its defaults.

df_a.merge(df_b.drop('col1', 1))

  month  year     col2  col1 stat_fips
0   abc  2000  DEFSSDS   190         a
1   def  2001  GHISFDS   210         b
2   ghi  2002  SJDYHGF   910         c
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download