如何将Excel数据读取到Spark/Scala中的数据框中

我有一个要求，其中我需要在Spark/Scala中读取Excel文件（带有.XLSX扩展名）。我需要使用从Excel读取的数据创建一个数据框，并在其上应用/编写SQL查询以进行一些分析。Excel文件具有一些列标题/标题，例如" time_spend_company（年）"，" 平均_ monthly_hours（小时）"我的问题要在加载的数据框架上应用任何SQL查询。

我正在使用 com.crealytics.spark.excel.excel 库来解析Excel内容，而我的代码看起来像下面的

val empFile = "C:\EmpDatasets.xlsx"
val employeesDF = sc.sqlContext.read
  .format("com.crealytics.spark.excel")
  .option("sheetName", "Sheet1")
  .option("useHeader", "true")
  .option("treatEmptyValuesAsNulls", "false")
  .option("inferSchema", "false")
  .option("location", empFile)
  .option("addColorColumns", "False")
  .load()
employeesDF.createOrReplaceTempView("EMP")

我想通过和这些列上的其他聚合功能应用一些组，并且我面临这些列的问题，如下所示，我的要求是在time_spent_company列中申请组并获取计数它。

val expLevel = sc.sqlContext.sql("Select 'time_spend_company (Years)' as 'Years_spent_in_company',count(1) from EMP where left_company = 1 group by 'time_spend_company (Years)'")
expLevel.show

我需要帮助： -

是否有更好的方法来加载Excel并为其分配自定义列名并创建DataFrame？
如何编写其中包含空格的这些列名称的SQL查询？

注意：我需要仅将其读为Excel文件，我不能转换为CSV或任何其他文件格式。

对于版本0.13.5，您需要一组不同的参数：

def readExcel(file: String): DataFrame = {
    sqlContext.read
      .format("com.crealytics.spark.excel")
      .option("dataAddress", "'sheet_name'!A1") // Optional, default: "A1"
      .option("header", "true") // Required
      .option("treatEmptyValuesAsNulls", "false") // Optional, default: true
      .option("inferSchema", "true") // Optional, default: false
      .option("addColorColumns", "false") // Optional, default: false
      .option("timestampFormat", "MM-dd-yyyy HH:mm:ss") // Optional, default: yyyy-mm-dd hh:mm:ss[.fffffffff]
      .option("maxRowsInMemory", 20) // Optional, d[#All]efault None. If set, uses a streaming reader which can help with big files
      .load(file)
  }

maven依赖性：

<dependency>
  <groupId>com.crealytics</groupId>
  <artifactId>spark-excel_2.11</artifactId>
  <version>0.13.5</version>
</dependency>

答案问题2：尽管使用'，但您需要在使用空格的列名的开始和结尾之前使用`。尝试以下查询将起作用：

val expLevel = sc.sqlContext.sql("Select `time_spend_company (Years)` as `Years_spent_in_company`,count(1) from EMP where left_company = 1 group by `time_spend_company (Years)`")

问题1：使用" com.crealytics.spark.excel"加载Excel。我也在使用它。也可能有不同的选择。为了分配不同的列名称，您可以使用struct类型来定义模式并在将数据加载到数据框中时强加于该模式。例如

val newSchema = StructType(
    List(StructField("a", IntegerType, nullable = true),
         StructField("b", IntegerType, nullable = true),
         StructField("c", IntegerType, nullable = true),
         StructField("d", IntegerType, nullable = true))
  )
val employeesDF = spark.read.schema(newSchema)
  .format("com.crealytics.spark.excel")
  .option("sheetName", "Sheet1")
  .option("useHeader", "true")
  .option("treatEmptyValuesAsNulls", "false")
  .option("inferSchema", "false")
  .option("location", empFile)
  .option("addColorColumns", "False")
  .load()

现在将通过A，B，C和D访问前四列名称。在查询下运行它将在新的列名上使用。

sc.sqlContext.sql("select a,b,c,d from EMP").show()

Spark可以很好地支持与CSV合作。因此，如果您的Excel文件只有一个表，则可以通过将EmpDatasets.xlsx重命名为EmpDatasets.csv，将其转换为CSV。用这个做到这一点。

将文件作为CSV作为spark.read.csv(pathToCSV)，可以提供许多选项，例如以下选项：读取/跳过数据集的架构或供应模式为spark.read.schema(schema).csv(pathToCSV)。

这里可以如下所述创建schema，也可以使用SPARK SQL编码器Encoders.product[case_class_name].schema

从案例类中提取

您可以从列名中删除空格：

val employeesDFColumns = employeesDF.columns.map(x => col(x.replaceAll(" ", "")))

并将这些新列名在数据框架上应用。

val employeeDF = employeeDF.select(employeesDFColumns:_*)

我们可以使用PANDAS库将PySpark数据保存到Excel文件，该数据提供了以Excel格式编写数据的功能。

from pyspark.sql import SparkSession
import pandas as pd
# Create a Spark session
spark = SparkSession.builder.appName("PySpark to Excel").getOrCreate()
# Create a PySpark dataframe
df = spark.createDataFrame([(1, "John Doe", 30), (2, "Jane Doe", 35), (3, "Jim Brown", 40)], 
                           ["id", "name", "age"])
# Convert the PySpark dataframe to a pandas dataframe
pandas_df = df.toPandas()
# Write the pandas dataframe to an Excel file
pandas_df.to_excel("output.xlsx", index=False)
# Stop the Spark session
spark.stop()

相关内容

最新更新

热门标签：