'Fill forward' dummy variable for observations in same group (Python)

Question

I've created a dummy variable (in Python), seo, which takes the value 1 if the value of another column is greater than 0, as shown in the code below.

df['seo'] = (df['amount'] > 0).astype(int)

What I want to do is to create a second dummy variable, past_seo, which takes the value 1 if the seo dummy for a particular firm was 1 at any historical time.

For reference, my dataset comprises monthly firm data and contains a firm identifier variable (6_cusip).

What I tried to do was to group the dataset by 6_cusip and date, and then "fill forward" the seo dummy variable. However, I couldn't get this to work.

The code below shows an example of the first 20 observations in my dataset. As shown, the observations are all from the same firm. What I want to do is create a new column which fills that '1' in the seo column forward to all subsequent observations belonging to the same firm.

{'date': {0: '1994-05',
  1: '1994-06',
  2: '1994-07',
  3: '1994-08',
  4: '1994-09',
  5: '1994-10',
  6: '1994-11',
  7: '1994-12',
  8: '1995-01',
  9: '1995-02',
  10: '1995-03',
  11: '1995-04',
  12: '1995-05',
  13: '1995-06',
  14: '1995-07',
  15: '1995-08',
  16: '1995-09',
  17: '1995-10',
  18: '1995-11',
  19: '1995-12'},
 '6_cusip': {0: '00077R',
  1: '00077R',
  2: '00077R',
  3: '00077R',
  4: '00077R',
  5: '00077R',
  6: '00077R',
  7: '00077R',
  8: '00077R',
  9: '00077R',
  10: '00077R',
  11: '00077R',
  12: '00077R',
  13: '00077R',
  14: '00077R',
  15: '00077R',
  16: '00077R',
  17: '00077R',
  18: '00077R',
  19: '00077R'},
 'seo': {0: 0,
  1: 0,
  2: 0,
  3: 0,
  4: 0,
  5: 0,
  6: 0,
  7: 0,
  8: 0,
  9: 0,
  10: 0,
  11: 0,
  12: 0,
  13: 0,
  14: 0,
  15: 1,
  16: 0,
  17: 0,
  18: 0,
  19: 0}}

Let me know if you have any advice, thanks!

please provide a sample of your data, maybe df.head(20).to_dict() in your question — anon01

anon01 anon01 · Accepted Answer · 2020-12-06T08:01:00

I think this should work:

df["past_seo"] = df.groupby("6_cusip").seo.cumsum().gt(0).astype(int)

Basically, cumulatively sum seo for each group, flag as true if it's greater than 1 and cast as an integer.

output:

       date 6_cusip  seo  past_seo
0   1994-05  00077R    0         0
1   1994-06  00077R    0         0
2   1994-07  00077R    0         0
3   1994-08  00077R    0         0
4   1994-09  00077R    0         0
5   1994-10  00077R    0         0
6   1994-11  00077R    0         0
7   1994-12  00077R    0         0
8   1995-01  00077R    0         0
9   1995-02  00077R    0         0
10  1995-03  00077R    0         0
11  1995-04  00077R    0         0
12  1995-05  00077R    0         0
13  1995-06  00077R    0         0
14  1995-07  00077R    0         0
15  1995-08  00077R    1         1
16  1995-09  00077R    0         1
17  1995-10  00077R    0         1
18  1995-11  00077R    0         1
19  1995-12  00077R    0         1

'Fill forward' dummy variable for observations in same group (Python)

1 Answers