title: count values in multiple columns that contain a substring based on strings of lists pyspark
body: <p>I have a data frame in <code>Pyspark</code> like below. I want to <code>count</code> values in two columns based on some <code>lists</code> and populate new columns for each <code>list</code></p>
  
  <pre><code>df.show()
  
  +---+-------------+-------------_+
  | id|       device|  device_model|
  +---+-------------+--------------+
  |  3|      mac pro|           mac|
  |  1|       iphone|       iphone5|
  |  1|android phone|       android|
  |  1|   windows pc|       windows|
  |  1|   spy camera|    spy camera|
  |  2|             |        camera|
  |  2|       iphone|  apple iphone|
  |  3|   spy camera|              |
  |  3|         cctv|          cctv|
  +---+-------------+--------------+
  </code></pre>
  
  <p><code>lists are below</code>:</p>
  
  <pre><code>phone_list = ['iphone', 'android', 'nokia']
  pc_list = ['windows', 'mac']
  security_list = ['camera', 'cctv']
  </code></pre>
  
  <p>I want to <code>count</code> the <code>device</code> and <code>device_model</code> for each <code>id</code> and <code>pivot</code> the values in a new data frame.</p>
  
  <p>I want to <code>count</code> the values in the both the <code>device_model</code> and <code>device</code> columns for each <code>id</code> that match the <code>strings</code> in the list. </p>
  
  <p>For example: in <code>phone_list</code> I have a <code>iphone</code> string this should count values for both values <code>iphone</code> and <code>iphone5</code></p>
  
  <p>The result I want </p>
  
  <pre><code>+---+------+----+--------+
  | id|phones|  pc|security|
  +---+------+----+--------+
  |  1|     4|   2|       2|
  |  2|     2|null|       1|
  |  3|  null|   2|       3|
  +---+------+----+--------+
  </code></pre>
  
  <p>I have done like below</p>
  
  <pre><code>df.withColumn('cat', 
      F.when(df.device.isin(phone_list), 'phones').otherwise(
      F.when(df.device.isin(pc_list), 'pc').otherwise(
      F.when(df.device.isin(security_list), 'security')))
  ).groupBy('id').pivot('cat').agg(F.count('cat')).show()
  </code></pre>
  
  <p>Using the above I can only do for <code>device</code> column and only if the <code>string</code> matches exactly. But unable to figure out how to do for both the columns and when value contains the <code>string</code>.</p>
  
  <p>How can I achieve the result I want? </p>