How to extract the first row for each hour from a pandas dataframe to a new dataframe

user_1273782 0
Viewed 868

I've a dataframe with Datetime index and other 3 columns, something like this:

import numpy as np
df = pd.DataFrame({'DateTime': pd.date_range('1/1/2012', periods=20, freq=pd.offsets.Minute(n=15))})
df['Data1'] = np.random.randint(0, 500, len(df))
df['Data2'] = np.random.randint(0, 500, len(df))
df['Data3'] = np.random.randint(0, 500, len(df))


              DateTime  Data1  Data2  Data3
0  2012-01-01 00:00:00    130    297    240
1  2012-01-01 00:15:00    408    121    480
2  2012-01-01 00:30:00    120    341     67
3  2012-01-01 00:45:00     80    123    418
4  2012-01-01 01:00:00    413    394    242
5  2012-01-01 01:15:00    339    338    440
6  2012-01-01 01:30:00    101    435    414
7  2012-01-01 01:45:00    359     55    449
8  2012-01-01 02:00:00    122    267    217
9  2012-01-01 02:15:00    117     60      1
10 2012-01-01 02:30:00    456    167    178
11 2012-01-01 02:45:00    236    256     84
12 2012-01-01 03:00:00    308    187    110
13 2012-01-01 03:15:00    426    134      1
14 2012-01-01 03:30:00    185    424    472
15 2012-01-01 03:45:00    269    198    346
16 2012-01-01 04:00:00    213    349    166
17 2012-01-01 04:15:00    477     77    406
18 2012-01-01 04:30:00    412    210    492
19 2012-01-01 04:45:00     44    337     88

How could I extract the first row for each hour to a new dataframe? I don't want sum or avg. Just the first row for each hour like below.

              DateTime  Data1  Data2  Data3
0  2012-01-01 00:00:00    130    297    240
1  2012-01-01 01:00:00    413    394    242
2  2012-01-01 02:00:00    122    267    217
3  2012-01-01 03:00:00    308    187    110
4  2012-01-01 04:00:00    213    349    166
pandas python
4 Answers

You can use pd.Grouper() under df.groupby() with keys as the column name to group:

df.groupby(pd.Grouper(key='DateTime',freq='60Min')).first().reset_index()

             DateTime  Data1  Data2  Data3
0 2012-01-01 00:00:00    130    297    240
1 2012-01-01 01:00:00    413    394    242
2 2012-01-01 02:00:00    122    267    217
3 2012-01-01 03:00:00    308    187    110
4 2012-01-01 04:00:00    213    349    166
user_98406370

I'd use:

df.groupby(df.DateTime.dt.floor('H')).first()

Your sample dataframe above, does not have a DatetimeIndex, you have a column with dtype of datetime but it is not in the index. But, if you move DateTime into the index the you can do the same using this syntax:

df = df.set_index('DateTime')
df.loc[df.index.floor('H').drop_duplicates()]

or

df.groupby(df.index.floor('H')).first()
user_63615310

Another option is using Series.duplicated which involves minimal modification to your DataFrame:

df[~df['DateTime'].dt.floor('H').duplicated()]

              DateTime  Data1  Data2  Data3
0  2012-01-01 00:00:00    130    297    240
4  2012-01-01 01:00:00    413    394    242
8  2012-01-01 02:00:00    122    267    217
12 2012-01-01 03:00:00    308    187    110
16 2012-01-01 04:00:00    213    349    166
user_49090870

if there is no change in seconds will happen

new_df=df[df.DateTime.apply(lambda x: x.minute==0)]
user_55028660
Related