I have the following df:
| day | first mover |
| -------- | -------------- |
| 1 | 1 |
| 2 | 1 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 1 |
| 7 | 0 |
| 8 | 1 |
i want to group this Data frame in the order bottom to top with a frequency of 4 rows. Furthermore if first row of group is 1 make all other entries 0. Desired output:
| day | first mover |
| -------- | -------------- |
| 1 | 1 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 7 | 0 |
| 8 | 0 |
The first half i have accomplished. I am confuse about how to make other entries 0 if first entry in each group is 1.
N=4
(df.iloc[::-1].groupby(np.arange(len(df))//N
import pandas as pd
import numpy as np
# Create sample DataFrames
df = pd.DataFrame(
{
"day": [*range(1, 21)],
"first mover": np.random.randint(0, 2, 20),
}
)
# if the day-1 (1, 5, 9, ...) is dividable by 4
# and
# the 'first mover' == 1
# result is 1 otherwise keep as 0
df['first mover edited'] = df.apply(lambda row: ( ( (row.day-1) % 4 == 0 ) and
( row['first mover'] == 1 ) )*1, axis=1)
df['group'] = (df['day']-1) // 4
df
| day | first mover | first mover edited | group |
|------:|--------------:|---------------------:|--------:|
| 1 | 1 | 1 | 0 |
| 2 | 0 | 0 | 0 |
| 3 | 1 | 0 | 0 |
| 4 | 0 | 0 | 0 |
| 5 | 1 | 1 | 1 |
| 6 | 1 | 0 | 1 |
| 7 | 0 | 0 | 1 |
| 8 | 1 | 0 | 1 |
| 9 | 0 | 0 | 2 |
| 10 | 0 | 0 | 2 |
| 11 | 0 | 0 | 2 |
| 12 | 0 | 0 | 2 |
| 13 | 1 | 1 | 3 |
| 14 | 0 | 0 | 3 |
| 15 | 1 | 0 | 3 |
| 16 | 0 | 0 | 3 |
| 17 | 0 | 0 | 4 |
| 18 | 1 | 0 | 4 |
| 19 | 0 | 0 | 4 |
| 20 | 0 | 0 | 4 |
you can edit the 'first mover' column this is for demonstration