Categories of columns that are always NULL for every value of a specific column in pyspark

user_6911480 0
Viewed 67

I have a very large table and my database is spark. suppose that it's like this:

+----------------------------------------------------------------------------+
| col1  | col2 | col3  | col4(event_type) | col5  | col6  |   ...     |col20 |
+----------------------------------------------------------------------------+
|  null | val1 | val2  |       'A'        | val3  | null  |   ...     | null |
|  val4 | null | val5  |       'B'        | val6  | null  |   ...     | null |
|  null | null | val7  |       'C'        | null  | val8  |   ...     | val9 |
|  null | val1 | vall8 |       'A'        | val2  | null  |   ...     | null |
|............................................................................|
+-----------------------------------------------------------------------------

in this table we have a lot of column with lots of NULL value. also this table has a column which is type. for every type value, some columns are null forever. for example in above table, for type='A' col1 and col5 and col20 are NULL.

I want to extract for every type all column names which is not null all the time. (for type 'A' for example I want to get col1, col5 and col20 name).

can anyone help me how to do it?

Update:

as @Mohammad said, I try this pyspark code:

from pyspark.sql import functions as F

df.groupBy("event_type").agg\
(F.first(F.concat_ws(",",*[(F.when(F.col(x).isNotNull(), F.lit(x)))\
                           for x in df.columns if x!='event_type'])).alias("non_null_columns")).show()

it seems to be correct but result does not show correctly. result is like this:

+--------------------+-----------------------------------------+
|     event_type     |all_not_null_columns_for_each_event      |
+--------------------+-----------------------------------------+
|    event1_name     |                     timestamp,created...|
|    event2_comple...|                     timestamp,created...|
|    event3_name     |                     timestamp,battery...|
|    event5_name     |                     timestamp,battery...|
|    event6_name     |                     timestamp,battery...|
|    event7_comple...|                     timestamp,created...|
+--------------------+-----------------------------------------+

as you can see, results not showed completely and instead, we see ...

pyspark sql
2 Answers

You can achieve the looping through columns using Pyspark DataFrame API, not possible with pure SQL. 

df.show() #sampledata
#+----+----+----+----------+
#|col1|col2|col3|event_type|
#+----+----+----+----------+
#|null|val1|val2|         A|
#|val4|null|val5|         B|
#|null|null|val7|         C|
#|null|val1|val8|         A|
#+----+----+----+----------+


from pyspark.sql import functions as F

df.groupBy("event_type").agg\
(F.first(F.concat_ws(",",*[(F.when(F.col(x).isNotNull(), F.lit(x)))\
                           for x in df.columns if x!='event_type'])).alias("non_null_columns")).show()


#+----------+----------------+
#|event_type|non_null_columns|
#+----------+----------------+
#|         B|       col1,col3|
#|         C|            col3|
#|         A|       col2,col3|
#+----------+----------------+
user_101073890

In SQL, you can do this as:

select event_type,
       concat_ws(',',
                 (case when count(col1) > 0 then 'col1' end),
                 (case when count(col2) > 0 then 'col2' end),
                 (case when count(col3) > 0 then 'col3' end),
                 . . .
                ) as non_null_columns
from t
group by event_type;
user_11440350
Related