How merge or join two dataframes, but keeping certain columns of both

Question

How merge or join two dataframes, but keeping certain columns of both?

I need to merge this two dataframes into one. dataframe 2 has all the columns dataframe 1 just need the column "leads"

Dataframe1

campaignid leads 35119190 391 31664745 365 4899110 211 325772660 195 64002140 131 143679198 58 283494007 45

Dataframe2

campaignid cost time reach 35119190 391 391 391 31664745 365 391 391 4899110 211 391 391 325772660 195 391 391 64002140 131 391 391 143679198 58 391 391 283494007 45 391 391

Desired result:

Dataframe2

campaignid cost time reach leads 35119190 391 391 391 391 31664745 365 391 391 365 4899110 211 391 391 211 325772660 195 391 391 195 64002140 131 391 391 131 143679198 58 391 391 58 283494007 45 391 391 45

g_spend.to_dict()

{'id': {0: 35119190, 1: 64002140, 2: 272351300, 3: 4899110,}, 'Campaign_ID_name': {0: 'brand', 1: '-', 2: '-', 3: 'science', , 'Month': {0: '2019|08', 1: '2019|08', 2: '2019|08', 3: '2019|08', }, 'Account': {0: 'a', 1: 'a', 2: 'b', 3: 'c', }, 'campaignid': {0: 35119190, 1: 64002140, 2: 272351300, 3: 4899110, }, 'campaign_name': {0: 'All_Brand', 1: 'All', 2: 'All_GBHS', 3: 'All_Science', }, 'cost': {0: '$59,399.37 ', 1: '$12,660.37 ', 2: '$5,631.96 ', }}

grouped_cw.to_dict()

{'leads': {'1076533154': 40.0, '143679198': 58.0, '169278078': 13.0, '1729099155': 8.0, }}

isn't it just Dataframe1.merge(Dataframe2, on='campaignid')? — Quang Hoang
– Quang Hoang, Commented Sep 26, 2019 at 20:38
@QuangHoang unfortunately not, I tried before. it give error: "You are trying to merge on int64 and object columns. If you wish to proceed you should use pd.concat " — Peter
– Peter, Commented Sep 26, 2019 at 20:45
That says the two campaignid are not of the same type. Convert them both to string? — Quang Hoang
– Quang Hoang, Commented Sep 26, 2019 at 20:46
df1.to_dict() and df2.to_dict() add the outputs of those to this question. — Scott Boston
– Scott Boston, Commented Sep 26, 2019 at 21:06
Ah... grouped_cw id is a series with campaignids in the index! — Scott Boston
– Scott Boston, Commented Sep 26, 2019 at 21:54

GSBYBF · Accepted Answer · 2019-09-26 20:38:59Z

1

pd.merge(Dataframe1, Dataframe2, on='campaignid')

answered Sep 26, 2019 at 20:38

GSBYBF

1603 bronze badges

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

3 Comments

Peter Over a year ago

unfortunately not, I tried before. it give error: "You are trying to merge on int64 and object columns. If you wish to proceed you should use pd.concat "

Emac Over a year ago

@Peter : You are trying to merge on two different types of data then. Check the datatypes.One is probably a string, one is an Int.

Peter Over a year ago

thanks @GSBYBF, but it still not working. KeyError: 'campaignid' During handling of the above exception, another exception occurred: – I used grouped_cw= cw.groupby(["campaignid"]).sum() in one of the dataframes, it mid be the cause of issue

Scott Boston · Accepted Answer · 2019-09-26 21:55:46Z

Let's use map:

df2['leads'] = df2['campaignid'].map(df1.set_index('campaignid')['leads']) df2

Output:

 campaignid cost time reach leads 0 35119190 391 391 391 391 1 31664745 365 391 391 365 2 4899110 211 391 391 211 3 325772660 195 391 391 195 4 64002140 131 391 391 131 5 143679198 58 391 391 58 6 283494007 45 391 391 45

Try

df2['leads'] = df2['campaignid'].map(grouped_cw)

thanks @Scott Boston, but it still not working. KeyError: 'campaignid' During handling of the above exception, another exception occurred:
I used grouped_cw= cw.groupby(["campaignid"]).sum() in one of the dataframes, it mid be the cause of issue

Collectives™ on Stack Overflow

How merge or join two dataframes, but keeping certain columns of both

2 Answers 2

3 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Related