Pandas: time converter works in isolation but not when reading csv file

user_5490188 0
Viewed 51

I want to read a csv file and convert the Excel serial time value to a YYYY-MM-DD string. The file looks like this:

A    B    start_date
0    yes  42736
1    no   42751
2    yes  43039

The function I want to use as a converter is:

def convert_excel_time(excel_time):  
    return (pd.to_datetime('1900-01-01') + pd.to_timedelta(excel_time,unit='day')).strftime("%Y-%m-%d")

And I am using it like this:

data = pd.read_csv("data.csv",encoding = "ISO-8859-1",
                  converters={'start_date': convert_excel_time})

When using the function as a stand-alone, I get the correct result:

excel_time = 42736
In[1]: (pd.to_datetime('1900-01-01')+pd.to_timedelta(excel_time,unit='day')).strftime("%Y-%m-%d")
Out[1]: '2017-01-03'

However, if I use the function as a converter whilst reading the csv file, all my start dates are '1900-01-01'. What am I doing wrong?

pandas converters time python
1 Answers

By default, python tends to read files as strings. It generally does this unless you tell it to do otherwise. I'm not particularly familiar with how pandas does things, but that seems to be what's happening here: when pd.read_csv() reads your file, it interprets the start_date column as strings.

The documentation for pd.to_timedelta() says that it should read strings just fine. But when you actually test the method, it seems to not work in this particular case.

>>> pd.to_timedelta(44, "day")
Timedelta('44 days 00:00:00')
>>> pd.to_timedelta("44", "day")
Timedelta('0 days 00:00:00.000000')

I suspect that when it lists str as a valid argument, it's referring to a string representation of a TimeDelta, rather than an int-as-a-str. And for whatever reason, instead of raising an error, it just returns a zero-value when given that.

Consequently, you'll just need to make sure that you pass it an int instead of a str. The simplest way to do this is just to cast excel_time at the start of the function convert_excel_time():

def convert_excel_time(excel_time):  
    excel_time = int(excel_time)
    return (pd.to_datetime('1900-01-01') + pd.to_timedelta(excel_time,unit='day')).strftime("%Y-%m-%d")

When actually reading in the data, pandas seems to provide a mechanism to avoid this problem: the dtype kwarg for pd.read_csv() allows you to specify the expected type of each column (with str presumably being the default). The problem is that if you give a converters kwarg, it gets applied instead, so you have to handle the conversion yourself.

user_26488110
Related