Update column values based on another dataframe's index

Question

I have the following dataframes:

NUMS = ['1', '2', '3', '4', '5']
LETTERS = ['a', 'b', 'c']
df1 = pd.DataFrame(index=NUMS, columns=LETTERS)
    a    b    c
1  NaN  NaN  NaN
2  NaN  NaN  NaN
3  NaN  NaN  NaN
4  NaN  NaN  NaN
5  NaN  NaN  NaN

df2 = pd.DataFrame([['tom', 10], ['nick', 15], ['james', 14]],
                   index=LETTERS,
                   columns=['col', 'col2'])
    col  col2
a    tom    10
b   nick    15
c  james    14

I'm trying to update df1 with df2 so that if the column matches the index from df2, all rows are updated with col2:

    a    b    c
1   10   15  14
2   10   15  14
3   10   15  14
4   10   15  14
5   10   15  14

I've tried df1.update(df2['col2']), but df1 does not update.

I've also tried df1.apply(lambda x: df2['col2'].loc[x]), but I'm getting the following error:

KeyError: "None of [Float64Index([nan, nan, nan, nan, nan], dtype='float64')] are in the [index]"

Thank you!

ziying35 · Accepted Answer · 2022-05-07 00:53:30Z

1

try this:

df1.fillna(df2.col2)
>>>
    a   b   c
1   10  15  14
2   10  15  14
3   10  15  14
4   10  15  14
5   10  15  14

answered May 7, 2022 at 0:53

ziying35

1,3155 silver badges6 bronze badges

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

Comments

Idan Hazan · Accepted Answer · 2022-05-07 00:21:56Z

0

Try this:

for column in df1.columns:
    df1.loc[:, column] = df2.at[column, 'col2']

answered May 7, 2022 at 0:21

Idan Hazan

14911 bronze badges

Collectives™ on Stack Overflow

Update column values based on another dataframe's index

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related