How to select column for a specific time range from pandas dataframe in python3?

Question

This is my pandas dataframe

                     time    energy
0     2018-01-01 00:15:00    0.0000
1     2018-01-01 00:30:00    0.0000
2     2018-01-01 00:45:00    0.0000
3     2018-01-01 01:00:00    0.0000
4     2018-01-01 01:15:00    0.0000
5     2018-01-01 01:30:00    0.0000
6     2018-01-01 01:45:00    0.0000
7     2018-01-01 02:00:00    0.0000
8     2018-01-01 02:15:00    0.0000
9     2018-01-01 02:30:00    0.0000
10    2018-01-01 02:45:00    0.0000
11    2018-01-01 03:00:00    0.0000
12    2018-01-01 03:15:00    0.0000
13    2018-01-01 03:30:00    0.0000
14    2018-01-01 03:45:00    0.0000
15    2018-01-01 04:00:00    0.0000
16    2018-01-01 04:15:00    0.0000
17    2018-01-01 04:30:00    0.0000
18    2018-01-01 04:45:00    0.0000
19    2018-01-01 05:00:00    0.0000
20    2018-01-01 05:15:00    0.0000
21    2018-01-01 05:30:00    0.9392
22    2018-01-01 05:45:00    2.8788
23    2018-01-01 06:00:00    5.5768
24    2018-01-01 06:15:00    8.6660
25    2018-01-01 06:30:00   15.8648
26    2018-01-01 06:45:00   24.1760
27    2018-01-01 07:00:00   38.5324
28    2018-01-01 07:15:00   49.9292
29    2018-01-01 07:30:00   64.3788

I would like to select the values from energy column using a specific Time range 01:15:00 - 05:30:00 and sum those values. To select datas from column I need both hour and minute values. I know how to select data from column using hour and minute separately..

import panadas as pd
from datetime import datetime as dt
energy_data = pd.read_csv("/home/mayukh/Downloads/Northam_january2018/output1.csv", index_col=None)
#Using Hour 
sum = energy_data[((energy_data.time.dt.hour < 1) & (energy_data.time.dt.hour >= 5))]['energy'].sum()
#using Minute
sum = energy_data[((energy_data.time.dt.minute < 15) & (energy_data.time.dt.minute >= 30))]['energy'].sum()

but I don't know how to use both hour and minute together to select data. Please tell me the way how can I will proceed.

jezrael · Accepted Answer · 2018-03-08 11:25:05Z

2

Use between_time working with Datetimeindex created by set_index:

#if necessary convert to datetime
df['time'] = pd.to_datetime(df['time'])
a = df.set_index('time').between_time('01:15:00','05:30:00')['energy'].sum()
print (a)
0.9392

Detail:

print (df.set_index('time').between_time('01:15:00','05:30:00'))
                     energy
time                       
2018-01-01 01:15:00  0.0000
2018-01-01 01:30:00  0.0000
2018-01-01 01:45:00  0.0000
2018-01-01 02:00:00  0.0000
2018-01-01 02:15:00  0.0000
2018-01-01 02:30:00  0.0000
2018-01-01 02:45:00  0.0000
2018-01-01 03:00:00  0.0000
2018-01-01 03:15:00  0.0000
2018-01-01 03:30:00  0.0000
2018-01-01 03:45:00  0.0000
2018-01-01 04:00:00  0.0000
2018-01-01 04:15:00  0.0000
2018-01-01 04:30:00  0.0000
2018-01-01 04:45:00  0.0000
2018-01-01 05:00:00  0.0000
2018-01-01 05:15:00  0.0000
2018-01-01 05:30:00  0.9392

answered Mar 8, 2018 at 11:25

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

7 Comments

Prayuktibid Over a year ago

Thanks for your response. I have tried using your example before but after compilation I got this error massage

SettingWithCopyWarning:  A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead  See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy   energy_data['time'] = pd.to_datetime(energy_data['time'])

jezrael Over a year ago

What is your code before energy_data['time'] = pd.to_datetime(energy_data['time']) ?

jezrael Over a year ago

@Prayuktibid - i think need copy if filtering like here

Prayuktibid Over a year ago

report_data['energy (kwh)'] = (report_data['actual_generated_energy (kwh)']) off_peak_energy1 = report_data[report_data.time.dt.weekday >= 5]['energy (kwh)'].sum() energy = report_data[report_data.time.dt.weekday < 5]

jezrael Over a year ago

so change last row by add copy - energy = report_data[report_data.time.dt.weekday < 5].copy()

|

jpp · Accepted Answer · 2018-03-08 12:13:02Z

1

You can convert your column to datetime and use .loc accessor with pd.Series.between:

from datetime import datetime

df['time'] = pd.to_datetime(df['time'])

start = datetime.strptime('01:15:00', '%H:%M:%S').time()
end = datetime.strptime('05:30:00', '%H:%M:%S').time()

result = df.loc[df['A'].dt.time.between(start, end), 'energy'].sum()

edited Mar 8, 2018 at 12:13

answered Mar 8, 2018 at 11:31

jpp

166k37 gold badges301 silver badges363 bronze badges

2 Comments

Prayuktibid Over a year ago

Thanks for response. I can not use date because date is variable for my case the ate could be anything. Actually I want to sum up all the values between 01:15:00 - 05:30:00 for pone month so, in a month the date could be anyrthing but the time range will be fixed for each day.

jpp Over a year ago

@Prayuktibid, see update. This will work independent of date.

Collectives™ on Stack Overflow

How to select column for a specific time range from pandas dataframe in python3?

2 Answers 2

7 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related