Koalas: When using ks.readcsv, Koalas type inference differs from pandas due to the use of Spark's inferSchema logic

[Callum027]

Hi guys,

I'm using Koalas to combine two sets of similar data, and there is a step where we block rows using a direct comparison on a specific column. As part of this, we remove all NaN values from that column using DataFrame.dropna, but in our tests, we found that for float columns, Koalas would convert that row to an int if the values did not have decimal components. This is not exactly undesirable per se, but it IS different from the behaviour in pandas, so I thought I'd at least double check if this is intended behaviour.

Test data:

ID,name,birth_year,hourly_wage,address,zipcode
b1,Mark Levene,1987,29.5,"108 Clement St, San Francisco",94107
b2,Bill Bridge,1986,32,"3131 Webster St, San Francisco",
b3,Mike Franklin,1988,27.5,"1652 Stockton St, San Francisco",94122
b4,,1982,26,"108 South Park, San Francisco",
b5,Alfons Kemper,1984,35,"170 Post St, Apt 4,  San Francisco",94122
b6,Michael Brodie,1987,32.5,,94107

Test script:

import pandas as pd
from databricks import koalas as ks

csv_file = 'test.csv'

pandas_A = pd.read_csv(csv_file)
koalas_A = ks.read_csv(csv_file)

print('pandas_A:\n{}\n'.format(pandas_A.to_string()))
print('koalas_A:\n{}\n'.format(koalas_A.to_string()))

pandas_A_dropna = pandas_A.dropna(subset=['zipcode'])
koalas_A_dropna = koalas_A.dropna(subset=['zipcode'])

print('pandas_A_dropna:\n{}\n'.format(pandas_A_dropna.to_string()))
print('koalas_A_dropna:\n{}\n'.format(koalas_A_dropna.to_string()))

print('pandas_A_dropna[zipcode].dtype = {}'.format(pandas_A_dropna['zipcode'].dtype))
print('koalas_A_dropna[zipcode].dtype = {}'.format(koalas_A_dropna['zipcode'].dtype))

Output:

pandas_A:
   ID            name  birth_year  hourly_wage                             address  zipcode
0  b1     Mark Levene        1987         29.5       108 Clement St, San Francisco  94107.0
1  b2     Bill Bridge        1986         32.0      3131 Webster St, San Francisco      NaN
2  b3   Mike Franklin        1988         27.5     1652 Stockton St, San Francisco  94122.0
3  b4             NaN        1982         26.0       108 South Park, San Francisco      NaN
4  b5   Alfons Kemper        1984         35.0  170 Post St, Apt 4,  San Francisco  94122.0
5  b6  Michael Brodie        1987         32.5                                 NaN  94107.0

koalas_A:
   ID            name  birth_year  hourly_wage                             address  zipcode
0  b1     Mark Levene        1987         29.5       108 Clement St, San Francisco  94107.0
1  b2     Bill Bridge        1986         32.0      3131 Webster St, San Francisco      NaN
2  b3   Mike Franklin        1988         27.5     1652 Stockton St, San Francisco  94122.0
3  b4            None        1982         26.0       108 South Park, San Francisco      NaN
4  b5   Alfons Kemper        1984         35.0  170 Post St, Apt 4,  San Francisco  94122.0
5  b6  Michael Brodie        1987         32.5                                None  94107.0

pandas_A_dropna:
   ID            name  birth_year  hourly_wage                             address  zipcode
0  b1     Mark Levene        1987         29.5       108 Clement St, San Francisco  94107.0
2  b3   Mike Franklin        1988         27.5     1652 Stockton St, San Francisco  94122.0
4  b5   Alfons Kemper        1984         35.0  170 Post St, Apt 4,  San Francisco  94122.0
5  b6  Michael Brodie        1987         32.5                                 NaN  94107.0

koalas_A_dropna:
   ID            name  birth_year  hourly_wage                             address  zipcode
