CODEWITHSUNDEEP
pythonpython-3.xpandas
Iftak Nayeem
Iftak Nayeem

Reputation: 55

merge values of groupby results with dataframe in new column Python Pandas

I have a groupby object which looks like:

Age  Pclass  fam_size
0.0  3       alone       11.586475
1.0  1       alone       83.210410
     2       alone       18.092672
     3       alone        7.974073
2.0  1       alone       72.784513
     2       alone       12.058114
     3       alone       10.317035
3.0  1       alone       39.364014
     2       alone       14.625000
     3       alone        7.048491
4.0  1       alone       36.562920
     2       alone       10.500000
     3       alone        7.820000
Name: Fare, dtype: float64

I want to find the rows in my original df that satisfy the age, Pclass, and fam_size criteria as listed in the above groupby object and fill in a new column with their corresponding values. Those that don't should be kept null.

I'm trying to look for an efficient way to do this, possibly an apply,transform,map, merge or something short but having no luck.

Upvotes: 3

Views: 235

Answers (1)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210832

IIUC you can do it this way:

In [299]: df
Out[299]:
   a  b  c   d
0  1  1  1  11
1  1  1  2  12
2  1  2  3  13
3  1  2  4  14
4  2  1  5  15

In [300]: g
Out[300]:
a  b
1  1    3
   2    7
Name: c, dtype: int64

In [301]: df.merge(g.reset_index(), on=['a','b'], how='left',
                   suffixes=['','_'], indicator=True) \
            .rename(columns={'c_':'new'})
Out[301]:
   a  b  c   d  new     _merge
0  1  1  1  11  3.0       both
1  1  1  2  12  3.0       both
2  1  2  3  13  7.0       both
3  1  2  4  14  7.0       both
4  2  1  5  15  NaN  left_only

Upvotes: 1

Related Questions