Pandas Resample 5 mins data to Hourly average : Date issue [duplicate]-CodePudding

I am trying to resample a timeseries data from 5 min frequency to hourly average.

 df = pd.read_csv("my_data.csv", index_col=False, usecols=['A','B','C'])

output:

         A            B      C
0   16-01-21 0:00   95.75   0.0
1   16-01-21 0:05   90.10   0.0
2   16-01-21 0:10   86.26   0.0
3   16-01-21 0:15   92.72   0.0
4   16-01-21 0:20   81.54   0.0

df.A= pd.to_datetime(df.A)

Output:

             A            B      C
0   2021-01-16 00:00:00 95.75   0.0
1   2021-01-16 00:05:00 90.10   0.0
2   2021-01-16 00:10:00 86.26   0.0
3   2021-01-16 00:15:00 92.72   0.0
4   2021-01-16 00:20:00 81.54   0.0

Now I set the Timestamp column as index,

df.set_index('A', inplace=True)

And when I try to resample with

df2 = df.resample('H').mean()

I am getting this,

                       B         C
        A       
2021-01-02 00:00:00 79.970278   0.0
2021-01-02 01:00:00 77.951667   0.0
2021-01-02 02:00:00 77.610556   0.0
2021-01-02 03:00:00 80.800000   0.0
2021-01-02 04:00:00 84.305000   0.0

Was expecting this kind of timestamp with the average values for each hour,

       A             B         C
2021-01-16 00:00:00 79.970278   0.0
2021-01-16 01:00:00 77.951667   0.0
2021-01-16 02:00:00 77.610556   0.0
2021-01-16 03:00:00 80.800000   0.0
2021-01-16 04:00:00 84.305000   0.0

I am not sure where I am making a mistake. Help me out.

CodePudding user response：

I think problem here is some datetimes are wrongly converted:

#default is month first in df.A= pd.to_datetime(df.A)
01-02-21 -> 2021-01-02

Possible solutions:

df.A= pd.to_datetime(df.A, dayfirst=True)

Or:

df = pd.read_csv("my_data.csv", 
                 index_col=False, 
                 usecols=['A','B','C'], 
                 parse_dates=['A'], 
                 dayfirst=True)