In python how to limit inserting a dummy to once per gvkey?

user_11738437 0
Viewed 32

I have the following code which produces a one every time notch_sp goes from 11 or higher to 10 and lower, but I only want it to produce a 1 the first time it happens per gvkey.

df['cliff']= (df['gvkey'].eq(df['gvkey'].shift()) &
              df['notch_sp'].shift().gt(10) &
              df['notch_sp'].lt(11)).view('i1')

For a dataset looking like this:

df[['gvkey','notch_sp','am','cliff']]
Out[1]: 
            gvkey    notch_sp    am     cliff
1        1004.0        12.0    0.490      0
2        1004.0        10.0    0.490      1
3        1004.0        11.0    0.608      0
4        1004.0        10.0    0.608      1
5        329498.0      12.0      NaN      0
6        329498.0      10.0      NaN      1
7        329498.0      13.0      NaN      0
8        329498.0      10.0      NaN      1

So cliff needs to be one at row 2 and 6 and zero everywhere else. I'm sorry I don't have any input on an idea to do so, but I just have no idea how to.

pandas for-loop python loops
1 Answers

Use GroupBy.cumsum compared by 1 for first group and chain by original by & for bitwise AND:

df['cliff']= (df['gvkey'].eq(df['gvkey'].shift()) &
              df['notch_sp'].shift().gt(10) &
              df['notch_sp'].lt(11))
df['cliff'] = (df.groupby('gvkey')['cliff'].cumsum().eq(1) & df['cliff']).view('i1')
print (df)
      gvkey  notch_sp     am  cliff
1    1004.0      12.0  0.490      0
2    1004.0      10.0  0.490      1
3    1004.0      11.0  0.608      0
4    1004.0      10.0  0.608      0
5  329498.0      12.0    NaN      0
6  329498.0      10.0    NaN      1
7  329498.0      13.0    NaN      0
8  329498.0      10.0    NaN      0
user_29010020
Related