CODEWITHSUNDEEP
pythonpandasdataframegroup-by
Ank
Ank

Reputation: 1904

Pandas groupby and subtract rows

I have the following dataframe:

id variable year value
1      a    2020   2
1      a    2021   3
1      a    2022   5
1      b    2020   3
1      b    2021   8
1      b    2022   10

I want to groupby id and variable and subtract 2020 values from all the rows of the group. So I will get:

id variable year value
1      a    2020   0
1      a    2021   1
1      a    2022   3
1      b    2020   0
1      b    2021   5
1      b    2022   7

How can I do that?

Upvotes: 3

Views: 3811

Answers (2)

jezrael
jezrael

Reputation: 862541

Use DataFrame.merge if not sure if 2020 is first per groups:

df1 = df[df['year'].eq(2020)]
df['value'] -= df.merge(df1,how='left',on=['id','variable'],suffixes=('_',''))['value'].values
print (df)
   id variable  year  value
0   1        a  2020      0
1   1        a  2021      1
2   1        a  2022      3
3   1        b  2020      0
4   1        b  2021      5
5   1        b  2022      7

If 2020 is always first per groups use GroupBy.transform with GroupBy.first:

df['value'] -= df.groupby(['id','variable'])['value'].transform('first')
print (df)
   id variable  year  value
0   1        a  2020      0
1   1        a  2021      1
2   1        a  2022      3
3   1        b  2020      0
4   1        b  2021      5
5   1        b  2022      7

EDIT:

If in data are duplicated 2020 rows per groups solution first remove dupes and subtract only first value:

print (df)
   id variable  year  value
0   1        a  2020      3
1   1        a  2020      2
2   1        a  2022      5
3   1        b  2020      3
4   1        b  2021      8
5   1        b  2022     10

df1 = df[df['year'].eq(2020)]
df['value'] -= df.merge(df1.drop_duplicates(['id','variable']),
                        how='left',
                        on=['id','variable'],
                        suffixes=('_',''))['value'].values

print (df)
   id variable  year  value
0   1        a  2020      0
1   1        a  2020     -1
2   1        a  2022      2
3   1        b  2020      0
4   1        b  2021      5
5   1        b  2022      7

Or aggregate values, e.g. by sum for deduplicate data:

print (df)
   id variable  year  value
0   1        a  2020      3
1   1        a  2020      1
2   1        a  2022      5
3   1        b  2020      3
4   1        b  2021      8
5   1        b  2022     10

df = df.groupby(['id','variable','year'], as_index=False).sum()
print (df)
   id variable  year  value
0   1        a  2020      4
1   1        a  2022      5
2   1        b  2020      3
3   1        b  2021      8
4   1        b  2022     10

df1 = df[df['year'].eq(2020)]
df['value'] -= df.merge(df1, how='left',
                        on=['id','variable'],
                        suffixes=('_',''))['value'].values

print (df)
   id variable  year  value
0   1        a  2020      0
1   1        a  2022      1
2   1        b  2020      0
3   1        b  2021      5
4   1        b  2022      7

Upvotes: 3

ansev
ansev

Reputation: 30920

Although 2020 is not the first of group we could use: GroupBy.transform with Series.where

df['value']= df['value'].sub(df['value'].where(df['year'].eq(2020))
                                        .groupby([df['id'],df['variable']])
                                        .transform('max'))
print(df)
   id variable  year  value
0   1        a  2020    0.0
1   1        a  2021    1.0
2   1        a  2022    3.0
3   1        b  2020    0.0
4   1        b  2021    5.0
5   1        b  2022    7.0

if year it is stringyou could need 

df['year'].eq('2020')

Upvotes: 2

Related Questions