Reliable way to verify Pyspark data frame column type

Question

If I read data from a CSV, all the columns will be of "String" type by default. Generally, I inspect the data using the following functions which gives an overview of the data and its types

df.dtypes
df.show()
df.printSchema()
df.distinct().count()
df.describe().show()

But, if there is a column that I believe is of a particular type e.g. Double, I cannot be sure if all the values are double if I don't have business knowledge and because

1. I cannot see all the values (millions of unique values)
2. If I explicitly cast it to double type, spark quietly converts the type without throwing any exception and the values which are not double are converted to "null" - for example

Code:

from pyspark.sql.types import DoubleType

changedTypedf = df_original.withColumn('label', df_control_trip['id'].cast(DoubleType()))

What could be the best way to confirm the type of column then?

Answer 1

If you don't have business knowledge, there is no way you can tell the correct type, and no way you can 'confirm' it. You can at most make assumptions about your dataset and your dataset only, and you for sure have to inspect every value.

In your example, you created a new column label that is a conversion of column id to double. You could count all rows that are null in label but not null in id. If this count is zero you can assume that for this dataset you can work with id as a double. That doesn't necessarily mean that in a new dataset the same will be true for column id.

Answer 2

Optimus can help you with this. https://github.com/ironmussa/optimus

After initializing Optimus you can run:

op.profiler.run(df, "*", infer=True)

This will give you the count for String, Integer, Float, Bool and Date. For more info check https://github.com/ironmussa/Optimus/blob/master/examples/new-api-profiler.ipynb

Answer 3

I had exactly the same issue, no inputs for the types of the column to cast.

My solution is to take the first row and convert it in dict your_dataframe.first().asDict(), then iterate with a regex to find if a value of a particular column is numeric or not. If a value is set to None with an empty string, filter the column and take the first row.

empty_columns = list()
first_row = your_dataframe.first().asDict()
dict_first_row_was_None = dict()   

for (column, value) in first_row.items():
        if value == "":
        empty_columns.append(column)

for column in empty_columns:
    result = your_dataframe.select(column).filter(col(column) != "").first()
        if result is not None:
            dict_first_row_was_None.update(result.asDict())

first_row.update(dict_first_row_was_None)

numeric_parameters = [column for (column, value) in first_row.items() if (re.match(r'YOUR_REGEX', value))]