Average of CSV data-CodePudding

I am trying to process per-minute data into an hourly format from the following example data file:

...
$2021-01-27 14:22:59, 7877479, 8388606, -0.1059374, -17.6280, 26.75315, 17.09192, 85.2, 8.05, 97.63, 1.642691, 1.665497, 12.13, 1.621841, 1.541391
$2021-01-27 14:23:59, 7865387, 8388606, -0.2734679, -19.2070, 25.73683, 17.42454, 85.3, 8.05, 68.57, 1.642685, 1.660315, 9.52, 1.621823, 1.557271
$2021-01-27 14:24:59, 7853363, 8377689, -0.3185322, -2.0235, 24.95236, 17.73101, 84.9, 8.05, 54.95, 1.642676, 1.657235, 7.96, 1.621809, 1.564930
$2021-01-27 14:25:59, 7842250, 8357877, -0.2209551, 10.2949, 24.49805, 18.00294, 85.1, 8.05, 46.31, 1.642646, 1.655068, 6.67, 1.621802, 1.574302
...

The code I have so far to average one column is:

import csv
import pandas as pd
from pandas.io.parsers import read_fwf

dataFile = 'C:fileLoction'

df = pd.read_csv(dataFile)

def readFile():
    df['TS'] = pd.to_datetime(df['Timestamp'], format=r'%Y/%m/%d %H:%M')
    df.index = df['TS']
    df1 = df.resample('H').mean()
    print(df1)

readFile

However this does not change the data. Can anyone help me debug this?

CodePudding user response：

You should first remove "$" fromt the dates and then change the format in pd.to_datetime from '%Y/%m/%d %H:%M' to '%Y-%m-%d %H:%M':

df['Timestamp']=df['Timestamp'].str.replace('$','') #remove "$"

df['TS'] = pd.to_datetime(df['Timestamp'], format=r'%Y-%m-%d %H:%M')
df.index = df['TS']
df1 = df.resample('H').mean()
print(df1)