0  b1     Mark Levene        1987         29.5       108 Clement St, San Francisco    94107
2  b3   Mike Franklin        1988         27.5     1652 Stockton St, San Francisco    94122
4  b5   Alfons Kemper        1984         35.0  170 Post St, Apt 4,  San Francisco    94122
5  b6  Michael Brodie        1987         32.5                                None    94107

pandas_A_dropna[zipcode].dtype = float64
koalas_A_dropna[zipcode].dtype = int32

[beobest2]

I have printed types before implementing "dropna"

csv_file = 'test.csv'

pandas_A = pd.read_csv(csv_file)
koalas_A = ks.from_pandas(csv_file)

print('pandas_A:\n{}\n'.format(pandas_A.to_string()))
print('koalas_A:\n{}\n'.format(koalas_A.to_string()))

print('pandas_A[zipcode].dtype = {}'.format(pandas_A['zipcode'].dtype))
print('koalas_A[zipcode].dtype = {}'.format(koalas_A['zipcode'].dtype))

pandas_A_dropna = pandas_A.dropna(subset=['zipcode'])
koalas_A_dropna = koalas_A.dropna(subset=['zipcode'])

print('pandas_A_dropna:\n{}\n'.format(pandas_A_dropna.to_string()))
print('koalas_A_dropna:\n{}\n'.format(koalas_A_dropna.to_string()))

print('pandas_A_dropna[zipcode].dtype = {}'.format(pandas_A_dropna['zipcode'].dtype))
print('koalas_A_dropna[zipcode].dtype = {}'.format(koalas_A_dropna['zipcode'].dtype))

result :

pandas_A:
   ID            name  birth_year  hourly_wage                             address  zipcode
0  b1     Mark Levene        1987         29.5       108 Clement St, San Francisco  94107.0
1  b2     Bill Bridge        1986         32.0      3131 Webster St, San Francisco      NaN
2  b3   Mike Franklin        1988         27.5     1652 Stockton St, San Francisco  94122.0
3  b4             NaN        1982         26.0       108 South Park, San Francisco      NaN
4  b5   Alfons Kemper        1984         35.0  170 Post St, Apt 4,  San Francisco  94122.0
5  b6  Michael Brodie        1987         32.5                                 NaN  94107.0


koalas_A:
   ID            name  birth_year  hourly_wage                             address  zipcode
0  b1     Mark Levene        1987         29.5       108 Clement St, San Francisco  94107.0
1  b2     Bill Bridge        1986         32.0      3131 Webster St, San Francisco      NaN
2  b3   Mike Franklin        1988         27.5     1652 Stockton St, San Francisco  94122.0
3  b4            None        1982         26.0       108 South Park, San Francisco      NaN
4  b5   Alfons Kemper        1984         35.0  170 Post St, Apt 4,  San Francisco  94122.0
5  b6  Michael Brodie        1987         32.5                                None  94107.0

pandas_A[zipcode].dtype = float64
koalas_A[zipcode].dtype = int32
pandas_A_dropna:
   ID            name  birth_year  hourly_wage                             address  zipcode
0  b1     Mark Levene        1987         29.5       108 Clement St, San Francisco  94107.0
2  b3   Mike Franklin        1988         27.5     1652 Stockton St, San Francisco  94122.0
4  b5   Alfons Kemper        1984         35.0  170 Post St, Apt 4,  San Francisco  94122.0
5  b6  Michael Brodie        1987         32.5                                 NaN  94107.0


koalas_A_dropna:
   ID            name  birth_year  hourly_wage                             address  zipcode
0  b1     Mark Levene        1987         29.5       108 Clement St, San Francisco    94107
2  b3   Mike Franklin        1988         27.5     1652 Stockton St, San Francisco    94122
4  b5   Alfons Kemper        1984         35.0  170 Post St, Apt 4,  San Francisco    94122
5  b6  Michael Brodie        1987         32.5                                None    94107

pandas_A_dropna[zipcode].dtype = float64
koalas_A_dropna[zipcode].dtype = int32

As the result shows, Koalas was already recognized as "integer" before running "dropna" function.

