Btukfyl

I have a column called event_data in json format in my spark DataFrame, after reading it using from_json, I get this schema:

root

 |-- user_id: string (nullable = true)

 |-- event_data: struct (nullable = true)

 |    |-- af_content_id: string (nullable = true)

 |    |-- af_currency: string (nullable = true)

 |    |-- af_order_id: long (nullable = true)

I only need af_content_id from this column. This attribute can be of different formats:

from pyspark.sql.functions import explode



def get_content_id(column):

    return column.af_content_id



df_transf_1 = df_transf_1.withColumn(

    "products_basket", 

    get_content_id(df_transf_1.event_data)

)



df_transf_1 = df_transf_1.withColumn(

    "product_id",

    explode(df_transf_1.products_basket)

)

import pandas as pd



arr = [

    ['b5ad805c-f295-4852-82fc-961a88',12732936],

    ['0FD6955D-484C-4FC8-8C3F-DA7D28',['Gklb38','123655']],

    ['0E3D17EA-BEEF-4931-8104','12909841'],

    ['CC2877D0-A15C-4C0A-AD65-762A35C1',[12645715, 12909837, 12909837]]

]



df = pd.DataFrame(arr, columns = ['user_id','products_basket'])



df = df[['user_id','products_basket']].astype(str)

df_transf_1 = spark.createDataFrame(df)

If you are starting with a DataFrame like:

df_transf_1.show(truncate=False)

#+--------------------------------+------------------------------+

#|user_id                         |products_basket               |

#+--------------------------------+------------------------------+

#|b5ad805c-f295-4852-82fc-961a88  |12732936                      |

#|0FD6955D-484C-4FC8-8C3F-DA7D28  |['Gklb38', '123655']          |

#|0E3D17EA-BEEF-4931-8104         |12909841                      |

#|CC2877D0-A15C-4C0A-AD65-762A35C1|[12645715, 12909837, 12909837]|

#+--------------------------------+------------------------------+

where the products_basket column is a StringType:

df.printSchema()

#root

# |-- user_id: string (nullable = true)

# |-- products_basket: string (nullable = true)

You can't call explode on products_basket because it's not an array or map.

One workaround is to remove any leading/trailing square brackets and then split the string on ", " (comma followed by a space). This will convert the string into an array of strings.

from pyspark.sql.functions import col, regexp_replace, split

df_transf_new= df_transf_1.withColumn(

    "products_basket",

    split(regexp_replace(col("products_basket"), r"(^[)|(]$)|(')", ""), ", ")

)



df_transf_new.show(truncate=False)

#+--------------------------------+------------------------------+

#|user_id                         |products_basket               |

#+--------------------------------+------------------------------+

#|b5ad805c-f295-4852-82fc-961a88  |[12732936]                    |

#|0FD6955D-484C-4FC8-8C3F-DA7D28  |[Gklb38, 123655]              |

#|0E3D17EA-BEEF-4931-8104         |[12909841]                    |

#|CC2877D0-A15C-4C0A-AD65-762A35C1|[12645715, 12909837, 12909837]|

#+--------------------------------+------------------------------+

The regular expression pattern matches any of the following:

• 
  (^[): An opening square bracket at the start of the string


• 
  (]$): A closing square bracket at the end of the string


• 
  ('): Any single quote (because your strings are quoted)

and replaces these with an empty string.

This assumes that your data does not contain any needed single quotes or square brackets inside the product_basket.

After the split, the schema of the new DataFrame is:

df_transf_new.printSchema()

#root

# |-- user_id: string (nullable = true)

# |-- products_basket: array (nullable = true)

# |    |-- element: string (containsNull = true)

Now you can call explode:

from pyspark.sql.functions import explode

df_transf_new.withColumn("product_id", explode("products_basket")).show(truncate=False)

#+--------------------------------+------------------------------+----------+

#|user_id                         |products_basket               |product_id|

#+--------------------------------+------------------------------+----------+

#|b5ad805c-f295-4852-82fc-961a88  |[12732936]                    |12732936  |

#|0FD6955D-484C-4FC8-8C3F-DA7D28  |[Gklb38, 123655]              |Gklb38    |

#|0FD6955D-484C-4FC8-8C3F-DA7D28  |[Gklb38, 123655]              |123655    |

#|0E3D17EA-BEEF-4931-8104         |[12909841]                    |12909841  |

#|CC2877D0-A15C-4C0A-AD65-762A35C1|[12645715, 12909837, 12909837]|12645715  |

#|CC2877D0-A15C-4C0A-AD65-762A35C1|[12645715, 12909837, 12909837]|12909837  |

#|CC2877D0-A15C-4C0A-AD65-762A35C1|[12645715, 12909837, 12909837]|12909837  |

#+--------------------------------+------------------------------+----------+