How do I replace column values of a dataframe with values of another dataframe based on a common column?

Question

I have two dataframes, one that looks like this:

hec_df:

accident year	factor	age
2007	1.5	13
2008	1.6	11
2009	1.7	15

and hec_ldfs:

accident year	factor
2007	1.6
2008	1.64
2009	1.7

My goal is to replace the factor value of df1 with the factor value of df2. My code for this is

hec_df['factor'] = hec_df['factor'].map(hec_ldfs.set_index('accident year')['factor'])

But it returns NaN on the factor column. Does anyone know why this is happening?

EDIT: I'm not sure why my first dataframe is formatted like that, does anyone know how to fix it?

Naveed · Accepted Answer · 2022-11-30 02:06:07Z

3

you're mapping factor to the accident_year, instead of hec_df.accident_year to the hec_df.accident year

hec_df['factor'] = hec_df['accident year'].map(hec_ldfs.set_index('accident year')['factor']).fillna(hec_df['factor'])
hec_df

accident year   factor  age
0   2007    1.60    13
1   2008    1.64    11
2   2009    1.70    15

answered Nov 30, 2022 at 2:06

Naveed

11.7k2 gold badges16 silver badges21 bronze badges

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

4 Comments

ferreiradev Over a year ago

I tested this, and fillna is not required for the sample data provided. --- However, If there was missing data in hec_ldfs (the second df), fillna would fill the newly built hec_df['factor'] Series using the original values in hec_df['factor'].

Naveed Over a year ago

@ferreiradev, fillna, is a fail-safe to avoid a NaN. If OP is certain of not having a null scenario, it can be safely removed.

ntwong Over a year ago

Thank you, this worked! An additional follow up question: can you use the map function to use two or more indexes? For example, if I needed the lookup to use accident year as well as segment, could I do that?

Naveed Over a year ago

Map works with dictionary. One way to make use of two fields is to create a temp field combining two column and use that to map. Hope it helps.

Collectives™ on Stack Overflow

How do I replace column values of a dataframe with values of another dataframe based on a common column?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related