Time Series Data Reformat-CodePudding

I am working on some code that will rearrange a time series. Currently I have a standard time series. I have a three columns with with the header being [Date, Time, Value]. I want to reformat the dataframe to index with the date and use a header with the time (i.e. 0:00, 1:00, ... , 23:00). The dataframe will be filled in with the value.

Here is the Dataframe currently have

essentially I'd like to mve the index toa single day and show the hours through the columns.

Thanks,

CodePudding user response：

Use pivot:

df = df.pivot(index='Date', columns='Time', values='Total')

Output (first 10 columns and with random values for Total):

>>> df.pivot(index='Date', columns='Time', values='Total').iloc[0:10]
time        00:00:00  01:00:00  02:00:00  03:00:00  04:00:00  05:00:00  06:00:00  07:00:00  08:00:00  09:00:00
date                                                                                                          
2019-01-01  0.732494  0.087657  0.930405  0.958965  0.531928  0.891228  0.664634  0.432684  0.009653  0.604878
2019-01-02  0.471386  0.575126  0.509707  0.715290  0.337983  0.618632  0.413530  0.849033  0.725556  0.186876

CodePudding user response：

You could try this. Split the time part to get only the hour. Add hr to it.

df = pd.DataFrame([['2019-01-01', '00:00:00',-127.57],['2019-01-01', '01:00:00',-137.57],['2019-01-02', '00:00:00',-147.57],], columns=['Date', 'Time', 'Totals'])
df['hours'] = df['Time'].apply(lambda x: 'hr'  str(int(x.split(':')[0])))
print(pd.pivot_table(df, values ='Totals', index=['Date'], columns = 'hours'))

Output

hours          hr0     hr1
Date                      
2019-01-01 -127.57 -137.57
2019-01-02 -147.57     NaN