PySpark:如何指定以逗号作为十进制的列 [英] PySpark: How to specify column with comma as decimal

问题描述

我正在使用PySpark并加载csv文件.我有一列带有欧洲格式数字的列，这意味着逗号替换了点，反之亦然.

I am working with PySpark and loading a csv file. I have a column with numbers in European format, which means that comma replaces the dot and vice versa.

例如:我有2.416,67而不是2,416.67.

My data in .csv file looks like this -    
ID;    Revenue
21;    2.645,45
23;   31.147,05
.
.
55;    1.009,11

在熊猫中，可以通过在pd.read_csv()中指定decimal=','和thousands='.'选项以读取欧洲格式来轻松读取此类文件.

In pandas, such a file can easily be read by specifying decimal=',' and thousands='.' options inside pd.read_csv() to read European formats.

熊猫代码:

import pandas as pd
df=pd.read_csv("filepath/revenues.csv",sep=';',decimal=',',thousands='.')

我不知道如何在PySpark中做到这一点.

I don't know how can this be done in PySpark.

PySpark代码:

from pyspark.sql.types import StructType, StructField, FloatType, StringType
schema = StructType([
            StructField("ID", StringType(), True),
            StructField("Revenue", FloatType(), True)
                    ])
df=spark.read.csv("filepath/revenues.csv",sep=';',encoding='UTF-8', schema=schema, header=True)

任何人都可以建议我们如何使用上述.csv()函数将此类文件加载到PySpark中吗?

Can anyone suggest as to how we can load such a file in PySpark using the above mentioned .csv() function?

推荐答案

由于数据格式的原因，您将无法以浮点形式读取它.您需要将其读取为字符串，将其清理，然后转换为浮点数:

You won't be able to read it as a float because the format of the data. You need to read it as a string, clean it up and then cast to float:

from pyspark.sql.functions import regexp_replace
from pyspark.sql.types import FloatType

df = spark.read.option("headers", "true").option("inferSchema", "true").csv("my_csv.csv", sep=";")
df = df.withColumn('revenue', regexp_replace('revenue', '\\.', ''))
df = df.withColumn('revenue', regexp_replace('revenue', ',', '.'))
df = df.withColumn('revenue', df['revenue'].cast("float"))

您可能也可以将所有这些链接在一起:

You can probably just chain these all together too:

df = spark.read.option("headers", "true").option("inferSchema", "true").csv("my_csv.csv", sep=";")
df = (
         df
         .withColumn('revenue', regexp_replace('revenue', '\\.', ''))
         .withColumn('revenue', regexp_replace('revenue', ',', '.'))
         .withColumn('revenue', df['revenue'].cast("float"))
     )

请注意，我尚未对此进行测试，因此其中可能有一两个错字.

Please note this I haven't tested this so there may be a typo or two in there.

这篇关于PySpark:如何指定以逗号作为十进制的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！