dataframe difference between consecutive row within group and creating string stating the same

user_9735423 0
Viewed 42

Dataframe:

col1  col_entity col2
a        a1       50
b        b1       40
a        a2       40
a        a3       30
b        b2       20
a        a4       20
b        b3       30
b        b4       50

I need to group them based on col1 and sort them highest to lowest based on col2 for each group and find difference between consecutive row and then create column for different groups for string statement. dateframe:

col1  col_entity col2   diff   col_statement
a        a1       50     10     difference between a1 and a2 is 10
b        a2       40     10     difference between a2 and a3 is 10
a        a3       30     10     difference between a3 and a4 is 10
a        a4       20     nan    **will drop this row**
b        b1       40     10     difference between b1 and b4 is 10
a        b4       50     10     difference between b4 and b3 is 10
b        b3       30     10     difference between b3 and b2 is 10
b        b2       20     nan    **will drop this row**

Please help me on this Thanks in advance

pandas-groupby pandas dataframe python-3.x python
1 Answers

You can do a couple of np.where statemtents:

  1. Use diff().abs() to get the absolute difference between one row and the row below with .shift().
  2. Return NaN for the .dif() if the extracted alphabetic characters do not match between one row and the next.
  3. In the col_statement column build a string based off other columns condtiionally on the NaN values with np.where()
df['diff'] = np.where(df['col1'].str.extract('([a-z])') == df['col1'].shift(-1).str.extract('([a-z])'),
                      df['col_entity col2'].diff().abs().shift(-1), np.nan)
df['col_statement'] = np.where(df['diff'].isnull(),
                               '**will drop this row**',
                              'difference between' + ' ' + df['col1'] + ' and '
                                   + df['col1'].shift(-1) + ' is ' + df['diff'].astype(str))
df
Out[1]: 
  col1  col_entity col2  diff                         col_statement
a   a1               50  10.0  difference between a1 and a2 is 10.0
b   a2               40  10.0  difference between a2 and a3 is 10.0
a   a3               30  10.0  difference between a3 and a4 is 10.0
a   a4               20   NaN                **will drop this row**
b   b1               40  10.0  difference between b1 and b4 is 10.0
a   b4               50  10.0  difference between b4 and b3 is 10.0
b   b3               30  10.0  difference between b3 and b2 is 10.0
b   b2               20   NaN                **will drop this row**
user_63667700
Related