Cyrine Ezzahra Cyrine Ezzahra - 3 months ago 12
Python Question

How to use Groupby with condition in Python

I have a dataframe called merged_df_energy

merged_df_energy.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 11232 entries, 0 to 11231
Data columns (total 17 columns):
TIMESTAMP 11232 non-null datetime64[ns]
P_ACT_KW 11232 non-null int64
PERIODE_TARIF 11232 non-null object
P_SOUSCR 11232 non-null int64
high_energy 11232 non-null int64
medium_energy 11232 non-null int64
low_energy 11232 non-null int64
0ACT_TIME_ETA_PRG_P2REF_RM 11232 non-null int64
0ACT_TIME_ETA_PRG_VDES_RM 11232 non-null int64
0ACT_TIME_ETA_PRG_P3REF_RM 11232 non-null int64
0ACT_TIME_ETA_POMP_RECIRC_N1 11232 non-null int64
0ACT_TIME_ETA_POMP_RECIRC_N2 11232 non-null int64
0ACT_TIME_ETA_POMP_RECIRC_N3 11232 non-null int64
0ACT_TIME_ETA_SURPRES_AIR_N1 11232 non-null int64
0ACT_TIME_ETA_SURPRES_AIR_N2 11232 non-null int64
0ACT_TIME_ETA_SURPRES_AIR_N3 11232 non-null int64
class_energy 11232 non-null object
dtypes: datetime64[ns](1), int64(14), object(2)
memory usage: 1.5+ MB


with this structure :

TIMESTAMP P_ACT_KW PERIODE_TARIF P_SOUSCR high_energy medium_energy low_energy 0ACT_TIME_ETA_PRG_P2REF_RM 0ACT_TIME_ETA_PRG_VDES_RM
0ACT_TIME_ETA_PRG_P3REF_RM 0ACT_TIME_ETA_POMP_RECIRC_N1 0ACT_TIME_ETA_POMP_RECIRC_N2 0ACT_TIME_ETA_POMP_RECIRC_N3
0ACT_TIME_ETA_SURPRES_AIR_N1 0ACT_TIME_ETA_SURPRES_AIR_N2
0ACT_TIME_ETA_SURPRES_AIR_N3 class_energy


2016-05-10 04:30:00 107 HP 250 107 0 0 100 0 0 0 0 0 0 0 0 high

2016-05-10 04:40:00 109 HC 250 109 0 0 0 0 100 0 0 0 0 0 0 high

2016-05-10 04:50:00 106 HP 250 106 0 0 0 0 100 0 0 0 0 0 0 high


I try to calculate the sum of (0ACT_TIME_ETA_PRG_P2REF_RM, 0ACT_TIME_ETA_PRG_VDES_RM, 0ACT_TIME_ETA_PRG_P3REF_RM, 0ACT_TIME_ETA_POMP_RECIRC_N1 0ACT_TIME_ETA_POMP_RECIRC_N2, 0ACT_TIME_ETA_POMP_RECIRC_N3, 0ACT_TIME_ETA_SURPRES_AIR_N1, 0ACT_TIME_ETA_SURPRES_AIR_N2, 0ACT_TIME_ETA_SURPRES_AIR_N3 class_energy) group by (class_energy).

For this I did :

df_F1 = (merged_df_energy.groupby(by=['class_energy'], as_index=False)['0ACT_TIME_ETA_PRG_P2REF_RM', '0ACT_TIME_ETA_PRG_VDES_RM','0ACT_TIME_ETA_PRG_P3REF_RM','0ACT_TIME_ETA_POMP_RECIRC_N1','0ACT_TIME_ETA_POMP_RECIRC_N2', '0ACT_TIME_ETA_POMP_RECIRC_N3', '0ACT_TIME_ETA_SURPRES_AIR_N1', '0ACT_TIME_ETA_SURPRES_AIR_N2', '0ACT_TIME_ETA_SURPRES_AIR_N3' ].sum())


It works fine, but I would like to know how can I do this with this condition ( if PERIODE_TARIF = 'HP') ?

Answer

I think you need before groupby boolean indexing:

merged_df_energy1 = merged_df_energy[merged_df_energy.PERIODE_TARIF == 'HP']

cols = ['0ACT_TIME_ETA_PRG_P2REF_RM', 
       '0ACT_TIME_ETA_PRG_VDES_RM',
       '0ACT_TIME_ETA_PRG_P3REF_RM',
       '0ACT_TIME_ETA_POMP_RECIRC_N1',
       '0ACT_TIME_ETA_POMP_RECIRC_N2', 
       '0ACT_TIME_ETA_POMP_RECIRC_N3', 
       '0ACT_TIME_ETA_SURPRES_AIR_N1', 
       '0ACT_TIME_ETA_SURPRES_AIR_N2', 
       '0ACT_TIME_ETA_SURPRES_AIR_N3']
df_F1 = (merged_df_energy1.groupby(by=['class_energy'], as_index=False)[cols].sum())

print (df_F1)
  class_energy  0ACT_TIME_ETA_PRG_P2REF_RM  0ACT_TIME_ETA_PRG_VDES_RM  \
0         high                         100                          0   

   0ACT_TIME_ETA_PRG_P3REF_RM  0ACT_TIME_ETA_POMP_RECIRC_N1  \
0                         100                             0   

   0ACT_TIME_ETA_POMP_RECIRC_N2  0ACT_TIME_ETA_POMP_RECIRC_N3  \
0                             0                             0   

   0ACT_TIME_ETA_SURPRES_AIR_N1  0ACT_TIME_ETA_SURPRES_AIR_N2  \
0                             0                             0   

   0ACT_TIME_ETA_SURPRES_AIR_N3  
0                             0  

EDIT:

If order of columns is never changed, you can use:

cols = merged_df_energy.columns[7:16]
print (cols)
Index(['0ACT_TIME_ETA_PRG_P2REF_RM', '0ACT_TIME_ETA_PRG_VDES_RM',
       '0ACT_TIME_ETA_PRG_P3REF_RM', '0ACT_TIME_ETA_POMP_RECIRC_N1',
       '0ACT_TIME_ETA_POMP_RECIRC_N2', '0ACT_TIME_ETA_POMP_RECIRC_N3',
       '0ACT_TIME_ETA_SURPRES_AIR_N1', '0ACT_TIME_ETA_SURPRES_AIR_N2',
       '0ACT_TIME_ETA_SURPRES_AIR_N3'],
      dtype='object')
Comments