Search code examples
pythonpandasdataframedatetimestring-to-datetime

Time Series Data Reformat

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

output

enter image description here

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

Thanks,

Solution

  • 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