convert timeseries to dataframe in Python

Question

I have a timeseries dataset which has two columns. the first column is timestamp and the second is the data. The timestamp is ordered by minutes and covers the entire year.

Timestamp           Data
1/1/2017   0:00        50
1/1/2017   0:01        80
...
12/31/2017 23:59     100

So now I would like the dataset to be rearranged to a table that each column stands for different days, but rows still stand for data for each minute. The new dataset will be like

      1/1  1/2  1/3 .......  12/31
0:00  50   60   34  .......  67
0:01  34   211  90  .......  90
...
23:59

So is there a way to do this fast?

You have to try to do something yourself first, and then if you need advice on specific problems then people may be able to help - but they will not jut do the job for you I'm afraid. — LordWilmore
– LordWilmore, Commented Feb 23, 2018 at 16:05
I am doing it manually. Find the timestamp mark a new day and manually add a new column to move the data around. It is in excel and I know it is silly, so that's why I want to find a more efficient way to do it. — Pythonqi
– Pythonqi, Commented Feb 23, 2018 at 16:15

Silenced Temporarily · Accepted Answer · 2018-02-23 17:06:25Z

2

Begin by splitting your "Timestamp" column into date and time. How to do this depends on whether it's truly a timestamp or a string. If it is a timestamp, do (using 'Date' as the column name to avoid confusion with the pandas builtin 'Timestamp'

df['Day'] = df['Date'].map(pd.Timestamp.date)
df['Time'] = df['Date'].map(pd.Timestamp.time)
del df['Date']

Follow this with a pivot_table:

pd.pivot_table(df, values='Data', index=['Time'], columns=['Day'])

answered Feb 23, 2018 at 17:06

Silenced Temporarily

1,00411 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

convert timeseries to dataframe in Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related