Adding a new column to a dataframe based on the values of another dataframe

Question

I do have two csv files, I am using pandas to read the data.

The train.csv contains values, with headers id, sentiment

87,Positive
10,Positive
7,Neutral

The text.csv contains values, with headers id, text

7,hello, I think the price if high...
87, you can call me tomorow...
....

I would like to insert the text from text.csv into train.csv so the result would be:

87,Positive, you can call me tomorow...

Can any one help with pandas?

import pandas as pd

train= pd.read_csv("train.csv")
text= pd.read_csv("text.csv")

# this does not work
combined= pd.merge(train, text, on=['id'])

Note Some Ids may not be in the files, so I need to set null if the id does not exists

Michael Delgado · Accepted Answer · 2021-04-04 02:36:48Z

1

set the indices on the two dataframes, then add the columns:

train.set_index('id').sentiment + text.set_index('id').text

answered Apr 4, 2021 at 2:36

Michael Delgado

15.7k4 gold badges39 silver badges65 bronze badges

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

2 Comments

the files already have headers, I tried but does not work, also, it may happen that some IDs not exists

always shows empty, the 3 headers show up, but not data ?!

Arkadip Bhattacharya · Accepted Answer · 2021-04-04 02:44:23Z

0

One of the easy way can be

pd.merge(train, test, on='id', how='outer')

As per pandas docs, if you use how as outer, it will take all keys

answered Apr 4, 2021 at 2:44

6535 silver badges15 bronze badges