Null count for every column

PySpark data engineering interview problem. Difficulty: beginner. Pattern: Data Quality. About 10 minutes. Free to practice.

Before you trust a table, you check how many values are missing in each column. A column with many NULLs may be a sign of a broken source. Writing one count for each column by hand does not scale to tables with hundreds of columns, so you build the list from the column names.

The table df has several columns, and some of them have NULL values. Return a 1-row table that has the same column names as df. In each column, put the number of NULLs in that column of df. Build the expressions from df.columns with a list comprehension, so the code still works if columns are added. The editor creates the table df and ends with result = df. Change the last line to build your answer.

Requirements

• Return exactly 1 row.
• Use the same column names as df.
• In each column, count the rows where that column is NULL.
• Build the expressions from df.columns, not by hand. Store the DataFrame in result.

Constraints

• The table has 4 rows and 3 columns: id, name and score.
• Count with F.count(F.when(F.col(c).isNull(), c)), because count ignores NULL and when without otherwise gives NULL for non-matching rows.
• One select with a list of expressions gives 1 row.

Examples

Input: df id | name | score 1 | a | 10 2 | NULL | NULL 3 | c | NULL NULL | NULL | 5 Output: id | name | score 1 | 2 | 2 id is NULL once, name twice and score twice.

Topics: lakebench, pyspark, nulls, profiling, columns.

More PySpark interview questions · All interview problems · Learn data engineering