PySpark - DataFrame的基本操作(笔记：20221205更新版)

连接spark

ps：我使用的是单机版spark3.0版本

import socket
from pyspark import SparkContext, SparkConf
from pyspark.sql import SparkSession
from pyspark.sql import SQLContext
import pandas as pd

localIpAddress = socket.gethostbyname(socket.gethostname())

# 创建Spark配置
sparkConf = SparkConf()

# 初始化我们的Spark集群，这实际上会生成工作节点。
spark = SparkSession.builder.config(conf=sparkConf).getOrCreate()
sc = spark.sparkContext
sqlContext = SQLContext(sc)
spark

1、添加数据

创建spark的数据框有这么两种常规的新建数据方式：
①.createDataFrame ()
②.toDF()

1.1、createDataFrame(): 创建空dataframe

from pyspark.sql.types import *

schema = StructType([
        StructField("user_id", StringType(), True),
        StructField("name", StringType(), True),
        StructField("age", IntegerType(), True),
        StructField("score", FloatType(), True)
])
empty_dataframes = spark.createDataFrame(spark.sparkContext.emptyRDD(), schema)

1.2、createDataFrame() : 创建一个spark数据框

sdf = sqlContext.createDataFrame([("a1", "小明", 12, 56.5), ("a2", "小红", 15, 23.0),\
                                 ("a3", "小强", 23, 84.0), ("a3","小小",9,93.5)],\
                                 ("user_id", "name", "age", "score"))

1.3、toDF() : 创建一个spark数据框

from pyspark.sql import Row

row = Row("user_id","name","age","score")
row_user_id = ['a1','a2','a3','a4']
row_name = ['小明','小红','小强','小小']
row_age = [12,15,23,9]
row_score = [56.5,23.0,84.0,93.5]

sdf1 = sc.parallelize([row(row_user_id[i],row_name[i],row_age[i],row_score[i]) for i in range(len(row))]).toDF()

1.4、withColumn(): 新增数据列

withColumn是通过添加或替换与现有列有相同的名字的列，返回一个新的DataFrame

sdf2 = sdf1.withColumn('score_new',sdf1.score/2.0)

如果不想在原有列的基础上添加新的列，而是添加一列全新的，不同于原有数据框的列，可以考虑join()函数

import numpy as np
sdf3 = sqlContext.createDataFrame([("a1",3.0), ("a2",3.0), ("a3",np.nan)], ("user_id_class", "class"))

sdf4 = sdf2.join(sdf3,sdf2.user_id==sdf3.user_id_class,'left').drop('user_id_class')

2、修改数据

2.1、withColumn(): 修改原有数据框中某一列的值（统一修改）

sdf5 = sdf4.withColumn('score_new',sdf4.score_new/2)

2.2、cast() 和 astype(): 修改列的类型（类型投射）

sdf6 = sdf4.withColumn('score_new',sdf4.score_new.cast("Int"))

sdf6 = sdf4.withColumn('age',sdf4.age.astype("Float"))

2.3、withColumnRenamed(): 修改列名

sdf6 = sdf6.withColumnRenamed("score_new","new_score")

2.4、fillna(): 填充NA

sdf6.fillna(-1)

2.5、replace(): 全局替换

sdf7 = sdf6.replace("小小", "大大")

3、查询数据

3.1、行数据查询操作

3.1.1、show(): 可用int类型指定要打印的行数

sdf.show(5)

3.1.2、dtypes(): 查看dataframe中每一列的类型