CODEWITHSUNDEEP
pythonpandasdataframe
Sean R
Sean R

Reputation: 337

Replace one column of a dataframe's values with another

I am working with a table like this: unique IDs might have 1 entry or they might have many entries.

ID Start Stop Days
101 1/1/2021 1/31/2021 270
101 2/1/2020 3/31/2020 270
102 1/1/2021 3/29/2020 208
102 3/30/2020 3/31/2020 208
103 1/1/2020 3/31/2020 210
104 1/1/2020 1/2/2020 130
104 1/3/2020 3/31/2020 130
105 1/1/2020 3/31/2020 160

I need to replace the number in the days column based off another dataframe, which gives a new Days number for each ID. This dataframe looks like:

ID Days
101 290
102 250
103 215
104 180
105 175

How can I replace the Days column in the first dataframe with the Days information for each ID in the second dataframe? My end result should look thus:

ID Start Stop Days
101 1/1/2021 1/31/2021 290
101 2/1/2020 3/31/2020 290
102 1/1/2021 3/29/2020 250
102 3/30/2020 3/31/2020 250
103 1/1/2020 3/31/2020 215
104 1/1/2020 1/2/2020 180
104 1/3/2020 3/31/2020 180
105 1/1/2020 3/31/2020 175

I tried df.update and converting the second dataframe to a dictionary and using .map but with no success. Any help would be greatly appreciated!

Thanks,

Sean

Upvotes: 1

Views: 52

Answers (2)

Andrej Kesely
Andrej Kesely

Reputation: 195438

df_1["Days"] = pd.merge(df_1, df_2, on="ID")["Days_y"]
print(df_1)

Prints:

    ID      Start       Stop  Days
0  101   1/1/2021  1/31/2021   290
1  101   2/1/2020  3/31/2020   290
2  102   1/1/2021  3/29/2020   250
3  102  3/30/2020  3/31/2020   250
4  103   1/1/2020  3/31/2020   215
5  104   1/1/2020   1/2/2020   180
6  104   1/3/2020  3/31/2020   180
7  105   1/1/2020  3/31/2020   175

Upvotes: 0

Niko Petering
Niko Petering

Reputation: 26

You could try this:

df_1.drop(columns=Days, inplace=True)
final = pd.merge(df1, df2, on='ID')
final.head()

Upvotes: 1

Related Questions