Skip to content

BigQuery: Fixed pandas DataFrames being returned with incorrect index.#7953

Merged
tswast merged 2 commits intogoogleapis:masterfrom
eriknil:fix-bq-pandas-incorrect-index
May 13, 2019
Merged

BigQuery: Fixed pandas DataFrames being returned with incorrect index.#7953
tswast merged 2 commits intogoogleapis:masterfrom
eriknil:fix-bq-pandas-incorrect-index

Conversation

@eriknil
Copy link
Copy Markdown
Contributor

@eriknil eriknil commented May 13, 2019 •
edited
Loading

When loading large datasets from BIgQuery as pandas DataFrames, sometimes the index contains duplicates. This happens when the results are collected as multiple DataFrames and then concatenated without resetting the index.

As an example, we get 0, 1 repeated in the index:

In [1]: import pandas as pd
In [2]: x = pd.DataFrame({"a": [1, 2]})
   ...: pd.concat([x, x])
Out[2]:
   a
0  1
1  2
0  1
1  2

which has some unintended consequences when we try to subset:

In [3]: pd.concat([x, x]).loc[0]
Out[3]:
   a
0  1
0  1

Instead, by setting ignore_index=True we get:

In [4]: pd.concat([x, x], ignore_index=True)
Out[4]:
   a
0  1
1  2
2  1
3  2

From the pandas documentation https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.concat.html:

ignore_index : boolean, default False
If True, do not use the index values along the concatenation axis. The resulting axis will be labeled 0, …, n - 1. This is useful if you are concatenating objects where the concatenation axis does not have meaningful indexing information. Note the index values on the other axes are still respected in the join.

In our case the indexes really don't have any meaningful information.

Reproducible example

We can see this in action by running the following query on the crypto-dash dataset:

from google.cloud import bigquery
bq_client = bigquery.Client()

query = """
SELECT
  block_timestamp_month
FROM
  `bigquery-public-data.crypto_dash.transactions`
LIMIT
  1000000
"""
data = bq_client.query(query).result().to_dataframe()

and then checking if the indexes are unique:

print(data.index.is_unique)
> False

I have replicated the problem with two different unit tests, and fixed it in this PR.

@eriknil eriknil requested a review from a team May 13, 2019 00:15
@googlebot
Copy link
Copy Markdown

googlebot commented May 13, 2019

Thanks for your pull request. It looks like this may be your first contribution to a Google open source project (if not, look below for help). Before we can look at your pull request, you'll need to sign a Contributor License Agreement (CLA).

📝 Please visit https://cla.developers.google.com/ to sign.

Once you've signed (or fixed any issues), please reply here (e.g. I signed it!) and we'll verify it.

What to do if you already signed the CLA

Individual signers
Corporate signers

ℹ️ Googlers: Go here for more info.

@googlebot googlebot added the cla: no This human has *not* signed the Contributor License Agreement. label May 13, 2019
@eriknil
Copy link
Copy Markdown
Contributor Author

eriknil commented May 13, 2019

I signed it!

@googlebot
Copy link
Copy Markdown

googlebot commented May 13, 2019

CLAs look good, thanks!

ℹ️ Googlers: Go here for more info.

@googlebot googlebot added cla: yes This human has signed the Contributor License Agreement. and removed cla: no This human has *not* signed the Contributor License Agreement. labels May 13, 2019
tswast
tswast approved these changes May 13, 2019
View reviewed changes
Copy link
Copy Markdown
Contributor

@tswast tswast left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Thanks for the contribution, explanation, and the unit tests!

@tswast tswast merged commit 53e492c into googleapis:master May 13, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@tswast tswast tswast approved these changes

Assignees

No one assigned

Labels

cla: yes This human has signed the Contributor License Agreement.

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

3 participants

@eriknil @googlebot @tswast