pandas.io.gbq.read_gbq() returns incorrect results

[markdregan]

When using the read_gbq() function on a BigQuery table, incorrect results are returned.

I compare the output from read_gbq() to that of a CSV export from BigQuery directly. Interestingly, there are the same number of rows in each output - however, there are many duplicates in the read_gbq() output.

I'm using Pandas '0.13.0rc1-125-g4952858' on a Mac 10.9 using Python 2.7. Numpy '1.8.0'.

The code I execute to load the data in pandas:
churn_data = gbq.read_gbq(train_query, project_id = projectid)

I can't share the underlying data. What additional data/info would be useful for root causing?

The output data is ~400k lines.

[jreback]

cc @jacobschaer

[jacobschaer]

@markdregan Can you possibly share the type of query? For instance, was it something like "SELECT *", or what kind of filters were you using? Also, what do you mean by duplicates?

It would also be useful if you could send your bq.py version. This can be retrieved from the command line using:
bq version

[markdregan]

This is BigQuery CLI v2.0.17

The query was similar to below:

SELECT variable_a, var_b, etc FROM dataset.table_name WHERE var_x IN ("String A", "String B") AND exception_flag = "5. No Flag" AND (renewal_date_str BETWEEN "2014-03-31" AND "2014-07-01" OR renewal_date_str BETWEEN "2013-01-01" AND "2013-12-31")

Running the exact same query in the BQ Web UI produces different output. By duplicates, I mean there are rows duplicated. In BQ UI, I see 1 row. In the Pandas dataframe, there are some duplicates. Quantifying this, there are ~100k rows that have duplicate key values. Total dataframe is ~404k.

Let me know if there are other tests I can do.

[markdregan]

On the off chance this is a factor, I am unable to access any gbq functions when I import pandas as
import pandas as pd

I am only able to access read_gbq() when I import the following:
from pandas.io import gbq

Seemed unusual. Even the IPython tab completion couldn't find gbq in pandas.io

[jacobschaer]

How are you comparing the two? Do you export from Web UI as CSV and then import into pandas using read_csv() ? To clarify, you:

1. Run your query in the web console: [bigquery.cloud.google.com]
2. You get 404k rows as a result
3. You run your query using read_gbq() and also get 404k rows as a result
4. You somehow compare these 404k row result sets and identify that 100k rows from read_gbq() are duplicates. However, there are no duplicates in the results from the Web Console?

Or perhaps, when you say: 'In BQ UI, I see 1 row. ' does this mean you are getting 403.999k rows more rows from pandas.io.gbq?

Please try using the command line tool to isolate the problem to our code or Google's code:
bq query --format=csv 'select {YOUR QUERY} from {YOUR DATASET}.{YOUR TABLE}

As far as the imports, I had thought we were in the top level of pandas, but I normally just do:
from pandas.io import gbq

[markdregan]

Correct, I execute the query in the web console. I get 404k rows. I save the result as a table so I can query it later.

In pandas, I import using read_gbq and the same query as above. I get 404k rows.

I note that the following query in the web console produces no sum greater than 1:

SELECT
  domain_name,
  SUM(1)
FROM dataset.table
GROUP BY 1

I then note that the equivalent pandas function does return many results where sum > 1

churn_data.groupby(by='domain_name').size().order(ascending=False)

I then do some spot checks between pandas and BQ web console. I do this by filtering on specific domain_names. This showed pandas dataframe to have duplicates and the BQ web console to have only one row per domain. The latter is expected as it is a unique key for the data set.

[jreback]

read_gbq is not in the top-level name space as its experimental (though it prob should be as we have other experimental modules there)....will add an issue to do this, see #5843

[markdregan]

I can confirm running the query via the command line returns the same results as the web console. And that they conflict with the results from data loaded into pandas using read_gbq()

[markdregan]

I limited the query down to 3 fields. One of the fields seems to contain both INTEGERS and FLOATS (eg. 78 vs 34.12). The BQ web console lists the field as a float. When I remove this field (only 2 fields remaining) no duplicates are generated in the pandas dataframe.

I then add the field back but I cast it using BQ SQL INTEGER() function. When I do so, pandas imports the data with no duplicates.

I haven't been able to exhaustively test this with other variables. I'm also aware of some fields containing Infinity values - so I wouldn't rule them out as possible culprits too.

[azbones]

@markdregan Interesting- that must be the problem. While you were doing that, I was trying to duplicate this with the public shakespeare dataset from BigQuery and haven't found any dup problems yet.

The csv case was SELECT * FROM [publicdata:samples.shakespeare]; download the whole table via the Google BigQuery UI, save as temp table, export to Google Cloud Storage and then download and load into pandas via from_csv.

The gbq case was:

from pandas.io import gbq

query='SELECT * FROM [publicdata:samples.shakespeare];'
project_id=xxx
df_gbq=gbq.read_gbq(query,project_id)

The results were:

df_csv.describe()
Out[45]: 
          word_count    corpus_date
count  164656.000000  164656.000000
mean        5.744370    1551.364572
std        25.706592     275.347840
min         1.000000       0.000000
25%         1.000000    1595.000000
50%         1.000000    1599.000000
75%         3.000000    1606.000000
max       995.000000    1612.000000

[8 rows x 2 columns]

df_gbq.describe()
Out[46]: 
          word_count    corpus_date
count  164656.000000  164656.000000
mean        5.744370    1551.364572
std        25.706592     275.347840
min         1.000000       0.000000
25%         1.000000    1595.000000
50%         1.000000    1599.000000
75%         3.000000    1606.000000
max       995.000000    1612.000000

[8 rows x 2 columns]

df_csv['word'].nunique()
Out[47]: 32786

df_gbq['word'].nunique()
Out[48]: 32786

[jacobschaer]

From your last comment, it makes me think there's a logic issue in our casting. I'm not looking at the code right now, but I do remember there was some discussion on how to handle INTEGERS/FLOATS. While BigQuery does support Integer types, they can be "Null", which means that raw int's would have problems (an issue inherited from numpy if I recall). So, we use an internal pandas method to downcast numeric columns - ultimately resulting in mixed types (which is not such a bad thing).

I'll take a peek at the code and see if anything pops out to me. One thing that would be very appreciated is if you can try to replicate this using BigQuery's public sample datasets. If you can give me an example using those public datasets (I'm sure one of them must have FLOATS and INTEGERS), we can make a Unit test out of this and ensure it doesn't happen again.

[azbones]

@jacobschaer When we talk to Google next, we need to include this in our discussions of test datasets. Ideally, we can not only solve the solution for testing writes, but also having a public dataset with some of these cases. Do you recall Felipe's github handle? Maybe we can copy him on this issue...

[jacobschaer]

@markdregan Can you post the dtypes of the dataframe from gbq, along with the column types reported by BigQuery UI?