1
$\begingroup$

I have a very dirty csv where there are several columns with only null values.

I would like to remove them. I am trying to select all columns where the count of null values in the column is not equal to the number of rows.

clean_df = bucketed_df.select([c for c in bucketed_df.columns if count(when(isnull(c), c)) not bucketed_df.count()])

However, I get this error:

SyntaxError: invalid syntax
  File "<command-2213215314329625>", line 1
    clean_df = bucketed_df.select([c for c in bucketed_df.columns if count(when(isnull(c), c)) not bucketed_df.count()])
                                                                                                             ^
SyntaxError: invalid syntax

If anyone could help me get rid of these dirty columns, that would be great.

$\endgroup$
1
$\begingroup$

[Updated]: Just realized it is about pyspark!

It is still simple! A concrete example (idea heavily borrowed from this answer):

Creating a dummy dataset

import pandas as pd
import numpy as np 
import pyspark.sql.functions as sqlf

main= pd.DataFrame(np.random.randint(0,100,size=(100, 4)), columns=list('ABCD'))

main["E"]= np.NAN
main["F"]= np.NAN

df = sqlContext.createDataFrame(main)

Function to drop Null columns

def drop_null_columns(df):
  
    """
    This function drops columns containing all null values.
    :param df: A PySpark DataFrame
    """
    
    null_counts = df.select([sqlf.count(sqlf.when(sqlf.col(c).isNull(), c)).alias(c) for c in df.columns]).collect()[0].asDict()
    to_drop = [k for k, v in null_counts.items() if v >= df.count()]
    df = df.drop(*to_drop)
    
    return df

Outcome

df_dropped = drop_null_columns(df)
$\endgroup$
0
$\begingroup$

Hello I think these lines could help: my case does not precisely answer to the original question. If we need to keep only the rows having at least one inspected column not null then use this:

from pyspark.sql import functions as F
from operator import or_
from functools import reduce

inspected = df.columns
df = df.where(reduce(or_, (F.col(c).isNotNull() for c in inspected ), F.lit(False)))
$\endgroup$
0
$\begingroup$

The accepted answer will work, but will run df.count() for each column, which is quite taxing for a large number of columns. Calculate it once before the list comprehension and save yourself an enormous amount of time:

def drop_null_columns(df):
  
    """
    This function drops columns containing all null values.
    :param df: A PySpark DataFrame
    """
    _df_length = df.count()
    null_counts = df.select([sqlf.count(sqlf.when(sqlf.col(c).isNull(), c)).alias(c) for c in df.columns]).collect()[0].asDict()
    to_drop = [k for k, v in null_counts.items() if v >= _df_length]
    df = df.drop(*to_drop)
    
    return df
New contributor
A F is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.