CODEWITHSUNDEEP
pythonpandas
Bastian Löffler
Bastian Löffler

Reputation: 51

Date and time in pandas read_csv

my data looks like this:

06.02.2013;13:00;0,215;0,215;0,185;0,205;0,00

I try to read it this way: s = pandas.read_csv(csv_file, sep=';', skiprows=3, index_col=[0],decimal=',',thousands='.',parse_dates={'Date': [0, 1]}, dayfirst=True)

(see http://www.nuclearphynance.com/Show%20Post.aspx?PostIDKey=164080 https://github.com/pydata/pandas/issues/2586)

This is what I get:

6022013.0 13:00       0.215  0.215  0.185    0.205        0

What am I doing wrong?

Upvotes: 1

Views: 5764

Answers (3)

Andy Hayden
Andy Hayden

Reputation: 375915

This was a bug fixed in pandas 0.13+ (thanks to this issue):

In [11]: pd.read_csv(StringIO(s), sep=';', header=None, parse_dates={'Dates': [0, 1]},
                     index_col=0, decimal=',', thousands=".")
Out[11]:
                            2      3      4      5  6
Dates
2013-06-02 13:00:00  1000.215  0.215  0.185  0.205  0

Upvotes: 2

Bastian Löffler
Bastian Löffler

Reputation: 51

Ok, when running your example file date-parsing works. However, my data looks like this:

Datum;Zeit;Er<F6>ffnung;Hoch;Tief;Schluss;Volumen
02.08.2013;14:00;8.428,58;8.431,67;8.376,28;8.406,94;73.393.682,00
01.08.2013;14:00;8.320,38;8.411,30;8.316,89;8.410,73;97.990.435,00

In that case, date does not get recognized:

s = pd.read_csv('test1.csv', decimal=',',sep=';', parse_dates=True, index_col=[0])
print s
....                                                            
02.08.2013  14:00  8.428,58  8.431,67  8.376,28  8.406,94   73.393.682,00
01.08.2013  14:00  8.320,38  8.411,30  8.316,89  8.410,73   97.990.435,00

For me the only difference between your file and mine is the missing spaces between the separators ;?

Upvotes: 0

nitin
nitin

Reputation: 7358

I am not sure if this is a bug. See below,

My data file looks like so,

date; time; col1; col2; col3; col4; col5
06.02.2013 ; 13:00 ; 0,215 ; 0,215 ; 0,185 ; 0,205 ; 0,00
06.02.2013 ; 13:00 ; 0,215 ; 0,215 ; 0,185 ; 0,205 ; 0,00

I implement the following code on it,

import pandas
s = pandas.read_csv('test.txt', decimal=',',sep=';', parse_dates=True, index_col=[0])
print s

To get,

               time   col1   col2   col3   col4   col5
date                                                  
2013-06-02   13:00   0.215  0.215  0.185  0.205      0
2013-06-02   13:00   0.215  0.215  0.185  0.205      0

Is this the result you want.

Please make sure that you are using the latest pandas version

'0.11.0'

To deal with the thousands operators... you could use

s = pandas.read_csv('test2.txt',sep=';',decimal=',', parse_dates=True, index_col=[0],converters={'col1':lambda x: float(x.replace('.','').replace(',','.'))})

Upvotes: 0

Related Questions