CODEWITHSUNDEEP
pythonpandasdatedatetime
mas
mas

Reputation: 413

How to get all the historical values for particular days/times in pandas dataframe?

I want to get all the values for (a) particular day-time. I need this to calculate some sort of historical mean/std/.., which is day- and time-dependent from a pandas data frame. I rather explain the problem in codes. Let's say I have this dataframe:

df_datetime = pd.date_range(start='2010-01-01', end='2020-12-31', freq='30min')
df_ts = pd.DataFrame(data=np.random.random((df_datetime.shape)), index=df_datetime, columns=['Value'])
print(df_ts)

                        Value
2010-01-01 00:00:00  0.148690
2010-01-01 00:30:00  0.639023
2010-01-01 01:00:00  0.339820
2010-01-01 01:30:00  0.226052
2010-01-01 02:00:00  0.893710
...                       ...
2020-12-30 22:00:00  0.473275
2020-12-30 22:30:00  0.183648
2020-12-30 23:00:00  0.077264
2020-12-30 23:30:00  0.085483
2020-12-31 00:00:00  0.311474

[192817 rows x 1 columns]

Now I want all the values in df for, let's say, this day-time: XXXX-12-30 22:00:00. XXXX mean all years included. The way I do it is like this:

df_sample = df_ts.loc[(df_ts.index.month==12) & (df_ts.index.day==30) & (df_ts.index.hour==22) & (df_ts.index.minute==0)]
print(df_sample)

                        Value
2010-12-30 22:00:00  0.073103
2011-12-30 22:00:00  0.525378
2012-12-30 22:00:00  0.247066
2013-12-30 22:00:00  0.192340
2014-12-30 22:00:00  0.968341
2015-12-30 22:00:00  0.458732
2016-12-30 22:00:00  0.709913
2017-12-30 22:00:00  0.706581
2018-12-30 22:00:00  0.994208
2019-12-30 22:00:00  0.172340
2020-12-30 22:00:00  0.473275

which works fine for a single day-time, but I don't know an elegant way (not for loop) of doing this for several day-times, let's say for example:

[`XXXX-12-30 22:00:00`, `XXXX-12-30 22:30:00`, `XXXX-12-30 23:00:00`]

Upvotes: 1

Views: 1007

Answers (1)

Rick M
Rick M

Reputation: 1012

You can make a string out of the index and then use groupby to aggregate. That will give you your answer indexed by "XXXX-"etc, and you can grab the rows you want afterwards:

result = df_ts.groupby(df_ts.index.strftime("XXXX-%m-%d %H:%M:%S")).mean()['Value']

If you want a subset of the dates, you can use .loc:

new_range = pd.date_range(start='2021-01-01', end='2021-02-10', freq='30min', closed='left')
new_result = result.loc[new_range.strftime("XXXX-%m-%d %H:%M:%S")]

Upvotes: 1

Related Questions