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!
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