Search for keywords in DataFrame

user_439621 0
Viewed 320

I've a Spark Dataframe and a list of 'keywords'.
For 4 columns I need to check if the value is in the list and fill the new column 'result' with a specific result (not necessarily the column name).
Then I need to search all the remaining columns and when there's a match, the result is 'other'

Sample DataFrame:

df = spark.createDataFrame([
  ["apple", "Null","Null","alcatel","Aalst","123","01-01-2016","blu"],
  ["apple", "apple","Lorem ipsum dolor sit amet","Null","Excepteur sint occaecat","543","07-12-2010","cat"],
  ["asus","apple","nisi ut aliquid ex ea commodi consequatur?","","Null","578","06-04-2020","htc"],
  ["samsung","fugiat quo voluptas nulla pariatur","apple","Null","Antwerp","285","04-08-2018","asus"],
  ["sony","magni dolores","Null","asus","quis nostrud exercitation","386","06-06-2009","huawei"],
  ["vivo","laborum","Null","Veriatis","adipisci ","389","23-12-2005","oppo"],
  ["alcatel","laboriosam","Contains Apple","Null","Asus","104","02-03-2018","zte"],
  ["sharp","null","null","apple","Asus","333","07-09-2017","alcatel"]
]).toDF("a-val","b-val","c-val","d-val","e-val","f-val","g-val","h-val")



keywords = ['apple', 'asus', 'alcatel']
df.withColum('result', when(col('a-val').isin(keywords), concat(lit('a'), col(result))))
df.withColum('result', when(col('b-val').isin(keywords), concat(lit('b'), col(result))))
df.withColum('result', when(col('c-val').isin(keywords), concat(lit('c'), col(result))))
df.withColum('result', when(col('d-val').isin(keywords), concat(lit('d'), col(result))))

Possible results;

    result
    -------
    a
    b
    c
    d
    a;b
    b;d
    a;c;d
    a;other
    c;d;other
    ...

Not sure if the concat is the desired way to go or maybe better create a list first and add that.

Search by column succeeds but merging the results and search through the remaining columns I can't get it done.

I really appreciate any help!

pyspark apache-spark
1 Answers

IIUC this can be done as

  1. You can create a dictionary for the column value mapping
evalCol={i:i[0] if i.startswith(('a','b','c','d')) else 'other' for i in df.columns}

{'a-val': 'a',
 'b-val': 'b',
 'c-val': 'c',
 'd-val': 'd',
 'e-val': 'other',
 'f-val': 'other',
 'g-val': 'other',
 'h-val': 'other'}
  1. Then use this to filter the values and then concat the columns as
keywords = ['apple', 'asus', 'alcatel']

df.withColumn('result',f.concat_ws(';',*[f.when(f.col(k).isin(keywords),v).otherwise(None) for k,v in evalCol.items()])).show(10,False)

+-------+----------------------------------+------------------------------------------+--------+-------------------------+-----+----------+-------+-------+
|a-val  |b-val                             |c-val                                     |d-val   |e-val                    |f-val|g-val     |h-val  |result |
+-------+----------------------------------+------------------------------------------+--------+-------------------------+-----+----------+-------+-------+
|apple  |Null                              |Null                                      |alcatel |Aalst                    |123  |01-01-2016|blu    |a;d    |
|apple  |apple                             |Lorem ipsum dolor sit amet                |Null    |Excepteur sint occaecat  |543  |07-12-2010|cat    |a;b    |
|asus   |apple                             |nisi ut aliquid ex ea commodi consequatur?|        |Null                     |578  |06-04-2020|htc    |a;b    |
|samsung|fugiat quo voluptas nulla pariatur|apple                                     |Null    |Antwerp                  |285  |04-08-2018|asus   |c;other|
|sony   |magni dolores                     |Null                                      |asus    |quis nostrud exercitation|386  |06-06-2009|huawei |d      |
|vivo   |laborum                           |Null                                      |Veriatis|adipisci                 |389  |23-12-2005|oppo   |       |
|alcatel|laboriosam                        |Contains Apple                            |Null    |Asus                     |104  |02-03-2018|zte    |a      |
|sharp  |null                              |null                                      |apple   |Asus                     |333  |07-09-2017|alcatel|d;other|
+-------+----------------------------------+------------------------------------------+--------+-------------------------+-----+----------+-------+-------+

Hope it helps.

user_53527480
Related