1

I have a dataframe of DateTime (index) and a sampling of power usage:

DateTime          Usage
01-Jan-17 12am    10
01-Jan-17 3am     5
01-Jan-17 6am     15
01-Jan-17 9am     40
01-Jan-17 12pm    60
01-Jan-17 3pm     62
01-Jan-17 6pm     45
01-Jan-17 9pm     18
02-Jan-17 12am    11
02-Jan-17 3am     4
02-Jan-17 6am     17
02-Jan-17 9am     37
02-Jan-17 12pm    64
02-Jan-17 3pm     68
02-Jan-17 6pm     41
02-Jan-17 9pm     16

In reality, this series is much longer. I am trying to compare day-over-day time periods, such that I can look at the daily-seasonality of the time series. Is there a way in panda's to split the data such that you can compare these time series? I'd imagine the resulting DataFrame would look something like:

Time    1-Jan   2-Jan
12am    10      11
3am     5       4
6am     15      17
9am     40      37
12pm    60      64
3pm     62      68
6pm     45      41
9pm     18      16

Thanks!

Improve this question

1 Answer 1

Reset to default
1

Assuming you have DateTime as str data type, you can split it into Date and Time and then pivot it:

df[['Date', 'Time']] = df.DateTime.str.split(" ", expand=True)
df1 = df.pivot("Time", "Date", "Usage").reset_index()

enter image description here

How to sort the Time column? It's actually not so straight forward, to do this, we need to extract some columns from the Time, the hour, the PM/AM indicator as well as if the hour is 12, as 12 should be placed above all other hours:

# use regex to extract Hour (numeric part of Time) and AM/PM indicator
hourInd = df1.Time.str.extract("(?P<Hour>\d+)(?P<Ind>[pa]m)", expand=True)

# convert the hour column to integer and create another column to check if hour is 12
# then sort by AM/PM indicator, IsTwelve and Hour and get the index to reorder the original 
# data frame
df1.loc[(hourInd.assign(Hour = hourInd.Hour.astype(int), IsTwelve = hourInd.Hour != "12")
         .sort_values(["Ind", "IsTwelve", "Hour"]).index)]

enter image description here

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.