Subtract the value in a field in one row from all other rows of the same field in pandas dataframe

user_13920664 0
Viewed 67

I have a dataframe as shown below:

data = {'sid':[1,1,1,2,2,2],
        'field1':['start', None, None, 'start', None, None], 
        'field2':['a', 'b', 'z', 'd', 'z','s'],
        'val':[20, 22, 23, 40, 45, 47]}
df = pd.DataFrame(data)
print(df)

   sid field1  val
0    1  start   20
1    1   None   22
2    1   None   23
3    2  start   40
4    2   None   45
5    2   None   47

I would like to create a new field newval which stores the difference between val of that row and the val in the row with same sid and field1 = 'start'.

   sid field1  val  newval
0    1  start   20     NaN
1    1   None   22     2.0
2    1   None   23     3.0
3    2  start   40     NaN
4    2   None   45     5.0
5    2   None   47     7.0

I have tried diff() with groupby() but that gives me a running difference.

 df['newval'] = df.groupby('sid')['val'].diff()

   sid field1  val  newval
0    1  start   20     NaN
1    1   None   22     2.0
2    1   None   23     1.0
3    2  start   40     NaN
4    2   None   45     5.0
5    2   None   47     2.0

How can I get the difference from a particular row?

pandas dataframe python
3 Answers

You can use groupby with a helper column and sid and then get first value of the group and then subtract from val field.

df['new_val'] = (df['val']-
     df.groupby(['sid',df['field1'].eq("start").cumsum()])['val'].transform("first"))

print(df)

   sid field1 field2  val  new_val
0    1  start      a   20        0
1    1   None      b   22        2
2    1   None      z   23        3
3    2  start      d   40        0
4    2   None      z   45        5
5    2   None      s   47        7

The helper column is as below, which helps in grouping:

print(df['field1'].eq("start").cumsum())
0    1
1    1
2    1
3    2
4    2
5    2
Name: field1, dtype: int32
user_98406370

Probably not the most elegant solution, but you could do a join on just the rows with field1 = 'start'

Using df as defined above:

tmp = pd.merge(df, df.loc[df['field1'] == 'start', ['sid', 'val']],
        how = 'left', left_on = 'sid', right_on = 'sid',
        suffixes = ['', 'start_val'])

tmp['newval'] = np.where(tmp['field1'] == 'start', np.nan, tmp['val'] - tmp['valstart_val'])
tmp.drop('valstart_val', axis = 1, inplace = True)

Output:

sid field1  field2  val newval
1   start   a   20  NaN
1   None    b   22  2.0
1   None    z   23  3.0
2   start   d   40  NaN
2   None    z   45  5.0
2   None    s   47  7.0
user_98681190

Looks like your data is already sorted by sid and there is only one start at the first row of each Id. In which case, you can do:

ids = df.duplicated(['sid'])
# also
# ids = df['field1'].astype(bool)

df['newval'] = df['val'].sub(df['val'].mask(ids).ffill()).where(ids)

Output:

   sid field1 field2  val  newval
0    1  start      a   20     NaN
1    1   None      b   22     2.0
2    1   None      z   23     3.0
3    2  start      d   40     NaN
4    2   None      z   45     5.0
5    2   None      s   47     7.0
user_42384080
Related