CODEWITHSUNDEEP
pythonpandasdatetime
Pinky
Pinky

Reputation: 231

Pandas split hourly data to 15 minute interval data

I have a csv file which has temperature, humidity data for every hour in a day for a span of 2 years. I want to split this data into 15minute interval data by subtracting the difference of temperature and humidity between hours and dividing the difference by 4 (in order to get 15minute interval data) How to achieve this in pandas?

Below is the data sample

Location,Temperature,Humidity,Date,Hour
WA,70.403,73.493,2019-03-01,0
WA,71.593,73.153,2019-03-01,1
NY,73.131,74.93,2019-03-01,0
NY,73.085,73.161,2019-03-01,1

Upvotes: 0

Views: 2327

Answers (2)

jezrael
jezrael

Reputation: 862511

Out of box solution with concat and create DatetimeIndex, last soring per column and index with divide both columns by 4:

df = pd.concat([df.assign(minute='0'),
                df.assign(minute = '15'),
                df.assign(minute = '30'),
                df.assign(minute = '45')])

df.index = pd.to_datetime(df['Date'].astype(str) +
                          df['Hour'].astype(str) + 
                          df['minute'], format='%Y-%m-%d%H%M')

df = df.rename_axis('datetimes').sort_values(['Location','datetimes'])

df[['Temperature','Humidity']] /= 4
print (df)
                    Location  Temperature  Humidity        Date  Hour minute
datetimes                                                                   
2019-03-01 00:00:00       NY     18.28275  18.73250  2019-03-01     0      0
2019-03-01 01:00:00       NY     18.27125  18.29025  2019-03-01     1      0
2019-03-01 01:05:00       NY     18.28275  18.73250  2019-03-01     0     15
2019-03-01 03:00:00       NY     18.28275  18.73250  2019-03-01     0     30
2019-03-01 04:05:00       NY     18.28275  18.73250  2019-03-01     0     45
2019-03-01 11:05:00       NY     18.27125  18.29025  2019-03-01     1     15
2019-03-01 13:00:00       NY     18.27125  18.29025  2019-03-01     1     30
2019-03-01 14:05:00       NY     18.27125  18.29025  2019-03-01     1     45
2019-03-01 00:00:00       WA     17.60075  18.37325  2019-03-01     0      0
2019-03-01 01:00:00       WA     17.89825  18.28825  2019-03-01     1      0
2019-03-01 01:05:00       WA     17.60075  18.37325  2019-03-01     0     15
2019-03-01 03:00:00       WA     17.60075  18.37325  2019-03-01     0     30
2019-03-01 04:05:00       WA     17.60075  18.37325  2019-03-01     0     45
2019-03-01 11:05:00       WA     17.89825  18.28825  2019-03-01     1     15
2019-03-01 13:00:00       WA     17.89825  18.28825  2019-03-01     1     30
2019-03-01 14:05:00       WA     17.89825  18.28825  2019-03-01     1     45

If last days per groups should be not contains 15, 30 and 45 minutes:

df.index = pd.to_datetime(df['Date'].astype(str) + df['Hour'].astype(str), 
                          format='%Y-%m-%d%H')

df = (df.groupby('Location').resample('15Min')[['Temperature','Humidity']]
        .ffill()
        .rename_axis(['Location','Datetime'])
        .reset_index(level=0))

df[['Temperature','Humidity']] /= 4
print (df)
                    Location  Temperature  Humidity
Datetime                                           
2019-03-01 00:00:00       NY     18.28275  18.73250
2019-03-01 00:15:00       NY     18.28275  18.73250
2019-03-01 00:30:00       NY     18.28275  18.73250
2019-03-01 00:45:00       NY     18.28275  18.73250
2019-03-01 01:00:00       NY     18.27125  18.29025
2019-03-01 00:00:00       WA     17.60075  18.37325
2019-03-01 00:15:00       WA     17.60075  18.37325
2019-03-01 00:30:00       WA     17.60075  18.37325
2019-03-01 00:45:00       WA     17.60075  18.37325
2019-03-01 01:00:00       WA     17.89825  18.28825

Thank you for suggestion for interpolate solution:

df.index = pd.to_datetime(df['Date'].astype(str) + df['Hour'].astype(str), 
                          format='%Y-%m-%d%H')

df = (df.groupby('Location').resample('15Min')[['Temperature','Humidity']]
        .asfreq())

df = (df.groupby(['Location', pd.Grouper(freq='d', level=1)])
        .transform(lambda x: x.interpolate()))

print (df)
                              Temperature  Humidity
Location                                           
NY       2019-03-01 00:00:00      73.1310  74.93000
         2019-03-01 00:15:00      73.1195  74.48775
         2019-03-01 00:30:00      73.1080  74.04550
         2019-03-01 00:45:00      73.0965  73.60325
         2019-03-01 01:00:00      73.0850  73.16100
WA       2019-03-01 00:00:00      70.4030  73.49300
         2019-03-01 00:15:00      70.7005  73.40800
         2019-03-01 00:30:00      70.9980  73.32300
         2019-03-01 00:45:00      71.2955  73.23800
         2019-03-01 01:00:00      71.5930  73.15300

Upvotes: 2

Artyom Akselrod
Artyom Akselrod

Reputation: 966

First resample (documentation) your df:

df['Date'] = df['Date'] + ' ' + df['Hour'] + ':00:00'
df['Date'] = pd.to_datetime(df['Date'])
df.set_index('Date', inplace=True)
df = df.resample('15T').asfreq()

Next you need to use interpolate (documentation):

df['Temperature'] = df['Temperature'].interpolate()

(!) But be aware that you need to work with each location separately.

Upvotes: 0

Related Questions