Data Profiling - How to count nulls, NaNs and empty string values?

user_6382488 0
Viewed 414

I am new to pyspark and I have this example dataset:

   Ticker_Modelo Ticker  Type   Period  Product  Geography  Source  Unit  Test
0  Model1_Index  Model1  Index  NWE     Forties  Hydrocraking  Daily  Refinery Margins  NWE  Bloomberg  None  3
1  Model2_Index  Model2  Index  NWE     Bonny Light Hydrocraking  Daily  Refinery Margins  NWE  Bloomberg  None  5
2  Model3_Index  Model3  Index  USGC    LLS FCC  Daily  Refinery Margins  USGC  Bloomberg  None  12
3  Model4_Index  Model4  Index  USGC    Maya Coking  Daily  Refinery Margins  USGC  Bloomberg  None  67
4  Model6_Index  Model6  Index  USMC    WTI FCC  Daily  Refinery Margins  USMC  Bloomberg  None  45
5  Model5_Index  Model5  Index  USMC    WCSS Coking  Daily  Refinery Margins  USMC  Bloomberg  None  22
6  Model7_Index  Model7  Index  USEC    Hibernia FCC  Daily  Refinery Margins  USEC  Bloomberg  None  
7  Model8_Index  Model8  Index  Singapore Dubai Hydrocracking  Daily  Refinery Margins  Singapore  Bloomberg  None  Null

I need do a data profiling and store it in a database.

I've tried with Optimus (https://github.com/ironmussa/Optimus/) and panda_profiler (https://pandas-profiling.github.io/pandas-profiling/docs/) but they do the profiling and gives you an HTML and there are some values I need that it doesn't calculate.

I need to count how many nulls/NaNs/empty strings are in each column and create a new table with it.

I'm using pandas and pyspark.

I've found an answer that I thought could help, Python / Pyspark - Count NULL, empty and NaN, but when I try to apply it to one column to try

data_df.filter((data_df["Ticker_Modelo"] == "") | data_df["Ticker_Modelo"].isNull() | isnan(data_df["Ticker_Modelo"])).count()

it gives me an error: AttributeError: 'Series' object has no attribute 'isNull'

And then I'm not sure how to apply it to all the columns and transpose it to get something like this:

               Count_nulls
Ticker_Modelo  0
Ticker         0
Type           0
Period         0
Product        0
Geography      0
Source         0
Unit           0
Test           2
pyspark pandas python-3.x
1 Answers

you could do the following :

first change all Null/None values into Panda NaN's

df.replace(['None','Null'],np.nan)

df.isnull().sum(axis=0).to_frame().rename(columns={0 : 'Count_Nulls'})
user_93751020
Related