我的 csv 文件包含两列
- 身份证
- cbgs(用“”括起来的字典键值对)
示例 Csv 数据看起来像在记事本中 单元格 B2 包含 json key 对作为字符串。
id,cbgs sg:bd1f26e681264baaa4b44083891c886a,"{""060372623011"":166,""060372655203"":70,""060377019021"":34}"sg:04c7f777f01c4c75bbd9"81603"7{01633,"7 012“”:7}”
现在尝试如下转换
id,cbgs,value
sg:bd1f26e681264baaa4b44083891c886a,060372623011,166
sg:bd1f26e681264baaa4b44083891c886a,060372655203,70
sg:bd1f26e681264baaa4b44083891c886a,060377019021,34
sg:04c7f777f01c4c75bbd9e43180ce811f,060372073012,7
我尝试过的
1.尝试1
from pyspark.sql.functions import udf, explode
import json
fifa_df = spark.read.csv("D:\\1. Work\\Safegraph\\Sample Files\\Los Angels\\csv.csv", inferSchema = True, header = True)
fifa_df.printSchema()
df2.select("item",explode(parse("cbgs")).alias("recom_item","recom_cnt")).show()
Error msg:
cannot resolve '
item' given input columns: [id, cbgs, recom_item, recom_cnt];;
根据 DrChess 的建议，我尝试了下面的代码，但输出为空列表。
fifa_df.withColumn("cbgs", F.from_json("cbgs", T.MapType(T.StringType(), T.IntegerType()))).select("id", F.explode(["visitor_home_cbgs"]).alias('cbgs', 'value')).show()
+------------------+----+-----+
|safegraph_place_id|cbgs|value|
+------------------+----+-----+
+------------------+----+-----+
请您参考如下方法:
您需要先将 json 解析为 Map<String, Integer>然后炸开 map 。你可以这样做:
import pyspark.sql.types as T
import pyspark.sql.functions as F
...
df2.withColumn("cbgs", F.from_json("cbgs", T.MapType(T.StringType(), T.IntegerType()))).select("id", F.explode("cbgs").alias('cbgs', 'value')).show()
