Question

Open an AI platform jupyter notebook and load the invoices and vendors data from big query in to separate pandas dataframes.

Answer 1

1. Type the following Python code into the next cell to import the BigQuery Python client library and initialize a client. The BigQuery client is used to send and receive messages from the BigQuery API.

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

2. Click Run.

3. Use the Client.query() method to run a query. In the next cell, enter the following code to run a query to retrieve the annual count of plural births by plurality (2 for twins, 3 for triplets, and so on).

   sql = """
   SELECT
       plurality,
       COUNT(1) AS count,
       year
   FROM
       `bigquery-public-data.samples.natality`
   WHERE
       NOT IS_NAN(plurality) AND plurality > 1
   GROUP BY
       plurality, year
   ORDER BY
       count DESC
   """
   df = client.query(sql).to_dataframe()
   df.head()

4. Click Run.

5. To chart the query results in your DataFrame, insert the following code into the next cell to pivot the data and create a stacked bar chart of the count of plural births over time.

   pivot_table = df.pivot(index='year', columns='plurality', values='count')
   pivot_table.plot(kind='bar', stacked=True, figsize=(15, 7));

6. Click Run.

7. The chart appears below the code block.

   

8. In the next cell, enter the following query to retrieve the count of births by the number of gestation weeks.

   sql = """
   SELECT
       gestation_weeks,
       COUNT(1) AS count
   FROM
       `bigquery-public-data.samples.natality`
   WHERE
       NOT IS_NAN(gestation_weeks) AND gestation_weeks <> 99
   GROUP BY
       gestation_weeks
   ORDER BY
       gestation_weeks
   """
   df = client.query(sql).to_dataframe()

   Note: Because the gestation_weeks field allows null values and stores unknown values as 99, this query excludes records where gestation_weeks is null or 99.

9. Click Run.

10. To chart the query results in your DataFrame, paste the following code in the next cell.

    ax = df.plot(kind='bar', x='gestation_weeks', y='count', figsize=(15,7))
    ax.set_title('Count of Births by Gestation Weeks')
    ax.set_xlabel('Gestation Weeks')
    ax.set_ylabel('Count');

    Note: This code plots the data as a bar chart and sets a chart title, x-axis label, and y-axis label. There are many other options you can specify when plotting data. For more information, see pandas.DataFrame.plot in the pandas API reference.

11. Click Run.

12. The bar chart appears below the code block.