BigQuery : ArrowTypeError when trying to push DataFrame with int columns with NaN values #22

Activity

1. tswast commented on Apr 12, 2019

   

   tswast

   on Apr 12, 2019

   Contributor

   More actions

   Thanks for the report, @sebastienharinck ! I love the new nullable integer dtype, but I haven't gotten a chance to use it much yet. It looks like there are some problems in parquet serialization with the new type. I'll dig into this.

   FYI: marking as P2, since the Int64 dtype is still marked experimental in pandas.
2. tswast

   self-assigned this

   on Apr 12, 2019
3. HemangChothani commented on Oct 2, 2019

   

   HemangChothani

   on Oct 2, 2019

   Contributor

   More actions

   The problem might be with the pyarrow library as found in another git issue-4168 that points to an open Jira ticket 5379.
4. tswast commented on Oct 7, 2019

   

   tswast

   on Oct 7, 2019

   Contributor

   More actions

   It appears this is fixed in Arrow 0.15.0 and Pandas master branch with pandas-dev/pandas#28368

   Let's keep this issue open to update unit/system tests once the next pandas release happens.
5. tswast commented on Oct 11, 2019

   

   tswast

   on Oct 11, 2019

   Contributor

   More actions

   Since the arrow support isn't in the 0.24.x branch of pandas (https://github.com/pandas-dev/pandas/blob/0.24.x/pandas/core/arrays/integer.py), this will actually likely have to wait for pandas 1.0.
6. tswast

   assigned

   plamut

   and unassigned

   tswast

   on Jan 8, 2020
7. tswast commented on Jan 8, 2020

   

   tswast

   on Jan 8, 2020

   Contributor

   More actions

   Peter, once pandas 1.0 is released, let's add some unit and system tests for uploading pandas dataframes with the nullable integer dtype.

   👍React with 👍1Reacted by Peter Lamut
8. plamut commented on Jan 9, 2020

   

   plamut

   on Jan 9, 2020

   Contributor

   More actions

   Blocked until pandas 1.0 is released.
9. plamut

   transferred this issue fromgoogleapis/google-cloud-pythonon Feb 4, 2020
10. product-auto-label

    added

    api: bigqueryIssues related to the googleapis/python-bigquery API.Issues related to the googleapis/python-bigquery API.

    on Feb 4, 2020
11. plamut

    added

    status: blockedResolving the issue is dependent on other work.Resolving the issue is dependent on other work.

    type: feature request‘Nice-to-have’ improvement, new feature or different behavior or design.‘Nice-to-have’ improvement, new feature or different behavior or design.

    on Feb 4, 2020
12. HemangChothani commented on Feb 12, 2020

    

    HemangChothani

    on Feb 12, 2020

    Contributor

    More actions

    @plamut New version of pandas has been released recently,have you started working on this?if not then may i work on this issue?
13. plamut commented on Feb 12, 2020

    

    plamut

    on Feb 12, 2020

    Contributor

    More actions

    @HemangChothani Not yet, as I was occupied with other things (repo split, timeout issues...). Feel free to start working on it.
14. plamut

    assigned

    HemangChothani

    on Feb 12, 2020
15. plamut

    removed

    status: blockedResolving the issue is dependent on other work.Resolving the issue is dependent on other work.

    on Feb 12, 2020
16. plamut

    removed their assignment

    on Feb 12, 2020
