CODEWITHSUNDEEP
pythonpandasdataframe
Ladenkov Vladislav
Ladenkov Vladislav

Reputation: 1297

DataFrameGroupBy diff() on condition

Suppose i have a DataFrame:

df = pd.DataFrame({'CATEGORY':['a','b','c','b','b','a','b'],
                   'VALUE':[pd.np.NaN,1,0,0,5,0,4]})

which looks like

    CATEGORY    VALUE
0      a         NaN
1      b         1
2      c         0
3      b         0
4      b         5
5      a         0
6      b         4

I group it:

df = df.groupby(by='CATEGORY')

And now, let me show, what i want with the help of example on one group 'b':

df.get_group('b')

group b:

    CATEGORY    VALUE
1      b          1
3      b          0
4      b          5
6      b          4

I need: In the scope of each group, count diff() between VALUE values, skipping all NaNs and 0s. So the result should be:

    CATEGORY    VALUE  DIFF
1      b          1      - 
3      b          0      -
4      b          5      4
6      b          4     -1

Upvotes: 6

Views: 3232

Answers (2)

pansen
pansen

Reputation: 6663

You can use diff to subtract values after dropping 0 and NaN values:

df = pd.DataFrame({'CATEGORY':['a','b','c','b','b','a','b'],
               'VALUE':[pd.np.NaN,1,0,0,5,0,4]})

grouped = df.groupby("CATEGORY")

# define diff func
diff = lambda x: x["VALUE"].replace(0, np.NaN).dropna().diff()
df["DIFF"] = grouped.apply(diff).reset_index(0, drop=True)

print(df)

  CATEGORY  VALUE  DIFF
0        a    NaN   NaN
1        b    1.0   NaN
2        c    0.0   NaN
3        b    0.0   NaN
4        b    5.0   4.0
5        a    0.0   NaN
6        b    4.0  -1.0

Upvotes: 4

firelynx
firelynx

Reputation: 32224

Sounds like a job for a pd.Series.shift() operation along with a notnull mask.

First we remove the unwanted values, before we group the data

nonull_df = df[(df['VALUE'] != 0) & df['VALUE'].notnull()]
groups = nonull_df.groupby(by='CATEGORY')

Now we can shift internally in the groups and calculate the diff

nonull_df['next_value'] = groups['VALUE'].shift(1)
nonull_df['diff'] = nonull_df['VALUE'] - nonull_df['next_value']

Lastly and optionally you can copy the data back to the original dataframe

df.loc[nonull_df.index] = nonull_df

df
  CATEGORY  VALUE  next_value  diff
0        a    NaN         NaN   NaN
1        b    1.0         NaN   NaN
2        c    0.0         NaN   NaN
3        b    0.0         1.0  -1.0
4        b    5.0         1.0   4.0
5        a    0.0         NaN   NaN
6        b    4.0         5.0  -1.0

Upvotes: 2

Related Questions