Home > Software design >  Realise accumulated DataFrame from a column of Boolean values
Realise accumulated DataFrame from a column of Boolean values

Time:05-14

Be the following python pandas DataFrame:

ID Holidays visit_1 visit_2 visit_3 other
0 True 1 2 0 red
0 False 3 2 0 red
0 True 4 4 1 blue
1 False 2 0 0 red
1 True 1 2 1 green
2 False 1 0 0 red

Currently I calculate a new DataFrame with the accumulated visit values as follows.

# Calculate the columns of the total visit count
visit_df = df.groupby('ID')[['visit_1', 'visit_2', 'visit_3']].sum()

I would like to create a new one taking into account only the rows whose Holiday value is True. How could I do this?

Thanks for your attention.

CodePudding user response:

Simple subset the rows first:

df[df['Holidays']].groupby('ID')[['visit_1', 'visit_2', 'visit_3']].sum()

output:

    visit_1  visit_2  visit_3
ID                           
0         5        6        1
1         1        2        1

Alternative if you want to also get the groups without any match:

df2 = df.set_index('ID')

(df2.where(df2['Holidays'])
   .groupby('ID')[['visit_1', 'visit_2', 'visit_3']].sum()
)

output:

    visit_1  visit_2  visit_3
ID                           
0       5.0      6.0      1.0
1       1.0      2.0      1.0
2       0.0      0.0      0.0
variant
df2 = df.set_index('ID')

(df2.where(df2['Holidays'])
   .groupby('ID')[['visit_1', 'visit_2', 'visit_3']].sum()
   .convert_dtypes()
   .add_suffix('_Holidays')
)

output:

    visit_1_Holidays  visit_2_Holidays  visit_3_Holidays
ID                                                      
0                  5                 6                 1
1                  1                 2                 1
2                  0                 0                 0
  • Related