сопоставление строк pyspark - выбрать первое совпадение

#string #apache-spark #pyspark #apache-spark-sql #string-matching

#строка #apache-искра #пайспарк #apache-spark-sql #сопоставление строк

Вопрос:

У меня есть две таблицы.

comment_df комментарий

 | Date | Comment | 
|:---- |:------:| 
| 20/01/2020 | Transfer from Euro Account to HSBC account done on Monday but AMEX payment was on Tue. |
| 20/01/2020 | Brian initiated a Transfer from Euro Account to Natwest last Tuesday. |
| 21/01/2020 | AMEX payment to Natwest was delayed for second time in a row. |
| 21/01/2020 | AMEX receipts from Euro Account delayed. |

code_df код_df

 | Tag | Comment | 
|:---- |:------:| 
| EURO | Euro Account to HSBC |
| Natwest | Euro Account to Natwest |
| AMEX | AMEX payment |

Желаемый стол

 | Date | Comment | Tag |
|:---- |:------:| ----:|
| 20/01/2020 | Transfer from Euro Account to HSBC account done on Monday but AMEX payment was on Tue.| EURO |
| 20/01/2020 | Brian initiated a Transfer from Euro Account to Natwest last Tuesday. | Natwest |
| 21/01/2020 | AMEX payment to Natwest was delayed for second time in a row. | AMEX | 
| 21/01/2020 | AMEX receipts from Euro Account delayed. | |

Итак, в первом комментарии есть два тега (счет в евро для оплаты HSBC и AMEX), но я хочу, чтобы результат показывал первый встреченный тег, а не дублировал строки. Ниже приведено то, что было предложено ранее.

 code_df = code_df.withColumnRenamed('Comment', 'Commentcode')

result = comment_df.join(code_df, comment_df.Comment.contains(code_df.Commentcode), 'left').drop('Commentcode')

result.show(truncate=False)

 ---------- --------------------------------------------------------------------- ------- 
|Date      |Comment                                                              |Tag    |
 ---------- --------------------------------------------------------------------- ------- 
|20/01/2020|Transfer from Euro Account to HSBC account done on Monday but AMEX payment was on Tue. |EURO|
|20/01/2020|Brian initiated a Transfer from Euro Account to Natwest last Tuesday.|Natwest|
|21/01/2020|AMEX payment to Natwest was delayed for second time in a row.        |AMEX|
|21/01/2020|AMEX receipts from Euro Account delayed.                             |null|
 ---------- --------------------------------------------------------------------- -------

Ответ №1:

Вы можете упорядочить результаты на основе позиции совпадающей строки и получить только первое совпадение, отфильтровав позицию.

 from pyspark.sql import functions as F, Window

result = comment_df.join(
    code_df,
    comment_df.Comment.contains(code_df.Commentcode),
    'left'
).withColumn(
    'rn',
    F.row_number().over(
        Window.partitionBy('Date', 'Comment')
              .orderBy(F.expr('instr(Comment, Commentcode)'))
    )
).filter('rn = 1')