I tested again using "from_pandas". (Convert the created pandas Dataframe to a Koalas Dataframe.)

csv_file = 'test.csv'

pandas_A = pd.read_csv(csv_file)
koalas_A = ks.from_pandas(pandas_A)

print('pandas_A:\n{}\n'.format(pandas_A.to_string()))
print('koalas_A:\n{}\n'.format(koalas_A.to_string()))

print('pandas_A[zipcode].dtype = {}'.format(pandas_A['zipcode'].dtype))
print('koalas_A[zipcode].dtype = {}'.format(koalas_A['zipcode'].dtype))

pandas_A_dropna = pandas_A.dropna(subset=['zipcode'])
koalas_A_dropna = koalas_A.dropna(subset=['zipcode'])

print('pandas_A_dropna:\n{}\n'.format(pandas_A_dropna.to_string()))
print('koalas_A_dropna:\n{}\n'.format(koalas_A_dropna.to_string()))

print('pandas_A_dropna[zipcode].dtype = {}'.format(pandas_A_dropna['zipcode'].dtype))
print('koalas_A_dropna[zipcode].dtype = {}'.format(koalas_A_dropna['zipcode'].dtype))

pandas_A:
   ID            name  birth_year  hourly_wage                             address  zipcode
0  b1     Mark Levene        1987         29.5       108 Clement St, San Francisco  94107.0
1  b2     Bill Bridge        1986         32.0      3131 Webster St, San Francisco      NaN
2  b3   Mike Franklin        1988         27.5     1652 Stockton St, San Francisco  94122.0
3  b4             NaN        1982         26.0       108 South Park, San Francisco      NaN
4  b5   Alfons Kemper        1984         35.0  170 Post St, Apt 4,  San Francisco  94122.0
5  b6  Michael Brodie        1987         32.5                                 NaN  94107.0

koalas_A:
   ID            name  birth_year  hourly_wage                             address  zipcode
0  b1     Mark Levene        1987         29.5       108 Clement St, San Francisco  94107.0
1  b2     Bill Bridge        1986         32.0      3131 Webster St, San Francisco      NaN
2  b3   Mike Franklin        1988         27.5     1652 Stockton St, San Francisco  94122.0
3  b4            None        1982         26.0       108 South Park, San Francisco      NaN
4  b5   Alfons Kemper        1984         35.0  170 Post St, Apt 4,  San Francisco  94122.0
5  b6  Michael Brodie        1987         32.5                                None  94107.0

pandas_A[zipcode].dtype = float64
koalas_A[zipcode].dtype = float64
pandas_A_dropna:
   ID            name  birth_year  hourly_wage                             address  zipcode
0  b1     Mark Levene        1987         29.5       108 Clement St, San Francisco  94107.0
2  b3   Mike Franklin        1988         27.5     1652 Stockton St, San Francisco  94122.0
4  b5   Alfons Kemper        1984         35.0  170 Post St, Apt 4,  San Francisco  94122.0
5  b6  Michael Brodie        1987         32.5                                 NaN  94107.0

koalas_A_dropna:
   ID            name  birth_year  hourly_wage                             address  zipcode
0  b1     Mark Levene        1987         29.5       108 Clement St, San Francisco  94107.0
2  b3   Mike Franklin        1988         27.5     1652 Stockton St, San Francisco  94122.0
4  b5   Alfons Kemper        1984         35.0  170 Post St, Apt 4,  San Francisco  94122.0
5  b6  Michael Brodie        1987         32.5                                None  94107.0

pandas_A_dropna[zipcode].dtype = float64
koalas_A_dropna[zipcode].dtype = float64

Even if "dropna" was performed in Koalas, it was confirmed to recognize "float" as the same.

Let me check that this is a problem that "read_csv" recognizes float as an int. Thank you.

[beobest2]

@Callum027

Koalas uses the Spark engine internally.
If Koalas reads csv and infers the data types, it follows spark's __inferSchema__ logic.

