CODEWITHSUNDEEP
pythonpandasdataframe
user12513715
user12513715

Reputation:

python pandas mean by hour of day

I'm working with the following dataset with hourly counts in columns. The dataframe has more than 1400 columns and 100 rows.

My dataset looks like this:

CITY      2019-10-01  00:00     2019-10-01  01:00    2019-10-01  02:00     ....      2019-12-01  12:00

Wien            15                    16                       16          ....              14                                                              
Graz            11                    11                       11          ....              10
Innsbruck       12                    12                       10          ....              12
  ....

How can I convert this datatime to datetime such as this:

CITY          2019-10-01         2019-10-02               2019-10-03       ....          2019-12-01 
              (or 1 day)         (or 2 day)               (or 3 day)                     (or 72 day)
Wien            14                    15                      16           ....              12                                                              
Graz            13                    12                      14           ....              10
Innsbruck       13                    12                      12           ....              12
  ....

I would like the average of all hours of the day to be in the column of the one day. The data type is:

type(df.columns[0])
out: str

type(df.columns[1])
out: pandas._libs.tslibs.timestamps.Timestamp

Thanks for your help!

Upvotes: 0

Views: 462

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150805

I would do something like this:

days = df.columns[1:].to_series().dt.normalize()
df.set_index('CITY').groupby(days, axis=1).mean()

Output:

           2019-10-01  2019-12-01
CITY                             
Wien        15.666667        14.0
Salzburg    12.000000        14.0
Graz        11.000000        10.0
Innsbruck   11.333333        12.0

Upvotes: 1

Related Questions