Get and Modify column in groups on rows that meet a condition

user_10892888 0
Viewed 33

I have this DataFrame:

df = pd.DataFrame({'day': [1, 1, 1, 2, 2, 2, 3, 3, 3], 'hour': [10, 10, 10, 11, 11, 11, 12, 12, 12], 'sales': [0, 40, 30, 10, 80, 70, 0, 0, 20]})

   day  hour  sales
0    1    10      0
1    1    10     40
2    1    10     30
3    2    11     10
4    2    11     80
5    2    11     70
6    3    12      0
7    3    12      0
8    3    12     20

And I would like to filter to get the first entry of each day that has volume greater than 0. And as an additional thing I would like to change the 'sales' column for these to 9.

So to get something like this:

   day  hour  sales
0    1    10      0
1    1     9     40
2    1    10     30
3    2     9     10
4    2    11     80
5    2    11     70
6    3    12      0
7    3    12      0
8    3     9     20

I only came up with this iterative solution. But is there a solution, how I can apply it in a more functional way?

# Group by day:
groups = df.groupby(by=['day'])

# Get all indices of first non-zero sales entry per day:
indices = []
for name, group in groups:
    group = group[group['sales'] > 0]
    indices.append(group.index.to_list()[0])

# Change their values:
df.iloc[indices, df.columns.get_loc('hour')] = 9
pandas-groupby pandas
2 Answers

You can create a group of df['day'] after checking of sales is greater than 0 , then get idxmax and filter out groups which doesnot have any value greater than 0 by using any , then assign with loc[]

g = df['sales'].gt(0).groupby(df['day'])
idx = g.idxmax()
df.loc[idx[g.any()],'hour']=9

print(df)

    day  hour  sales
0     1    10      0
1     1     9     40
2     1    10     30
3     2     9     10
4     2    11     80
5     2    11     70
6     3    12      0
7     3    12      0
8     3     9     20
user_98406370
  1. Create a mask m that groups by day as well as rows where the sales are not 0.
  2. Then, use this mask as well as df['sales'] > 0 to change those specific rows to 9 with np.where()
df = pd.DataFrame({'day': [1, 1, 1, 2, 2, 2, 3, 3, 3],
                   'hour': [10, 10, 10, 11, 11, 11, 12, 12, 12],
                   'sales': [0, 40, 30, 10, 80, 70, 0, 0, 20]})
m = df.groupby(['day', df['sales'].ne(0)])['sales'].transform('first')
df['hour'] = np.where((df['sales'] == m) & (df['sales'] > 0), 9, df['hour'])
df

Out[37]: 
   day  hour  sales
0    1    10      0
1    1     9     40
2    1    10     30
3    2     9     10
4    2    11     80
5    2    11     70
6    3    12      0
7    3    12      0
8    3     9     20
user_63667700
Related