CODEWITHSUNDEEP
python-3.xpandasdatetimedataframetimestamp
Anagha
Anagha

Reputation: 3679

Select the data from between two timestamp in python

My query is regrading getting the data, given two timestamp in python.

I need to have a input field, where i can enter the two timestamp, then from the CSV read, i need to retrieve for that particular input.

Actaul Data(CSV)

      Daily_KWH_System  PowerScout  Temperature         Timestamp  Visibility    Daily_electric_cost   kW_System 
 0         4136.900384  P371602077           0  07/09/2016 23:58          0    180.657705            162.224216 
 1         3061.657187  P371602077          66  08/09/2016 23:59         10    133.693074            174.193804 
 2         4099.614033  P371602077          63  09/09/2016 05:58         10    179.029562            162.774013  
3          3922.490275  P371602077          63  10/09/2016 11:58         10    171.297701            169.230047  
4          3957.128982  P371602077          88  11/09/2016 17:58         10    172.806125            164.099307

Example:

Input:
start date : 2-1-2017
end date :10-1-2017

Output

Timestamp     Value
2-1-2017      10
3-1-2017      35
.
.
.
.
10-1-2017     25

The original CSV would contain all the data 

Timestamp        Value
1-12-2016        10
2-12-2016        25
.
.
.
1-1-2017         15
2-1-2017         10
.
.
.
10-1-2017        25
.
.
31-1-2017        50

Upvotes: 1

Views: 5516

Answers (2)

jezrael
jezrael

Reputation: 862661

It seems you need dayfirst=True in read_csv with select by [] if all start and end dates are in df.index:

import pandas as pd
from pandas.compat import StringIO

temp=u"""Timestamp;Value
1-12-2016;10
2-12-2016;25
1-1-2017;15
2-1-2017;10
10-1-2017;25
31-1-2017;50"""
#after testing replace 'StringIO(temp)' to 'filename.csv'
#if necessary add sep
#index_col=[0] convert first column to index
#parse_dates=[0] parse first column to datetime
df = pd.read_csv(StringIO(temp), sep=";", index_col=[0], parse_dates=[0], dayfirst=True)
print (df)
            Value
Timestamp        
2016-12-01     10
2016-12-02     25
2017-01-01     15
2017-01-02     10
2017-01-10     25
2017-01-31     50

print (df.index.dtype)
datetime64[ns]

print (df.index)
DatetimeIndex(['2016-12-01', '2016-12-02', '2017-01-01', '2017-01-02',
               '2017-01-10', '2017-01-31'],
              dtype='datetime64[ns]', name='Timestamp', freq=None)

start_date = pd.to_datetime('2-1-2017', dayfirst=True)
end_date  = pd.to_datetime('10-1-2017', dayfirst=True)
print (df[start_date:end_date])
            Value
Timestamp        
2017-01-02     10
2017-01-10     25

If some dates are not in index you need boolean indexing:

start_date = pd.to_datetime('3-1-2017', dayfirst=True)
end_date  = pd.to_datetime('10-1-2017', dayfirst=True)

print (df[(df.index > start_date) & (df.index > end_date)])
            Value
Timestamp        
2017-01-31     50

Upvotes: 1

piRSquared
piRSquared

Reputation: 294258

use pd.read_csv to read the file

df = pd.read_csv('my.csv', index_col='Timestamp', parse_dates=[0])

Then use your inputs to slice

df[start_date:end_date]

Upvotes: 2

Related Questions