I have a dataframe which I pivoted and I now want to select spefici rows from the data. I have seen similar questions such as the one here: Selecting columns in a pandas pivot table based on specific row value?. In my case I want to return all the columns but I want to select only specific rows.
timestamp,value 2008-03-01 00:00:00,55.0 2008-03-01 00:15:00,20.0 2008-03-01 00:30:00,13.0 2008-03-01 00:45:00,78.0 2008-03-01 01:00:00,34.0 2008-03-01 01:15:00,123.0 2008-03-01 01:30:00,25.0 2008-03-01 01:45:00,91.0 2008-03-02 00:00:00,55.0 2008-03-02 00:15:00,46.0 2008-03-02 00:30:00,66.0 2008-03-02 00:45:00,24.0 2008-03-02 01:00:00,70.0 2008-03-02 01:15:00,32.0 2008-03-02 01:30:00,15.0 2008-03-02 01:45:00,92.0
I have done the below to generate the below output
import pandas as pd
import numpy as np
from datetime import datetime
df = pd.read_csv('df.csv')
df.timestamp = pd.to_datetime(df.timestamp)
df = df.set_index('timestamp')
df['date'] = df.index.map(lambda t: t.date())
df['time'] = df.index.map(lambda t: t.time())
df_pivot = pd.pivot_table(df, values='value', index='timestamp', columns='time')
df_pivot = df_pivot.fillna(0.0)
print(df_pivot)
Generated output
time 00:00:00 00:15:00 00:30:00 00:45:00 01:00:00 01:15:00 01:30:00 01:45:00 timestamp 2008-03-01 00:00:00 55.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 2008-03-01 00:15:00 0.0 20.0 0.0 0.0 0.0 0.0 0.0 0.0 2008-03-01 00:30:00 0.0 0.0 13.0 0.0 0.0 0.0 0.0 0.0 2008-03-01 00:45:00 0.0 0.0 0.0 78.0 0.0 0.0 0.0 0.0 2008-03-01 01:00:00 0.0 0.0 0.0 0.0 34.0 0.0 0.0 0.0 2008-03-01 01:15:00 0.0 0.0 0.0 0.0 0.0 123.0 0.0 0.0 2008-03-01 01:30:00 0.0 0.0 0.0 0.0 0.0 0.0 25.0 0.0 2008-03-01 01:45:00 0.0 0.0 0.0 0.0 0.0 0.0 0.0 91.0 2008-03-02 00:00:00 55.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 2008-03-02 00:15:00 0.0 46.0 0.0 0.0 0.0 0.0 0.0 0.0 2008-03-02 00:30:00 0.0 0.0 66.0 0.0 0.0 0.0 0.0 0.0 2008-03-02 00:45:00 0.0 0.0 0.0 24.0 0.0 0.0 0.0 0.0 2008-03-02 01:00:00 0.0 0.0 0.0 0.0 70.0 0.0 0.0 0.0 2008-03-02 01:15:00 0.0 0.0 0.0 0.0 0.0 32.0 0.0 0.0 2008-03-02 01:30:00 0.0 0.0 0.0 0.0 0.0 0.0 15.0 0.0 2008-03-02 01:45:00 0.0 0.0 0.0 0.0 0.0 0.0 0.0 92.0
I want to select e.g., only the data for 2008-03-01 00:00:00, 2008-03-01 01:15:00, and 2008-03-02 01:00:00.
Expected output
time 00:00:00 00:15:00 00:30:00 00:45:00 01:00:00 01:15:00 01:30:00 01:45:00 timestamp 2008-03-01 00:00:00 55.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 2008-03-01 01:15:00 0.0 0.0 0.0 0.0 0.0 123.0 0.0 0.0 2008-03-02 01:00:00 0.0 0.0 0.0 0.0 70.0 0.0 0.0 0.0
How can I do that
Advertisement
Answer
Use list of datetimes converted by to_datetime and select by DataFrame.loc:
#create DatetimeIndex
df = pd.read_csv('df.csv', index_col='timestamp', parse_dates=['timestamp'])
#used pandas methods
df['date'] = df.index.date
df['time'] = df.index.time
#added fill_value parameter
df_pivot = pd.pivot_table(df,values='value',index='timestamp',columns='time',fill_value=0)
L = ['2008-03-01 00:00:00','2008-03-01 01:15:00','2008-03-02 01:00:00']
df = df_pivot.loc[pd.to_datetime(L)]
print (df)
time 00:00:00 00:15:00 00:30:00 00:45:00 01:00:00
2008-03-01 00:00:00 55 0 0 0 0
2008-03-01 01:15:00 0 0 0 0 0
2008-03-02 01:00:00 0 0 0 0 70
time 01:15:00 01:30:00 01:45:00
2008-03-01 00:00:00 0 0 0
2008-03-01 01:15:00 123 0 0
2008-03-02 01:00:00 0 0 0