How to group by IDs and count the number of groups with occurrence of a variable after first point?

Question

Language: Python 3.8

I have a dataframe that consists of a series of people (each appearing multiple times in the dataframe), dates, and binary variables. I am trying to figure out how many people after a specific event (marked by one of the binary variables) went on to have other positive events. So for example, say the table looks something like this:

| ID |    Date  | Earthquake | Fire | Storm Damage |
|----|----------|------------|------|--------------|
| 1  |  1/21/21 |     0      |  0   |     0        |
| 2  |  2/3/21  |     1      |  0   |     0        |
| 3  |  2/4/21  |     0      |  1   |     0        |
| 1  |  2/10/21 |     1      |  0   |     0        |
| 1  |  2/28/21 |     0      |  1   |     1        |
| 2  |  3/5/21  |     0      |  0   |     1        | 

So in this example, after the first incidence of earthquake, one person went on to have a fire and two went on to have storm damage.

My problem is, I can't quite figure out how to do this. I think I need to use groupby to group all the IDs together, but I'm a bit stuck after that point.

Answer 1

I am not sure if you need to group all the records which would create a group by object, not a dataframe.

I initialized a new_df dataframe with the data you mentioned in your question and then used the group by code. However, I think you are looking for the the sorted the matrix.

Check out the code and the output mentioned below:

grouped = new_df.groupby("ID")
print(grouped.first())
print("Type of grouped:"+str(type(grouped)))
sorted_df = new_df.sort_values(["ID"], ascending=[1])
print("Type of Sort values:"+str(type(sorted_df)))
print(sorted_df)

Output:

ID     Date Earthquake Fire Storm Damage
0  1  1/21/21          0    0            0
1  2   2/3/21          1    0            0
2  3   2/4/21          0    1            0
3  1  2/10/21          1    0            0
4  1  2/28/21          0    1            1
5  2   3/5/21          0    0            1
       Date  Earthquake  Fire  Storm Damage
ID                                         
1   1/21/21           0     0             0
2    2/3/21           1     0             0
3    2/4/21           0     1             0
Type of grouped:<class 'pandas.core.groupby.generic.DataFrameGroupBy'>
Type of Sort values:<class 'pandas.core.frame.DataFrame'>
  ID     Date Earthquake Fire Storm Damage
0  1  1/21/21          0    0            0
3  1  2/10/21          1    0            0
4  1  2/28/21          0    1            1
1  2   2/3/21          1    0            0
5  2   3/5/21          0    0            1
2  3   2/4/21          0    1            0

Answer 2

I actually found what I think to be a good solution the other day, please let me know if I am missing something important.

1. First, I ensured that the matrix was completely sorted by date:

df.sort_values(by=['Date'], inplace=True)

2. Then, I used groupby followed by cumulative sum to create a variable that would count the number of instances up to the current point in the dataframe:

df['Earthquake_cumulative'] = df.groupby(['ID'])['Earthquake'].cumsum().astype(int)

3. I then dropped the rows with Earthquake's cumulative sum < 0, then did another cumulative sum for the other variables

df = df[df.Earthquake_cumulative >= 1]
df['Fire_cumulative'] = df.groupby(['ID'])['Fire'].cumsum().astype(int)
df['Storm Damage_cumulative'] = df.groupby(['ID'])['Storm Damage'].cumsum().astype(int)

4. Finally, I dropped all the duplicate IDs, keeping the final one (the last date) and counted the ones where Fire and Storm Damage were greater than 0.

df.drop_duplicates(subset='ID', keep='last')
dfFire = df[df["Fire_cumulative"] > 0]
dfStorm = df[df["Storm Damage_cumulative"] > 0]
num_rowsFire = dfFire.shape[0]
num_rowsStorm = dfStorm.shape[0]

I'm not sure if it's the most efficient method, but it seems to work as far as I can tell.