1
$\begingroup$

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?

$\endgroup$
  • 1
    $\begingroup$ I would prefer to use Counter([type(a) for a in df_control_trip['id']]) which will return something like Counter({float: 4, int: 1087, str: 1035}). When I use pandas.csv_read() if the values are actually numbers, they will be read as float or int instead of str. $\endgroup$ – user12075 Sep 21 '18 at 5:48
2
$\begingroup$

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.

| improve this answer | |
$\endgroup$
1
$\begingroup$

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

| improve this answer | |
$\endgroup$
0
$\begingroup$

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))]
| improve this answer | |
$\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.