CODEWITHSUNDEEP
pythonpandasdataframepandas-groupby
alex_T
alex_T

Reputation: 115

how to update a dataframe with another filtered dataframe

I have 2 dataframes. I need to update a column in the first one with the average of values in the second one, grouped by index. here an example df1 (col1 is the index)

      col2 col3
col1           
a        0    X
b        0    0
c        0    0
d        0    0

df2 (col1 is the index)

    col2  col3
col1            
a        1     0
a        3     0
d        2     0
d        4     0

I need the average in df2's col2 (a=2, d=3) and update df1 only for the rows with col3 = X

I tried this 

df1.loc[df1.col3=='X'].update(df2.groupby(df2.index),'col2'].mean().to_frame())

It works only if I don't use loc.

the result I'm trying to have df1 (col1 is the index)

    col2 col3
col1           
a        2    X
b        0    0
c        0    0
d        0    0

Upvotes: 1

Views: 70

Answers (1)

anky
anky

Reputation: 75080

Use:

m=df2.groupby(df2.index).col2.mean()
df1.loc[df1.col3=='X','col2']=m
print(df1)

      col2 col3
col1           
a        2    X
b        0    0
c        0    0
d        0    0

Upvotes: 1

Related Questions