0

I imported a worksheet from a google sheets which happens to have a timestamp in string format in the ['Timestamp'] column. To filter the date by comparison and select some rows, I've created a variable which takes today's date (diaHoy) and another which is from the day before (diaAyer)

Then I'm trying to apply a mask which compares diaHoy and diaAyer with each timestamp element, but I can't because diaHoy and diaAyer are datetime elements and each timestamp cell is a string. I've tried applying strptime to ['Timestamp'] column but i can't because it's a list

Sample data:

df = pd.DataFrame ({'16/10/2019 14:56:36':['A','B'],'21/10/2019 14:56:36':['C','D'],'21/10/2019 14:56:36':['E','F']

diaHoy = 2019/10/21

diaAyer = 2019/10/20


import pandas as pd

diaHoy = datetime.today().date()
diaAyer = diaHoy + timedelta(days = -1)


wks1 = gc.open_by_url("CODE_URL").sheet1
df1 = wks1.get_all_values()
df1.pop(0)
mask1 = (df1 > diaAyer) & (df1 <= diaHoy)
pegado1 = df1.loc[mask1]

I expect that the mask filters out rows by the dates in the first column, by comparing them with diaHoy and diaAyer

Filter: between 21/10/2019 and 20/10/2019

Expected result:

df = pd.DataFrame ({'21/10/2019 14:56:36':['C','D'],'21/10/2019 14:56:36':['E','F']
Gonzalo
  • 116
  • 9
  • it's customary to add sample data and desired output, that way is easier/faster to help. try applying `pd.to_datetime` to the string series – Yuca Oct 21 '19 at 19:37
  • Please have a look at [How to make good pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) and provide both sample input and output, if you're using a pandas dataframe – G. Anderson Oct 21 '19 at 19:46

1 Answers1

0

you can convert the tuple of timestamp strings to a list of datetime objects:

import pandas as pd 
df2 = pd.DataFrame({pd.to_datetime(key):df[key] for key in df})
Sadrach Pierre
  • 121
  • 4
  • The dataframe df1 is 3 columns wide. Isn't to_datetime being applied to all columns if I don't specify it? Can to_datetime be targeted to a single column just to compare it with current day? – Gonzalo Oct 21 '19 at 20:04
  • This should work, if I understand correctly. You can convert the keys of the old dataframe into datetime objects and define a new dataframe: df2 = pd.DataFrame({pd.to_datetime(key):df[key] for key in df}) – Sadrach Pierre Oct 21 '19 at 20:17