Import csv file using pandas and split the resulting strings

Question

I want to read this csv file in pandas as a DataFrame. Then I would like to split the resulting strings from colons.

I import using:

df_r = pd.read_csv("report.csv", sep=";|,", engine="python")

Then split using:

for c in df_r:
    if df_r[c].dtype == "object":
        df_r[c] = df_r[c].str.split(':')

But I get the following error:

ValueError: could not convert string to float: '"\x001\x000\x00'

Any idea what I am doing wrong?

Edit:

The error actually shows when I try to convert one of the strings to a float

print(float(df_r["Laptime"].iloc[0][2]))

Andrea Bergonzo · Accepted Answer · 2017-07-05 18:04:45Z

1

I ran your code and everything works fine. You can try catching the error and print the row that has that strange behaviour and manual inspect that.

Is that the entire dump you are using? I saw that you are assigning the csv to the variable a and using df_r afterwards so I think you are doing something else in between.

If the csv file is complete be aware that the last line is empty and create a row full of NaNs. You want to read the csv with skipfooter=1.

a = pd.read_csv('report.csv', sep=";|,", engine="python", skipfooter=1)

Edit:
You can convert it to float like this:

print(float(df_r["Laptime"].iloc[0][2].replace(b'\x00',b'')))

edited Jul 5, 2017 at 18:04

answered Jul 5, 2017 at 14:38

Andrea Bergonzo

5,4304 gold badges26 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fariborz Ghavamian Over a year ago

I did not correctly ask my question at first. I made an edit. That error shows when I try to convert one of the string to a float. Would you please check that out too.

Andrea Bergonzo Over a year ago

@FariborzGhavamian Answer modified. Let me know if that works for you.

Fariborz Ghavamian Over a year ago

It does, thanks @Andrea! Just a tiny modification. I had to change .replace(b'\x00',b''))) to .replace('\x00',''))).

Collectives™ on Stack Overflow

Import csv file using pandas and split the resulting strings

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related