Flattering the json file to a individual rows in pyspark

Question

I have recieve a json file as input from the api here is the sample json.

json_data = 
{
  "field1": "value1",
  "field2": "value2",
  "message_records": [
    {
      "field3": "value3",
      "field4": "value4"
    },
    {
      "field5": "value5",
      "field6": "value6"
    }
  ],
  "messages": [
    {
      "field7": "value3",
      "field8": "value4"
    },
    {
      "field9": "value5",
      "field10": "value6"
    },
    {
      "field11": "value5",
      "field12": "value6"
    }
  ]
}

how to flattern the json data into an individual rows using python and load the data into dataframe .here messages,message_records having nested arrays need to load into individual records. Convert the json file to pyspark dataframe

Here field1,field2 is common for message_records and messages i need to write the message_records data to a seperate file and messages data to a seperate file

score 1 · Answer 1 · answered May 24 '23 at 06:29

You use below code for creating in individual rows and write data into separate file of message_records and messages.

record = {}
record["field1"] = json_data["field1"]
record["field2"] = json_data["field2"]
message_records_df =spark.createDataFrame([record])
messages_df = spark.createDataFrame([record])

Creating both dataframe with field1 and field2. Since these both are same to message_records and messages.

from pyspark.sql.types import LongType
from pyspark.sql import Row

def zipindexdf(df):
    schema_new = df.schema.add("index", LongType(), False)
    return df.rdd.zipWithIndex().map(lambda l: list(l[0]) + [l[1]]).toDF(schema_new)
    
message_records_df_index = zipindexdf(message_records_df)
message_records_df_index.show()
messages_df_index = zipindexdf(messages_df)
messages_df_index.show()

Here I am adding index column using zipWithIndex so it can joined on index.

enter image description here

Next, creating dataframe and merging into final dataframe by looping through each item in message_records as below.

for i in json_data['message_records']:
    df = zipindexdf(spark.createDataFrame([i]))
    message_records_df_index = message_records_df_index.join(df, "index", "inner")
    message_records_df_index.show()

enter image description here

Same way I am doing for messages as below

for i in json_data['messages']:
    df = zipindexdf(spark.createDataFrame([i]))
    messages_df_index = messages_df_index.join(df, "index", "inner")
    messages_df_index.show()

enter image description here

Finally, writing this data into csv file.

message_records_df_index.write.option("header","true").csv('/message_records_df/')
messages_df_index.write.option("header","true").csv('/messages_df/')

spark.read.option("header","true").csv('/message_records_df/').show()
spark.read.option("header","true").csv('/messages_df/').show()

enter image description here

score 1 · Answer 2 · answered May 24 '23 at 07:13

You can find the answer in this SO answer.

You only have to change the way you call the paras. I used

df_flat1 = flatten_test(multiline_df.select(multiline_df.field1,multiline_df.field2,multiline_df.message_records))
df_flat2 = flatten_test(multiline_df.select(multiline_df.field1,multiline_df.field2, multiline_df.messages))
df_flat1.printSchema()
df_flat1.show(5)

df_flat2.printSchema()
df_flat2.show(5)

and got

root
 |-- field1: string (nullable = true)
 |-- field2: string (nullable = true)
 |-- message_records_field3: string (nullable = true)
 |-- message_records_field4: string (nullable = true)
 |-- message_records_field5: string (nullable = true)
 |-- message_records_field6: string (nullable = true)

+------+------+----------------------+----------------------+----------------------+----------------------+
|field1|field2|message_records_field3|message_records_field4|message_records_field5|message_records_field6|
+------+------+----------------------+----------------------+----------------------+----------------------+
|value1|value2|                value3|                value4|                  null|                  null|
|value1|value2|                  null|                  null|                value5|                value6|
+------+------+----------------------+----------------------+----------------------+----------------------+

root
 |-- field1: string (nullable = true)
 |-- field2: string (nullable = true)
 |-- messages_field10: string (nullable = true)
 |-- messages_field11: string (nullable = true)
 |-- messages_field12: string (nullable = true)
 |-- messages_field7: string (nullable = true)
 |-- messages_field8: string (nullable = true)
 |-- messages_field9: string (nullable = true)

+------+------+----------------+----------------+----------------+---------------+---------------+---------------+
|field1|field2|messages_field10|messages_field11|messages_field12|messages_field7|messages_field8|messages_field9|
+------+------+----------------+----------------+----------------+---------------+---------------+---------------+
|value1|value2|            null|            null|            null|         value3|         value4|           null|
|value1|value2|          value6|            null|            null|           null|           null|         value5|
|value1|value2|            null|          value5|          value6|           null|           null|           null|
+------+------+----------------+----------------+----------------+---------------+---------------+---------------+

Flattering the json file to a individual rows in pyspark

2 Answers2