Home > database >  Update column values based on another dataframe's index
Update column values based on another dataframe's index

Time:05-07

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!

CodePudding user response:

Try this:

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

CodePudding user response:

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
  • Related