Home / Questions / 124876
Accepted
Jorge Ponti
Asked: 2020-12-15 11:38:38 +0800 CST 2020-12-15 11:38:38 +0800 CST

Error when trying to compare dates of two time series: Cannot compare type 'Timestamp' with type 'str'"

  • 772

I am performing a data transformation and I need to run a whilebetween date periods, but I have the error:

"TypeError: Cannot compare type 'Timestamp' with type 'str'"

I have seen type()what type of data they are and both are class 'pandas.tslib.Timestamp'therefore I do not understand what happens.

I am attaching my full code for you to see and a CSV file for you to run and test:

from collections import defaultdict
import pandas as pd
from datetime import datetime, timedelta
from time import time

fecha_i=[]
fecha_f=[]
contador=[]
id=[]
contador={}
contador = defaultdict(list)

df = pd.read_csv('contour-export-2017-12-14.csv', header=0, sep=',',parse_dates = ['FCH_HORA_INICIO'],dayfirst = True, usecols=[0,3,6,7])

fecha_i=df['FCH_HORA_INICIO']
fecha_f=df['FCH_HORA_TERMINO']
id=df['ID']

acumulado=fecha_i[0]
i=1
k=0

print(type(acumulado))
print(type(fecha_i[0]))

while(acumulado<datetime.now()):
    acumulado=fecha_i[0]+timedelta(days=i)
    k=0
    while k<=len(df)-1:
        if acumulado>=fecha_i[k] and acumulado<=fecha_f[k]:
            contador[acumulado].append(str(ID[k]))
            k=k+1
        else:
            k=k+1
    i=i+1

File: https://drive.google.com/file/d/18sKmsg9MSs_t1JWKRYArxsVP4OPud4jL/view?usp=sharing

python

2 Answers

  1. Best Answer

    When converting the data to time through parse_dates, it is converted to Timestamp, this type of data is not used to make comparisons, what you should do is convert it to datetime through the to_datetime() function:

    fecha_i= pd.to_datetime(df['FCH_HORA_INICIO'], errors='coerce')
fecha_f= pd.to_datetime(df['FCH_HORA_TERMINO'], errors='coerce')

    Code:

    from collections import defaultdict
import pandas as pd
from datetime import datetime, timedelta
from time import time

fecha_i=[]
fecha_f=[]
contador=[]
id=[]
contador={}
contador = defaultdict(list)

df = pd.read_csv('contour-export-2017-12-14.csv', 
    header=0, sep=',',
    parse_dates = ['FCH_HORA_INICIO'],
    dayfirst = True, usecols=[0,3,6,7])

fecha_i= pd.to_datetime(df['FCH_HORA_INICIO'], errors='coerce')
fecha_f= pd.to_datetime(df['FCH_HORA_TERMINO'], errors='coerce')

ID=df['ID']

acumulado= fecha_i[0]
i=1
k=0

while acumulado < datetime.now():
    acumulado=fecha_i[0]+timedelta(days=i)
    k=0
    while k<= len(df)-1:
        if acumulado>=fecha_i[k] and acumulado<=fecha_f[k]:
            contador[acumulado].append(str(ID[k]))
            k=k+1
        else:
            k=k+1
    i=i+1
    • 3

  2. The problem is that you are not parsing the column 'FCH_HORA_TERMINO'because you do not indicate it in the argument parse_dates, so the data type that the column contains FCH_HORA_TERMINOis Python strings, while it FCH_HORA_INICIOis a column of type datetime64(NumPy).

    Just tell a to parse_datesalso take this column into account as a date and parse it to datetime64[ns]:

    df = pd.read_csv('contour-export-2017-12-14.csv', header=0, sep=',', usecols=[0,3,6,7], 
                 parse_dates = ['FCH_HORA_INICIO', 'FCH_HORA_TERMINO'], dayfirst = True)

    Both columns are parsed taking into account that the day comes before the month in both columns.

    • 1