CSV数据的每小时平均数据

My data is in CSV format which is minute resolution. It looks like

我的数据是CSV格式,分辨率很小。看起来像

Timestamp           value
6/10/2018 0:00       23.9
6/10/2018 0:01       19.8
6/10/2018 0:02       20.3
-------------------------
-------------------------
6/18/2018 23:59      25.9

Now I need the hourly average of this data. The code I have done so far is

现在我需要这个数据的每小时平均值。我到目前为止所做的代码是

import pandas as pd

df = pd.read_csv("filename.csv")
df['DateTime'] = pd.to_datetime(df['Timestamp'])
df.index = df['DateTime']
df1 = df.resample('H').mean()
print(df1)

But the output is not correct which is as

但是输出不正确就是这样

DateTime               Value
2018-06-13 00:00:00    16.19
2018-06-13 01:00:00    20.80
----------------------------
----------------------------
2018-12-06 23:00:00    19.09

The date is far from the actual data table. So please help me to debug it.

日期远离实际数据表。所以请帮我调试一下。

2 个解决方案

#1

Try this

df["DateTime"] = pd.to_datetime(df['Timestamp'], format="%d/%m/%Y %H:%M")

instead this

df['DateTime'] = pd.to_datetime(df['Timestamp'])

#2

pandas has trouble parsing your Datetime column, probably because the string representation begins with the month. I think pandas assumes it is day-first until it is no longer possible, then it goes month-first.

pandas无法解析您的Datetime列,可能是因为字符串表示以月份开头。我认为大熊猫假设它是第一天,直到它不再可能,然后它会以月为先。

You should specify a format string :

您应该指定格式字符串:

df['DateTime'] = pd.to_datetime(df['Timestamp'], format='%m/%d/%Y %H:%M')

Conventions for string format are in this page :
https://docs.python.org/3/library/datetime.html#strftime-strptime-behavior

字符串格式的约定在此页面:https://docs.python.org/3/library/datetime.html#strftime-strptime-behavior

#1