How can I groupby an ID and tag the first row with a non-null value?

user_9494569 0
Viewed 147

Within an ID, I need to remove the first row with a value > 0 and all rows before it in dataframe with an ordered date column. I think the easiest way to do that would be creating a new flag column to mark those rows for removal.

I figured out the below to tag the first date row within each ID (after sorting), but I'm having trouble figuring out how to continue my flag up to and including the first row where the value > 0:

df['flag'] = np.where((df.date == df.groupby('id')['date'].transform('flag')),1,0)

Which gets me:

id  date        value   flag
114 2016-01-01  0       1
114 2016-02-01  0       0
114 2016-03-01  200     0
114 2016-04-01  300     0
114 2016-05-01  100     0
220 2016-01-01  0       1
220 2016-02-01  0       0
220 2016-03-01  0       0
220 2016-04-01  0       0
220 2016-05-01  400     0
220 2016-06-01  200     0

but the end result should be:

id  date        value   flag
114 2016-01-01  0       1
114 2016-02-01  0       1
114 2016-03-01  200     1
114 2016-04-01  300     0
114 2016-05-01  100     0
220 2016-01-01  0       1
220 2016-02-01  0       1
220 2016-03-01  0       1
220 2016-04-01  0       1
220 2016-05-01  400     1
220 2016-06-01  200     0
pandas python
2 Answers
  1. First sort id and date in ascending order
  2. Then fill flag 1 when first non zero value with Id
  3. replace 0 with nan in flag
  4. bfill with group by and tranform
  5. final replace Nan with 0
df = pd.DataFrame(data={"id": [114, 114, 114, 114, 114, 220, 220, 220, 220, 220, 220],
                        "date": ['2016-01-01', '2016-02-01', '2016-03-01', '2016-04-01', '2016-05-01',
                                 '2016-01-01', '2016-02-01', '2016-03-01', '2016-04-01', '2016-05-01', '2016-06-01'],
                        'value': [0, 0, 200, 300, 100, 0, 0, 0, 0, 400, 200]})

df.sort_values(by=['id', 'date'], ascending=[True, True], inplace=True)
df['flag'] = 0
df.loc[df['value'].ne(0).groupby(df['id']).idxmax(),'flag']=1
df['flag'].replace({0:np.nan},inplace=True)

df['flag'] = df.groupby(['id'],as_index=False)['flag'].transform(pd.Series.bfill)
df['flag'].fillna(0,inplace=True)
print(df)

  id        date      value  flag
0   114  2016-01-01      0   1.0
1   114  2016-02-01      0   1.0
2   114  2016-03-01    200   1.0
3   114  2016-04-01    300   0.0
4   114  2016-05-01    100   0.0
5   220  2016-01-01      0   1.0
6   220  2016-02-01      0   1.0
7   220  2016-03-01      0   1.0
8   220  2016-04-01      0   1.0
9   220  2016-05-01    400   1.0
10  220  2016-06-01    200   0.0

I hope it would solve your problem

user_116079860

You can achieve that without creating an intermediate flag variable

Let's say your data looks like this:

     id       date  value
0   114 2016-01-01      0
1   114 2016-02-01    100
2   114 2016-03-01    200
3   114 2016-04-01    300
4   115 2016-01-01      0
5   115 2016-02-01      0
6   115 2016-03-01    100
7   115 2016-04-01    200
8   116 2016-01-01    100
9   116 2016-02-01      0    <-- notice the 0 value in the middle here
10  116 2016-03-01    330
11  116 2016-04-01    400

Method 1

This method assumes that all values of 0 are undesired, and that the first real value in each id group will be preceded with 0.

What we do is just remove all the zeros, then groupby id and simply remove the first row of data. This has the effect of removing the first real row and everything before it (which are assumed to be 0)

df1 = df[df['value'] > 0]
df1.sort_values('date').groupby('id', group_keys=False).apply(lambda g: g.iloc[1:])

    id       date  value
0  114 2016-03-01    200
1  114 2016-04-01    300
2  115 2016-04-01    200
3  116 2016-03-01    330
4  116 2016-04-01    400

Method 2

What if there are zeros in the middle of each id group (like row 9 in the data above)?

It's unclear from your question what you want to do in this case. Following your description, I'm assuming what you want to happen is to find the first real row (row 8), remove it and everything previous (which is no previous data in this case), and leave the 0 alone

The key here is using first_valid_index() to get the first row of data that is not NA/null, and using it as an index in .iloc

def remove_prev(g):
    out = g.replace({0: np.nan}).reset_index(drop=True)
    return out.iloc[out['value'].first_valid_index()+1:].fillna(0)

df.groupby('id', group_keys=False).apply(remove_prev).reset_index(drop=True)

      id       date  value
0  114.0 2016-03-01  200.0
1  114.0 2016-04-01  300.0
2  115.0 2016-04-01  200.0
3  116.0 2016-02-01    0.0
4  116.0 2016-03-01  330.0
5  116.0 2016-04-01  400.0

Flag method

If you really want to set a flag variable, you can again use first_valid_index() to determine which rows to set flag=1:

def flag_prev(g):
    out = g.replace({0: np.nan})
    out.loc[:out['value'].first_valid_index(), 'flag'] = 1
    return out.fillna(0)

df.groupby('id', group_keys=False).apply(flag_prev).reset_index(drop=True)

       id       date  value  flag
0   114.0 2016-01-01    0.0   1.0
1   114.0 2016-02-01  100.0   1.0
2   114.0 2016-03-01  200.0   0.0
3   114.0 2016-04-01  300.0   0.0
4   115.0 2016-01-01    0.0   1.0
5   115.0 2016-02-01    0.0   1.0
6   115.0 2016-03-01  100.0   1.0
7   115.0 2016-04-01  200.0   0.0
8   116.0 2016-01-01  100.0   1.0
9   116.0 2016-02-01    0.0   0.0
10  116.0 2016-03-01  330.0   0.0
11  116.0 2016-04-01  400.0   0.0
user_26056040
Related