时间戳

首页 > 解决方案 > 熊猫数据框货币到数字

python - 熊猫数据框货币到数字

问题描述

我知道还有其他类似的主题，但我已经尝试了所有这些并没有成功。我正在阅读其中一列（“总”）充满货币（如 2,553.00 美元）的数据框

这就是我在数据框中读取的方式：

columns=['Client','year','month','traffic','Source','Leads','Sold','Gross']             
return pd.read_csv(filename,thousands=',',decimal=".", index_col='Client',parse_dates=True,encoding = "ISO-8859-1",usecols=columns)

我现在尝试了几种将货币列转换为浮点数的方法，以便对其进行计算，例如以下内容：

data = data.apply(lambda x: x.str.replace(',','.'))
data['Gross'] = data['Gross'].fillna(0.0).str.replace(r'[$,-]', '').astype('float')

这给了我以下错误：

Traceback (most recent call last):
  File "C:\Users\Laila\Desktop\tool5.py", line 136, in <module>
    data=getData()
  File "C:\Users\Laila\Desktop\tool5.py", line 123, in getData
    data = data.apply(lambda x: x.str.replace(',','.'))
  File "C:\Users\Laila\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pandas\core\frame.py", line 4877, in apply
    ignore_failures=ignore_failures)
  File "C:\Users\Laila\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pandas\core\frame.py", line 4973, in _apply_standard
    results[i] = func(v)
  File "C:\Users\Laila\Desktop\tool5.py", line 123, in <lambda>
    data = data.apply(lambda x: x.str.replace(',','.'))
  File "C:\Users\Laila\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pandas\core\generic.py", line 3610, in __getattr__
    return object.__getattribute__(self, name)
  File "C:\Users\Laila\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pandas\core\accessor.py", line 54, in __get__
    return self.construct_accessor(instance)
  File "C:\Users\Laila\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pandas\core\strings.py", line 1910, in _make_accessor
    raise AttributeError("Can only use .str accessor with string "
AttributeError: ('Can only use .str accessor with string values, which use np.object_ dtype in pandas', 'occurred at index year')
[Finished in 7.4s with exit code 1]
[shell_cmd: python -u "C:\Users\Laila\Desktop\crmtool5.py"]
[dir: C:\Users\Laila\Desktop]
[path: C:\Program Files (x86)\Common Files\Oracle\Java\javapath;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0\;C:\WINDOWS\System32\OpenSSH\;C:\Users\Laila\AppData\Local\Programs\Python\Python36-32\Scripts\;C:\Users\Laila\AppData\Local\Programs\Python\Python36-32\;C:\Users\Laila\AppData\Local\Microsoft\WindowsApps;]

或者：

data[['Gross']] = data[['Gross']].replace('[\$]','',regex=True).replace("-", "",regex=True).replace(',','.', regex=True)
data[['Gross']] = data[['Gross']].fillna(0.0).astype('float')

给我一个类似的错误：

Traceback (most recent call last):
  File "C:\Users\Laila\Desktop\crmtool5.py", line 136, in <module>
    data=getData()
  File "C:\Users\Laila\Desktop\crmtool5.py", line 121, in getData
    data[['Gross']] = data[['Gross']].fillna(0.0).astype('float')
  File "C:\Users\Laila\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pandas\util\_decorators.py", line 118, in wrapper
    return func(*args, **kwargs)
  File "C:\Users\Laila\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pandas\core\generic.py", line 4004, in astype
    **kwargs)
  File "C:\Users\Laila\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pandas\core\internals.py", line 3462, in astype
    return self.apply('astype', dtype=dtype, **kwargs)
  File "C:\Users\Laila\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pandas\core\internals.py", line 3329, in apply
    applied = getattr(b, f)(**kwargs)
  File "C:\Users\Laila\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pandas\core\internals.py", line 544, in astype
    **kwargs)
  File "C:\Users\Laila\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pandas\core\internals.py", line 625, in _astype
    values = astype_nansafe(values.ravel(), dtype, copy=True)
  File "C:\Users\Laila\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pandas\core\dtypes\cast.py", line 703, in astype_nansafe
    return arr.astype(dtype)
ValueError: could not convert string to float: 
[Finished in 7.9s with exit code 1]
[shell_cmd: python -u "C:\Users\Laila\Desktop\crmtool5.py"]
[dir: C:\Users\Laila\Desktop]
[path: C:\Program Files (x86)\Common Files\Oracle\Java\javapath;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0\;C:\WINDOWS\System32\OpenSSH\;C:\Users\Laila\AppData\Local\Programs\Python\Python36-32\Scripts\;C:\Users\Laila\AppData\Local\Programs\Python\Python36-32\;C:\Users\Laila\AppData\Local\Microsoft\WindowsApps;]

原始数据示例：

Client  year    month   traffic Source  Leads   Sold    Gross
dd      2018    March   6,778    x      0       1       -$27
dd      2018    March   6,778    yy     1       0       $0
dd      2018    March   6,778    z      2       1       $1,354

关于如何解决这个问题的任何建议？

标签： pythonpandasdataframe

解决方案

您正在尝试替换,为，.但生成的字符串无法转换为浮点数。例如，2.553.00 包含两个点，当将其转换为浮点数时，将引发异常。

将代码更改为：

data['Gross'] = data['Gross'].fillna(0.0).str.replace('[$,]', '').astype('float')

推荐阅读