>>> sdf = spark.read.csv('test.csv', header=True, inferSchema=True)
>>> sdf
DataFrame[ID: string, name: string, birth_year: int, hourly_wage: double, address: string, zipcode: int]

In Pandas, if a missing value exists, the data type is always float. ( as in the __zipcode__ column in the example above)
If there is no missing value, (as in the __birth_year__ column) Pandas also decides to be an integer.
spark has no logic for missing values ​​when inferring data types.
So if you want the same behavior as Pandas, I recommend using __ks.from_pandas__.
Or you can change one piece of data like this.
94107 --> 94107.0

ID,name,birth_year,hourly_wage,address,zipcode
b1,Mark Levene,1987,29.5,"108 Clement St, San Francisco",94107.0
b2,Bill Bridge,1986,32,"3131 Webster St, San Francisco",
b3,Mike Franklin,1988,27.5,"1652 Stockton St, San Francisco",94122
b4,,1982,26,"108 South Park, San Francisco",
b5,Alfons Kemper,1984,35,"170 Post St, Apt 4,  San Francisco",94122
b6,Michael Brodie,1987,32.5,,94107

@HyukjinKwon @ueshin Do you plan to correct the differences in inferSchema logic from a Koalas user's point of view later?

also.. another issue is found.

this is internal sdf

|__index_level_0__| ID|          name|birth_year|hourly_wage|             address|zipcode|__natural_order__|
+-----------------+---+--------------+----------+-----------+--------------------+-------+-----------------+
|                0| b1|   Mark Levene|      1987|       29.5|108 Clement St, S...|  94107|
      0|
|                1| b2|   Bill Bridge|      1986|       32.0|3131 Webster St, ...|   null|
      1|
|                2| b3| Mike Franklin|      1988|       27.5|1652 Stockton St,...|  94122|
      2|
|                3| b4|          null|      1982|       26.0|108 South Park, S...|   null|
      3|
|                4| b5| Alfons Kemper|      1984|       35.0|170 Post St, Apt ...|  94122|
      4|
|                5| b6|Michael Brodie|      1987|       32.5|                null|  94107|
      5|
+-----------------+---+--------------+----------+-----------+--------------------+-------+-----------------+

and this is result of "print(kdf)" or "kdf.to_string()"

   ID            name  birth_year  hourly_wage                             address  zipcode
0  b1     Mark Levene        1987         29.5       108 Clement St, San Francisco  94107.0
1  b2     Bill Bridge        1986         32.0      3131 Webster St, San Francisco      NaN
2  b3   Mike Franklin        1988         27.5     1652 Stockton St, San Francisco  94122.0
3  b4            None        1982         26.0       108 South Park, San Francisco      NaN
4  b5   Alfons Kemper        1984         35.0  170 Post St, Apt 4,  San Francisco  94122.0
5  b6  Michael Brodie        1987         32.5                                None  94107.0

In the test results, the __zip_code__ column appears as float type, because Koalas converts it to Pandas Dataframe during the __to_string()__ process and outputs it. Since Pandas has missing values, Koalas converts __zip_code__ column to float type. I think Koalas should choose between unifying __inferSchema__ logic or developing a koalas-specific __to_string()__ without going through Pandas transformation on output.

[HyukjinKwon]

Missing value being float is known issue and that's one of thing even pandas authors don't quite like. So far, Koalas tried to copy this behaviour but In pandas 1.0.0, it targets to replace np.nan (https://pandas.pydata.org/pandas-docs/version/1.0.0/whatsnew/v1.0.0.html#experimental-na-scalar-to-denote-missing-values).

I think it's time for Koalas to consider to keep the type but interprets as None or pd.NA.

[itholic]

I think it's time for Koalas to consider to keep the type but interprets as None or pd.NA.

totally agree. maybe related with #1203

[Callum027]

@beobest2 , thanks for investigating this so quickly. That makes a lot of sense. For any data imported via CSV, while not ideal, I think importing it via pandas first and using ks.from_pandas is an acceptable solution for now.

[HyukjinKwon]

I am resolving this as a duplicate of #1203 at this moment.