How do I get the mean of celsius with based in the measued_at column?

user_7828402 0
Viewed 75

I want to get the expected output below. How do I use groupby or resampling to get the mean celcius by hour but still keep the minute values in the measured_at column?

My input:

 measured_at                  celsius
0 2020-05-19 01:13:40+00:00    15.00
1 2020-05-19 01:14:40+00:00    16.50
1 2020-05-20 02:13:26+00:00    30.00
2 2020-05-20 02:14:57+00:00    15.35
3 2020-05-20 02:15:19+00:00    14.00
4 2020-05-20 12:06:39+00:00    20.00
5 2020-05-21 03:13:07+00:00    15.50
6 2020-05-22 12:09:37+00:00    15.00


df['measured_at'] = pd.to_datetime(df.measured_at)
df1 = df.resample('60T', on='measured_at')['celsius'].mean().dropna().reset_index()

My output:

     measured_at    celsius
0 2020-05-19 01:00:00+00:00  15.750000
1 2020-05-20 02:00:00+00:00  19.783333
2 2020-05-20 12:00:00+00:00  20.000000
3 2020-05-21 03:00:00+00:00  15.500000
4 2020-05-22 12:00:00+00:00  15.000000

Expected output:

     measured_at    celsius
0 2020-05-19 01:13:00+00:00  15.750000
1 2020-05-20 02:13:00+00:00  19.783333
2 2020-05-20 12:06:00+00:00  20.000000
3 2020-05-21 03:13:00+00:00  15.500000
4 2020-05-22 12:09:00+00:00  15.000000
pandas python-3.x python
1 Answers

Here's the code for your use case.

I took out the minutes and seconds part so that they could be averaged and add back after the resampling.

Not sure what the +00:00 is for, if it is for better precision and you need it, you can convert into microseconds or nanoseconds instead.

import pandas as pd
from datetime import datetime

# Convert to datetime object
df['measured_at'] = df['measured_at'].apply(pd.to_datetime)

# Extract minutes and seconds as total seconds
df['seconds'] = df['measured_at'].apply(lambda x: (x.minute*60)+x.second)

# Resample to periods of one hour
df = df.resample('60T', on='measured_at').mean().dropna().reset_index()

# Add back average minutes for each period
df['measured_at'] = df['measured_at'] + pd.to_timedelta(df['seconds'].apply(int),'s')

# Remove seconds column
df = df.drop(columns='seconds')
user_87867700
Related