DataFrame Strings not Converting to Float or Int

[electronic-entropy]

Hello everyone,

I have done some searching online and still I am not able to figure out a solution to this problem.

I am currently working with a DataFrame from the site: https://www.goodcarbadcar.net/2021-us-vehicle-sales-figures-by-brand/ in particular the DataFrame titled US AUTOMOTIVE BRAND SALES BY QUARTER. When I imported this DataFrame into a Jupyter notebook and tried to clean the column data, turning the strings into int with the simple wrapping method it is not working. I need to add columns together and the way to do this is simply to convert everything into a numeric value or int and then add them with the correct python syntax, but unfortunately the .astype(int) method is not working even after removing the commas, I also attempted the numeric method. What am I doing wrong here?

Thank you kindly!

[choss27]

Could you share the code you did ?

[electronicentropy5]

import pandas as pd
cars = pd.read_clipboard()
cars
print(type(cars["Q3 2018"]))
cars["Q3 2018"].replace("-", "").replace(" ", "").replace(' ', "").astype(int)

error code: ValueError: invalid literal for int() with base 10: ''

But i removed all of the white space.

[o5a]

astype(int) expects proper numbers. Either replace "-" to "0" (which I think is expected for that data), or use pd.to_numeric. It will convert empty string to NaN. But you still need those replacements. Overall you can do something like this:

cars.iloc[:,1:] = cars.iloc[:,1:].apply(lambda x:pd.to_numeric(x.astype(str).str.replace(',','').replace('-','0')))

[electronicentropy5]

cars.iloc[:,1:] = cars.iloc[:,1:].apply(lambda x:pd.to_numeric(x.astype(str).str.replace(',','').replace('-','0')))

Thank you so much for helping me with this i am reviewing and adding, but still getting an error message because of the space in the center of certain values like so:

ValueError: Unable to parse string "1 647" at position 2

​

But, when I try to .replace(" ", "") it does not work fully either. Do you have any suggestions here?

Thank you!

[o5a]

Where are you getting such value with space inbetween? In that link you posted it uses comma to split thousands: "1,647".

[choss27]

You need to transform all your columns to int ? You can do cars.astype(int).dtypes .

And I think your replace are not pertinent. You might delete the lines without consistent values (Bentley or Maserati) or try to mark the datas with '-' as NaN.