I have two tables.
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
| Tag | Comment |
|:---- |:------:|
| EURO | Euro Account to HSBC |
| Natwest | Euro Account to Natwest |
| AMEX | AMEX payment |
The desired table
| 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. | |
So the first comment has two tags (Euro Account to HSBC & AMEX payment) but I want the result to show the first tag it comes across and not duplicate the rows. Below is what was previously suggested.
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|
+----------+---------------------------------------------------------------------+-------+
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…