Conflict between thousand separator and date format - pandas.read_csv

user_8404122 0
Viewed 394

I have a problem with reading data from csv file with Pythons' read_csv method.

Row format: 

'06.02.2013;544,00;2,52;3,53'

With this implementation: 

 df = pd.read_csv(filepath, sep=";", header=5, decimal=",")
 df['value'] = df['value'].astype(int)

Python gives me an error: invalid literal for int() with base 10: '544,00', When I print this dataframe object I can see that some float values have been recognized and some haven't. 

                value   value1  value2
Datum                               
06.02.2013      544,00   2.52    3.53

What I did next was implement a method (even though I do not have thousands in my file): 

df = pd.read_csv(filepath, sep=";", header=5, decimal=",", thousands = ".")

Then I do not get that error, but resulting date is 06022013 instead of 06.02.2013.

To solve that problem, I have tried this:

df = pd.read_csv(filepath, sep=";", header=5, dayfirst=True, decimal=",", thousands = ".", parse_dates=[0])

In that case, date is formatted like this: January 2, 2013, midnight.

And after all of that I have tried to add a date_parser to this method like this: 

df = pd.read_csv(filepath, sep=";", header=5, dayfirst=True, decimal=",", thousands = ".", parse_dates=[0],date_parser=lambda x: datetime.strptime(x, '%d.%m.%Y') )

But it still formatted date like before: January 2, 2013, midnight. Has anyone else encountered such a problem or knows how to solve it?

EDIT: So, the real data looks like this (first row after header):

0
1
2
3
4
Datum       value1 value2 value3 value4 value5 value6 value7 value8 value9 value10 value11 value12 value13 value14
01.03.2020    str1  str2   str3   str4   str5   str6   9,82   9,75   0,75   500,00  544,00  44,00   50,00  49,25

In [1]: df['value11'] = df['value11'].astype(int)
Out [1]: invalid literal for int() with base 10: '544,00'

Also, error takes place already on first row. I have since come to realize that after changing first row, I get no error. Modified first row:

0
1
2
3
4
Datum      value1 value2 value3 value4 value5 value6 value7 value8 value9 value10 value11 value12 value13 value14
01.04.2020    str1  str2   str3   str4   str5   str6   36,03   5,46   84,85   23,00  64,00  41,00   59,92  -24,92

Pandas version: 1.0.2

EDIT2:

df = pd.read_csv(filepath, sep=";", header=5, decimal=",")
print(df.iloc[:,7:])

OUTPUT: **Headers not included because of information sensitivity**

EDIT3: I found out how to reproduce this problem. Example of csv file:

data.csv
0
1
2
3
4
Datum
Datum;value1;value2;value3;value4;value5;value6;value7;value8;value9;value10;value11;value12;value13;value14
01.03.2020;str1;str2;str3;str4;str5;str6;"9,82";"9,75";"0,75";"500,00";"544,00";"44,00";"50,00";"49,25"
01.03.2020;str1;str2;str3;str4;str5;str6;"9,72";"7,00";"27,97";"737,00";"1.123,00";"386,00";"51,03";"23,06"

Thanks in advance!

pandas dataframe csv python-3.x
2 Answers

Are you indicating you header row correctly?

Here's a sample CSV:

cat seven_rows.csv                                                                                                                                                                                  

0
1
2
3
4
Datum;value1;value2;value3;value4;value5;value6;value7;value8;value9;value10;value11;value12;value13;value14
01.03.2020;str1;str2;str3;str4;str5;str6;9,82;9,75;0,75;500,00;544,00;44,00;50,00;49,25

Your original import:

df = pd.read_csv('seven_rows.csv', sep=";", header=5, decimal=",")

        Datum value1 value2 value3 value4 value5 value6  value7  value8  value9  value10  value11  value12  value13  value14
0  01.03.2020   str1   str2   str3   str4   str5   str6    9.82    9.75    0.75    500.0      544.0     44.0     50.0    49.25

Casting value11 to int:

df['value11'] = df['value11'].astype(int)

        Datum value1 value2 value3 value4 value5 value6  value7  value8  value9  value10  value11  value12  value13  value14
0  01.03.2020   str1   str2   str3   str4   str5   str6    9.82    9.75    0.75    500.0      544     44.0     50.0    49.25
user_60309510

I have managed to solve the problem.

df = pd.read_csv(filepath, sep=";", header=5, decimal=",", thousands = ".", parse_dates=['Datum'], date_parser = lambda x: datetime.strptime(x, '%d.%m.%Y'))
df['Datum'] = df['Datum'].dt.strftime("%d.%m.%Y")

Problem was because thousands separator was ".", I somehow managed to format the date like I wanted afterwards and now everything works good.

Appreciate all help!

user_84041220
Related