merging 2 dataframe with different columns in pandas

Question

I have a large dataset that requires merging, however I am unsure how to get my desired output

here is an example of what i have done:

df1 = pd.DataFrame({'identity': ['A','A','A'], 'Type': ['D','E','F'], 'count_df1': [7,8,9]})
df2 = pd.DataFrame({'identity': ['A'], 'Type':[ 'D'],'Name':['ABC co'],'count_df2':[5]})
    

merged = df1.merge(df2,on = ['identity','Type'],how ='inner')

I need to merge on identity and Type

output:

identity    Type    Name    count_df2
   A          D     ABC co     5

I have also tried outer join,

  identity  Type        count_df1   Name_y  count_df2
0   A         D            7         ABC co   5.0
1   A         E            8         NaN      NaN
2   A         F            9         NaN      NaN

----------What i hope to get -------------
    identity    Type     Name        count_df1    count_df2
        A         D     ABC co         7           5
        A         E     ABC co         8           0
        A         F     ABC co         9           0

Please help ! Thank you very much

Thanks for the fast response guys !

Alvin_O
– Alvin_O

2020-11-13 11:57:51 +00:00
Commented Nov 13, 2020 at 11:57 — Alvin_O
– Alvin_O, Commented Nov 13, 2020 at 11:57

Tom Ron · Accepted Answer · 2020-11-13 09:47:40Z

1

df1.merge(df2, on=['identity','Type'], how='outer').fillna({"count_df2": 0, "Name": "ABC co"})

outer join + fillna missing values by column.

answered Nov 13, 2020 at 9:47

Tom Ron

6,2413 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jxcvi · Accepted Answer · 2020-11-13 09:48:03Z

0

It seems that you want your NaNs to be zeros, which you can accomplish by adding a .fillna(0) at the end.

merged = df1.merge(df2,on = ['identity','Type'],how ='outer').fillna(0)

answered Nov 13, 2020 at 9:48

jxcvi

195 bronze badges

Comments

jojo_Berlin · Accepted Answer · 2020-11-13 09:48:07Z

0

Ah okay I see so basically your outer join works as I see it. the only problem that I see are the Nan values right ? I would suggest to use fillNaon the result https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.fillna.html

answered Nov 13, 2020 at 9:48

jojo_Berlin

7031 gold badge4 silver badges20 bronze badges

Collectives™ on Stack Overflow

merging 2 dataframe with different columns in pandas

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related