Update column values based on another dataframe's index

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!

2 answers

  • answered 2022-05-07 00:21 Idan Hazan

    Try this:

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

  • answered 2022-05-07 00:53 ziying35

    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
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum