add new column based on the several rows which have the same date

user_8525711 0
Viewed 45

I have one data frame as below. At first,they have three columns('date','time','flag'). I want to add one column which based on the flag and date which means when I get flag=1 in one day at first, then this row target is 1, the other target in this day is 0.

  date        time      flag  target
0 2017/4/10   10:00:00  0     0
1 2017/4/10   11:00:00  1     1
2 2017/4/10   12:00:00  0     0
3 2017/4/10   13:00:00  0     0
4 2017/4/10   14:00:00  0     0
5 2017/4/11   10:00:00  1     1
6 2017/4/11   11:00:00  0     0
7 2017/4/11   12:00:00  1     0
8 2017/4/11   13:00:00  1     0
9 2017/4/11   14:00:00  0     0
10 2017/4/12  10:00:00  0     0
11 2017/4/12  11:00:00  0     0
12 2017/4/12  12:00:00  0     0
13 2017/4/12  13:00:00  0     0
14 2017/4/12  14:00:00  0     0
15 2017/4/13  10:00:00  0     0
16 2017/4/13  11:00:00  1     1
17 2017/4/13  12:00:00  0     0
18 2017/4/13  13:00:00  1     0
19 2017/4/13  14:00:00  0     0
pandas python
4 Answers

Compare DataFrameGroupBy.cumsum by 1 and chain codition compare flag by 1 with bitwise AND and convert to integer:

df['target1'] = (df.groupby('date')['flag'].cumsum().eq(1) & df['flag'].eq(1)).astype(int)
         date      time  flag  target  target1
0   2017/4/10  10:00:00     0       0        0
1   2017/4/10  11:00:00     1       1        1
2   2017/4/10  12:00:00     0       0        0
3   2017/4/10  13:00:00     0       0        0
4   2017/4/10  14:00:00     0       0        0
5   2017/4/11  10:00:00     1       1        1
6   2017/4/11  11:00:00     0       0        0
7   2017/4/11  12:00:00     1       0        0
8   2017/4/11  13:00:00     1       0        0
9   2017/4/11  14:00:00     0       0        0
10  2017/4/12  10:00:00     0       0        0
11  2017/4/12  11:00:00     0       0        0
12  2017/4/12  12:00:00     0       0        0
13  2017/4/12  13:00:00     0       0        0
14  2017/4/12  14:00:00     0       0        0
15  2017/4/13  10:00:00     0       0        0
16  2017/4/13  11:00:00     1       1        1
17  2017/4/13  12:00:00     0       0        0
18  2017/4/13  13:00:00     1       0        0
19  2017/4/13  14:00:00     0       0        0

Another solution:

df['target1'] = ((~df.loc[df['flag']==1, 'date'].duplicated())
                     .reindex(df.index, fill_value=False).astype(int))
user_29010020

First select top first record with flag=1 for each date and set target to 1

df1 = df[df.flag==1].groupby("date").head(1);
df1.target = 1

Then reset target to 0 for original dataframe and replace target value with newly created dataframe with target to 1 only for those records which are selected in first dataframe.

df.target = 0
df.update(df1.target)
user_57629280

This is easy to understand and use answer:

m=f[['date','flag']]
i=m[m.flag==1].drop_duplicates().index
f['target']=0
f['target'].iloc[i]=1
print(f.drop(columns={'index'})

Output:

         date      time  flag  target
0   2017/4/10  10:00:00     0       0
1   2017/4/10  11:00:00     1       1
2   2017/4/10  12:00:00     0       0
3   2017/4/10  13:00:00     0       0
4   2017/4/10  14:00:00     0       0
5   2017/4/11  10:00:00     1       1
6   2017/4/11  11:00:00     0       0
7   2017/4/11  12:00:00     1       0
8   2017/4/11  13:00:00     1       0
9   2017/4/11  14:00:00     0       0
10  2017/4/12  10:00:00     0       0
11  2017/4/12  11:00:00     0       0
12  2017/4/12  12:00:00     0       0
13  2017/4/12  13:00:00     0       0
14  2017/4/12  14:00:00     0       0
15  2017/4/13  10:00:00     0       0
16  2017/4/13  11:00:00     1       1
17  2017/4/13  12:00:00     0       0
18  2017/4/13  13:00:00     1       0
19  2017/4/13  14:00:00     0       0
user_21263570

Oneliner:

# Assuming your `df` doesn't have target in the first place
df['target'] = 0
df.loc[df[df.flag==1].drop_duplicates('date').index, 'target'] = 1
user_70937410
Related