Imported csv, column text converted to number but not summing

[Ecniv]

Hi

Obviously did this wrong.

Situation:
CSV text file importing into pandas
Problem, the source has numbers with ., rather than ,. so its been imported as text.
Applied a replace (for . to nothing) and a second replace (comma to dot) so now looks like a number.
Used the .astype to try to push to float.

df.update(df[c].str.replace('.','').str.replace(',','.').astype(float))

variables
- c is from a list (at the moment only one column but may have more in the future)
- df is the dataframe
- bfc is the variable of the class

Coding is in a class (trying something out)

bfc.dfDemands.groupby(by='source').sum()

only returns the source column and a column set to False.

The column that is meant to be a number is not returning.

Any pointers as to what I can look at to solve this or a different route to import... welcomed

[michalmonday]

Hi, could you upload the file?

[Ecniv]

nope. It has business data in it.

import pandas as pd

df = pd.DataFrame([['a','1,00'],['b','1.000,20'],['c','123,45']],columns=['source','rev'])

r=['rev']
for c in r:
    df.update(df[c].str.replace('.','').str.replace(',','.').astype(float))
df.groupby(by='source').sum()

Was going to use this, but it works fine on this data...

---

I have a lot more columns, but its strange that it only affects this one file with the string number reversed comma/dot.

[DeaD_EyE]

You should use pd.read_csv for this task. This function has many parameters which define the behavior of parsing data and they know the problem with comma or dot as decimal sign. Also the thousands sep can and should be specified. You should read this document: https://pandas.pydata.org/pandas-docs/st...d_csv.html

from textwrap import dedent
from io import StringIO


import pandas as pd


data = """
    source;rev
    a;1,00
    b;1.000,20
    c;123,45
    """

csv_data = StringIO(dedent(data.strip()))
# csv_data is a file-like object
# just for testing

df = pd.read_csv(csv_data, decimal=',', thousands='.', sep=';')
print(df['rev'])

Output:
0       1.00
1    1000.20
2     123.45
Name: rev, dtype: float64

[Ecniv]

Hi thanks

Nice suggestion, I did indeed end up using it. As it was in a process which received different files it had to change for that one file. Works fine :)

Thank you