how to slice dates of data queried through database?

Question

How can I slice the data using std input function ?

My data is as follows:

                 time                duration
0 2018-07-04 12:19:29+00:00         4000000000
1 2018-07-04 12:20:30+00:00         4000000000
2 2018-07-04 12:21:31+00:00         3700000000
3 2018-07-04 12:22:31+00:00         4100000000
4 2018-07-04 12:23:31+00:00         4100000000

I queried data from influxdb and then converted to a Dataframe. It gives me a error SyntaxError: invalid token when giving input to Starting date :

I tried to split the time column as separate date and Time

import influxdb
import pandas as pd
from datetime import datetime
import matplotlib.pyplot as plt

client = influxdb.DataFrameClient('192.168.1.108' , port = 8086, database = 'Mlogi_01081081')

dfs_dict = client.query('select * from sy_1')

measurement = next(iter(dfs_dict))

ret = dfs_dict[measurement]

df = pd.DataFrame.from_dict(ret)

df = df.reset_index()

df.rename( columns={'index':'time'}, inplace=True )

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

df['Time'] = df['time'].dt.time

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

df = df.set_index(['date'])

a = input('Starting_Date: ')
b = input('Ending_Date :')

starting_date = datetime.strptime(a, "%Y-%m-%d").date()
ending_date = datetime.strptime(b, "%Y-%m-%d").date()

df.loc[starting_date:ending_date]

Still no luck !!!

Answer 1

This should be your go, from your question, I'm assuming that you want to get all the data between two times using date as your designated column to do the filter. As requested by the OP, since he is using Python 2.7, the typed input should be quoted " " like this for user's input to work.

>>> input("please type the date: ") 
please type the date: 2016-08-09
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "<string>", line 1
    2016-08-09
          ^
SyntaxError: invalid token
>>> input("please type the date: ") 
please type the date: "2018-07-09" 
'2018-07-09'

Now to the real problem of filtering the data, here's what you can do:

newdf = df[(df["date"]>starting_date) and df["date"]<=ending_date] 

This should give you your expected result. But you have to make sure that df["date"] has the same datatype as of starting_date and ending_date.