0
$\begingroup$

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.

$\endgroup$
0
$\begingroup$

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
$\endgroup$
1
  • $\begingroup$ I might be misunderstanding, but I don't think that would help me. It works for a small dataframe, but my problem consists of a dataframe with > 200,000 rows - the one posted was just a visual example to make the problem clear. $\endgroup$
    – RLB
    Sep 24 '21 at 19:49
0
$\begingroup$

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)
  1. 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)

  1. 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)
  1. 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.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.