Subtract a row from previous row which has value from previous group in DataFrame

user_13058169 0
Viewed 560

I have a pandas DataFrame in Python as Below:

   A   --  E    user
0  0   --  0   1
1  12  --  1   1
2  12  --  1   1
3  13  --  2   1
4  15  --  3   1
5  15  --  3   1
6  15  --  3   1
7  19  --  4   2
8  20  --  5   2
9  25  --  6   2
10 25  --  6   2

I want to subtract 1 row from the previous group/set of value(ie, it should not be 0) [groupby E] for each user

What I mean is to have a DataFrame as below:

   A            --  E   user
0  0            --  0   1
1  12           --  1   1
2  12           --  1   1
3  1(ie,13-12)  --  2   1
4  2 (  --   )  --  3   1
5  2            --  3   1
6  2            --  3   1
7  19           --  4   2 (should not change at user 2) 
8  1            --  5   2
9  5(ie,25-20)  --  6   2
10 5(ie,25-20)  --  6   2

Can You please help me..Thank You

pandas deep-learning machine-learning dataframe python
1 Answers

If in E column are unique groups use DataFrameGroupBy.diff, replace mising values by original with Series.fillna and use Series.where with mask for consecutive values (compared for not equal shifted values) and then forward filling missing values with ffill and last to integers:

df['A1'] = (df.groupby('user')['A'].diff()
              .fillna(df['A'])
              .where(df['E'].ne(df['E'].shift()))
              .ffill()
              .astype(int))
print (df)
     A  E  user  A1
0    0  0     1   0
1   12  1     1  12
2   12  1     1  12
3   13  2     1   1
4   15  3     1   2
5   15  3     1   2
6   15  3     1   2
7   19  4     2  19
8   20  5     2   1
9   25  6     2   5
10  25  6     2   5
user_29010020
Related