17. Ruotongw commented on Apr 12, 2020

    

    Ruotongw

    on Apr 12, 2020

    More actions

    Hello !

    I am a new user of BigQuery python package. And I encounter a problem.

    To simplify, I have a simple pandas DataFrame with a None value :

    df = pd.DataFrame({'x': [1, 2, None, 4]})

    For pandas, this will be a NaN and the dtype of column will be float64 by default. But I would like to push this DataFrame on BigQuery with an integer format for the column x. (the None will be a null)

    Thanks to the new version of pandas (>= 0.24), we can change the type of the column and keep the NaN value :

    df['x'] = df['x'].astype('Int64')
    print(df.dtypes)
    # Int64

    source : https://stackoverflow.com/questions/11548005/numpy-or-pandas-keeping-array-type-as-integer-while-having-a-nan-value

    But when I try to push this DataFrame to BigQuery, I encounter an ArrowTypeError :

    ArrowTypeError: ('Did not pass numpy.dtype object', 'Conversion failed for column x with type Int64')

    I find some solutions that allow me to do the stuff. But I always need to update my table after execute load_table_from_dataframe... I think it must have a better solution for doing this. Any ideas please ?

    At the end, I would like to have this table in BigQuery :

    Line | x    |
    1    | 1    |
    2    | 2    |
    3    | null |
    4    | 4    |
    

    with x as an INTEGER type.

    The full code

    import pandas as pd
    from google.cloud import bigquery
    
    print(pd.__version__)
    print(bigquery.__version__)
    
    df = pd.DataFrame({'x': [1, 2, None, 4]})
    
    df['x'] = df['x'].astype('Int64')
    print(df.dtypes)
    
    client = bigquery.Client()
    dataset_ref = client.dataset('test_dataset')
    table_ref = dataset_ref.table('test')
    
    client.load_table_from_dataframe(df, table_ref).result()

    Stack trace

    ArrowTypeError                            Traceback (most recent call last)
    <ipython-input-4-fe43ea977e67> in <module>
         14 table_ref = dataset_ref.table('test')
         15 
    ---> 16 client.load_table_from_dataframe(df, table_ref).result()
    
    /usr/local/lib/python3.7/site-packages/google/cloud/bigquery/client.py in load_table_from_dataframe(self, dataframe, destination, num_retries, job_id, job_id_prefix, location, project, job_config)
       1045         """
       1046         buffer = six.BytesIO()
    -> 1047         dataframe.to_parquet(buffer)
       1048 
       1049         if job_config is None:
    
    /usr/local/lib/python3.7/site-packages/pandas/core/frame.py in to_parquet(self, fname, engine, compression, index, partition_cols, **kwargs)
       2201         to_parquet(self, fname, engine,
       2202                    compression=compression, index=index,
    -> 2203                    partition_cols=partition_cols, **kwargs)
       2204 
       2205     @Substitution(header='Whether to print column labels, default True')
    
    /usr/local/lib/python3.7/site-packages/pandas/io/parquet.py in to_parquet(df, path, engine, compression, index, partition_cols, **kwargs)
        250     impl = get_engine(engine)
        251     return impl.write(df, path, compression=compression, index=index,
    --> 252                       partition_cols=partition_cols, **kwargs)
        253 
        254 
    
    /usr/local/lib/python3.7/site-packages/pandas/io/parquet.py in write(self, df, path, compression, coerce_timestamps, index, partition_cols, **kwargs)
        111         else:
        112             from_pandas_kwargs = {'preserve_index': index}
    --> 113         table = self.api.Table.from_pandas(df, **from_pandas_kwargs)
        114         if partition_cols is not None:
        115             self.api.parquet.write_to_dataset(
    
    /usr/local/lib/python3.7/site-packages/pyarrow/table.pxi in pyarrow.lib.Table.from_pandas()
    
    /usr/local/lib/python3.7/site-packages/pyarrow/pandas_compat.py in dataframe_to_arrays(df, schema, preserve_index, nthreads, columns, safe)
        466         arrays = [convert_column(c, t)
        467                   for c, t in zip(columns_to_convert,
    --> 468                                   convert_types)]
        469     else:
        470         from concurrent import futures
    
    /usr/local/lib/python3.7/site-packages/pyarrow/pandas_compat.py in <listcomp>(.0)
        465     if nthreads == 1:
        466         arrays = [convert_column(c, t)
    --> 467                   for c, t in zip(columns_to_convert,
        468                                   convert_types)]
        469     else:
    
    /usr/local/lib/python3.7/site-packages/pyarrow/pandas_compat.py in convert_column(col, ty)
        461             e.args += ("Conversion failed for column {0!s} with type {1!s}"
        462                        .format(col.name, col.dtype),)
    --> 463             raise e
        464 
        465     if nthreads == 1:
    
    /usr/local/lib/python3.7/site-packages/pyarrow/pandas_compat.py in convert_column(col, ty)
        455     def convert_column(col, ty):
        456         try:
    --> 457             return pa.array(col, type=ty, from_pandas=True, safe=safe)
        458         except (pa.ArrowInvalid,
        459                 pa.ArrowNotImplementedError,
    
    /usr/local/lib/python3.7/site-packages/pyarrow/array.pxi in pyarrow.lib.array()
    
    /usr/local/lib/python3.7/site-packages/pyarrow/array.pxi in pyarrow.lib._ndarray_to_array()
    
    /usr/local/lib/python3.7/site-packages/pyarrow/array.pxi in pyarrow.lib._ndarray_to_type()
    
    /usr/local/lib/python3.7/site-packages/pyarrow/error.pxi in pyarrow.lib.check_status()
    
    ArrowTypeError: ('Did not pass numpy.dtype object', 'Conversion failed for column x with type Int64')
    

    Thank you :)

    Hi,

    I would like to ask what is the temporary solution you found? I am encountering the same issue now. Thank you!
18. HemangChothani commented on Apr 13, 2020

    

    HemangChothani

    on Apr 13, 2020

    Contributor

    More actions

    @Ruotongw This feature is supported in pandas >1.0 versions, so make sure you are using the correct version of pandas.

    If still you are facing the issue, please provide the environment details.
19. HemangChothani

    closed this as completedin #39on May 13, 2020