Dataframe - Find first 0 in row grouped by date

Question

I have multiple data points for every day. I need to detect the first 0 of every day. I want to transform Data to the Output column.

Data in reproducible format:

Date,Data,Output
1/1/2019,1,False
1/1/2019,1,False
1/1/2019,0,True
1/1/2019,0,False
1/1/2019,1,False
2/1/2019,1,False
2/1/2019,0,True
2/1/2019,1,False
3/1/2019,0,True
3/1/2019,0,False

I thought this might involve the groupby feature, but struggling to figure out how to start.

can you add pandas code to generate the frame, so we can try it on our end. — oreopot, Nov 06 '19 at 02:39
Possible duplicate of [pandas: how do I select first row in each GROUP BY group?](https://stackoverflow.com/questions/30486417/pandas-how-do-i-select-first-row-in-each-group-by-group) — Tserenjamts, Nov 06 '19 at 02:41

Henry Yik · Accepted Answer · 2019-11-06T03:01:27.263

3

Using duplicated:

df["output"] = ~(df[df["Data"]==0].duplicated(subset=["Date","Data"],keep="first"))
df["output"].fillna(False, inplace=True)

print (df)

#
        Date  Data  output
0  1/01/2019     1   False
1  1/01/2019     1   False
2  1/01/2019     0    True
3  1/01/2019     0   False
4  1/01/2019     1   False
5  2/01/2019     1   False
6  2/01/2019     0    True
7  2/01/2019     1   False
8  3/01/2019     0    True
9  3/01/2019     0   False

edited Nov 06 '19 at 03:01

answered Nov 06 '19 at 02:40

Henry Yik

22,275
4
18
40

Am I missing something. This output and the user input is the same. You event put the dates wrong – 1__ Nov 06 '19 at 02:56
@YusufBaktir from OP: `I want to transform Data to the Output column`. – Henry Yik Nov 06 '19 at 02:58
Ok, it makes sense. Cool solution! – 1__ Nov 06 '19 at 02:59
One question - what does the ~ do? – Chris Norris Nov 14 '19 at 01:07
It inverts the boolean array. – Henry Yik Nov 14 '19 at 03:03

score 0 · Answer 2 · answered Nov 06 '19 at 02:58

Try 2 boolean masks

m = df.Data.eq(0)
m1 = m.groupby(df.Date).cumsum().eq(1)    
df['New'] = m & m1

Out[834]:
        Date  Data    New
0  1/01/2019     1  False
1  1/01/2019     1  False
2  1/01/2019     0   True
3  1/01/2019     0  False
4  1/01/2019     1  False
5  2/01/2019     1  False
6  2/01/2019     0   True
7  2/01/2019     1  False
8  3/01/2019     0   True
9  3/01/2019     0  False

score 0 · Answer 3 · answered Nov 06 '19 at 03:00

0

Another groupby solution with loc

df.loc[df[df.data.eq(0)].groupby('date').data.idxmin(), 'out'] = True
df = df.fillna(False)

answered Nov 06 '19 at 03:00

manwithfewneeds

1,137
1
7
10

Dataframe - Find first 0 in row grouped by date

3 Answers3