I have 2 dataframes:
df_1, column id
contain only characters and numbers ==> normalized, and id_no_normalized
Example:
id_normalized | id_no_normalized
-------------|-------------------
ABC | A_B.C
-------------|-------------------
ERFD | E.R_FD
-------------|-------------------
12ZED | 12_Z.ED
df_2, column name
contain only characters and numbers ==> normalized are attached
Example:
name
----------------------------
googleisa12ZEDgoodnavigator
----------------------------
internetABCexplorer
----------------------------
I would like to look the id_normalized (dataset_1)
if exist in name (dataset_2)
. If I find it, I take the value of id_no_normalized
and I store it in a new column in dataset_2
Expect result:
name | result
----------------------------|----------
googleisa12ZEDgoodnavigator | 12_Z.ED
----------------------------|----------
internetABCexplorer | A_B.C
----------------------------|----------
I did it using this code:
df_result = df_2.withColumn("id_no_normalized", dft_2.name.contains(df_1.id_normalized))
return df_result.select("name", "id_normalized")
is not working because, it doesn't find the id_normalized
in the df_2.
Second solution, it work only when I limited the output on 300 rows almost, but when I return all the data, is took many time running and not finish:
df_1 = df_1.select("id_no_normalized").drop_duplicates()
df_1 = df_1.withColumn(
"id_normalized",
F.regexp_replace(F.col("id_no_normalized"), "[^a-zA-Z0-9]+", ""))
df_2 = df_2.select("name")
extract = F.expr('position(id_normalized IN name)>0')
result = df_1.join(df_2, extract)
return result
How can I correct my code to resolve it ?
Thank you
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…