0

I am using to analyse a .csv files which only contains two columns:

  • date
  • time_taken

A snipped of the csv is:

date,time_taken
01-02-2019,2.3
02-02-2019,3.3
03-02-2019,2.8
04-02-2019,4.5
05-02-2019,1.2
06-02-2019,6.7

I am getting this ValueError:

ValueError: time data 'date' does not match format '%d-%m-%Y' (match)

The Python 3.6 snipped is:

import pandas as pd

transfer = pd.read_csv('tcs1_time.csv', header=None, delimiter=',')

transfer.columns = ['date', 'time_taken']

transfer['date'] = pd.to_datetime(transfer['date'], format='%d-%m-%Y')

The date in csv matches the format, however, I am getting this ValueError.

Can anyone help in this regard? thanks.

Dilshad Abduwali
  • 1,388
  • 7
  • 26
  • 47
  • if you are reading in a csv file that has headers, you shouldn't use the "header=None" option in pd.read_csv. – Chilled Geek Mar 06 '19 at 14:50
  • I think I did that in the above code. – Dilshad Abduwali Mar 06 '19 at 14:51
  • I'm saying that you should take out the "header=None" bit. What you are doing following your code is you are reading the first row in your file (date, time_taken) as a row in the dataframe but not as a header...this will throw an error because the string "date" will definitely not match your datetime format! – Chilled Geek Mar 06 '19 at 14:59
  • @Chilled Geek, sorry, I misunderstood you, actually it worked, that was the problem. I removed the header=None section. – Dilshad Abduwali Mar 06 '19 at 15:14
  • No problem! Glad to help. Enjoy python/pandas! – Chilled Geek Mar 06 '19 at 15:30

1 Answers1

1

Simple fix with 

transfer['date1'] = pd.to_datetime(transfer['date'], format='%d-%m-%Y',errors = 'coerce')

Then Using below to check what other format you have in the columns 

transfer.loc[transfer.date1.isnull(),'date']
BENY
  • 317,841
  • 20
  • 164
  • 234
  • can you elaborate the issue in my code? is this Pandas' issue/bug or am I doing something wrong, I am very new to Pandas. Thanks – Dilshad Abduwali Mar 06 '19 at 14:49
  • @DilshadAbduwali you have some value is in date columns does not have the format='%d-%m-%Y' – BENY Mar 06 '19 at 14:56
  • @DilshadAbduwali also in your csv do you have header ?, if so read it by using `pd.read_csv('tcs1_time.csv', delimiter=',')` – BENY Mar 06 '19 at 15:04