Time Series Data Reformat

Question

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,

Will you please provide your data as text, not an image? I can't copy/paste from an image. — , Mar 14 '22 at 16:42
Does this answer your question? [How can I pivot a dataframe?](https://stackoverflow.com/questions/47152691/how-can-i-pivot-a-dataframe) — , Mar 14 '22 at 17:15

score 1 · Accepted Answer · answered Mar 14 '22 at 17:04

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

score 0 · Answer 2 · answered Mar 14 '22 at 17:11

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

Time Series Data Reformat

2 Answers2