Use pandas to unstack a pivot table and convert to a timestamp format

user_11863394 0
Viewed 72

I have the below pivot table which I want to unstack and convert to a timestamp format

date,00:00:00,00:15:00,00:30:00,00:45:00,01:00:00,01:15:00,01:30:00,01:45:00,02:00:00
2008-01-01,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0

Desired timestamp format

wordtimestamp value
2008-01-01 00:00:00,0.0
2008-01-01,00:15:00,0.0
2008-01-01,00:30:00,0.0
2008-01-01,00:45:00,0.0
2008-01-01,01:00:00,0.0
2008-01-01,01:15:00,0.0
2008-01-01,01:30:00,0.0
2008-01-01,01:45:00,0.0
2008-01-01,02:00:00,0.0

I have tried the below but I get an error. How can I fix this error or is there an alternative solution to achieve my solution.

import pandas as pd 
import numpy as np
from datetime import datetime

def unpivot_data(date,time):
    time = datetime.strptime(time, "%H:%M:%S").time()
    full_timestamp = datetime.combine(date, time)
    return full_timestamp

if __name__ == '__main__':
    df = pd.read_csv('pivot_data.csv')

    df = df.T.unstack().reset_index()
    df['wordtimestamp'] = map(unpivot_data, df.date, df.level_1)
    df.index = df.wordtimestamp
    df = df.drop(['date','level_1','wordtimestamp'],axis=1)
    df.columns = ["value"]
    print(df)

Error

Traceback (most recent call last): File "pivot.py", line 14, in df['wordtimestamp'] = map(unpivot_data, df.date, df.level_1) File "/home/.../.local/lib/python3.6/site-packages/pandas/core/generic.py", line 5141, in getattr return object.getattribute(self, name) AttributeError: 'DataFrame' object has no attribute 'date'

pandas dataframe pivot python
1 Answers

IIUC, you could do:

df = pd.read_csv('pivot_data.csv', index_col=0)
# here for demo
# df = pd.read_csv(io.StringIO(data), index_col=0)

df = df.stack()
df.index = pd.to_datetime([' '.join(i) for i in df.index])

df = df.rename_axis('wordtimestamp').reset_index(name='value')

output:

        wordtimestamp  value
0 2008-01-01 00:00:00    0.0
1 2008-01-01 00:15:00    0.0
2 2008-01-01 00:30:00    0.0
3 2008-01-01 00:45:00    0.0
4 2008-01-01 01:00:00    0.0
5 2008-01-01 01:15:00    0.0
6 2008-01-01 01:30:00    0.0
7 2008-01-01 01:45:00    0.0
8 2008-01-01 02:00:00    0.0

used input:

data = '''date,00:00:00,00:15:00,00:30:00,00:45:00,01:00:00,01:15:00,01:30:00,01:45:00,02:00:00
2008-01-01,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0'''
user_163434640
Related