to_gbq: Allow creation of new tables from DataFrame (and generate schema)

[jtratner]

Small extension on top the to_gbq so that you can actually create new tables given only an existing dataframe. Given an arbitrary DataFrame with a non hierarchical-index, create a schema from it. For now, we'd likely assume that object dtype columns are string and maybe allow for specifying some or all columns for the schema so that int columns with nulls come out correctly (otherwise, they'd be coerced to float columns b/c of nan stuff).

E.g.:

In [6]: import pandas as pd

In [7]: import pandas.util.testing as testing

In [8]: df = testing.makeMixedDataFrame()

In [9]: df
Out[9]:
   A  B     C          D
0  0  0  foo1 2009-01-01
1  1  1  foo2 2009-01-02
2  2  0  foo3 2009-01-05
3  3  1  foo4 2009-01-06
4  4  0  foo5 2009-01-07

In [10]: df.dtypes
Out[10]:
A           float64
B           float64
C            object
D    datetime64[ns]
dtype: object

Then you could do something like:

In [11]: generate_bq_schema(df)
Out[11]:
{'fields': [{'name': 'A', 'type': 'FLOAT'},
  {'name': 'B', 'type': 'FLOAT'},
  {'name': 'C', 'type': 'STRING'},
  {'name': 'D', 'type': 'TIMESTAMP'}]}

and with a named index, that could be added to the schema as well. For now, we could stick to requiring non-hierarchical/MultiIndex, but maybe we could use record types for an index that's MultiIndex in the future?

[jtratner]

cc @jacobschaer - I think you're the main one to ask on this?

[jtratner]

@jacobschaer for context - there's a Bloomberg Hackathon that's happening next Saturday and I'm thinking this could be a good project for someone who uses BigQuery and/or Pandas

[jacobschaer]

Sounds like something someone asked a while ago on Stack. See

http://stackoverflow.com/questions/21886742/convert-pandas-dtypes-to-bigquery-type-representation

[jtratner]

yeah, it's certainly not that complicated, just would make it easier for
people to write to slap-dash write to bigquery. That SO post is probably
80% of the way there too.

On Fri, Sep 19, 2014 at 3:46 PM, Jacob Schaer notifications@github.com
wrote:

Sounds like something someone asked a while ago on Stack. See

http://stackoverflow.com/questions/21886742/convert-pandas-dtypes-to-bigquery-type-representation

—
Reply to this email directly or view it on GitHub
#8325 (comment).

I'm currently using pandas for a project I'm working on and would really like to see a new feature that allows users to create new tables in google big query using to_gbq. I notice that the ability to create tables from schema was removed in #6937.

I would like to try and develop this feature if no one else is working on it.