<jupyter_start><jupyter_text>SQL and Pandas DataFramesCloud Datalab allows you to use SQL to interact with BigQuery. However, SQL is just the starting point. What Cloud Datalab enables you to do is combine the power of declarative SQL, and imperative code (Python) to perform interesting data analysis, visualization and transformation tasks.As a result, you can use a number of Python data analysis, data wrangling and visualization libraries, such as `numpy`, `pandas`, `matplotlib` and many others. A number of these libraries build on top of a `DataFrame` object.This notebook shows how you can easily integrate these capabilites together in a single notebook. This functionality is provided by the BigQuery APIs, so the first step is of course to import the module, along with Pandas.<jupyter_code>import gcp.bigquery as bq
import pandas as pd<jupyter_output><empty_output><jupyter_text>From SQL to DataFramesThis is quite straightforward. Define some SQL, execute it and get a reference to the results in the form of a `DataFrame`.<jupyter_code>%%sql --module requests
SELECT timestamp, latency, endpoint
FROM [cloud-datalab-samples:httplogs.logs_20140615]
WHERE endpoint = 'Popular' OR endpoint = 'Recent'
%%bigquery sample --count 5 --query requests<jupyter_output><empty_output><jupyter_text>Working with DataFrames The code below constructs a BigQuery `Query` instance, executes the query, and converts the results into a `DataFrame`.<jupyter_code>df = bq.Query(requests).to_dataframe()
len(df)<jupyter_output><empty_output><jupyter_text>Data Manipulation The resulting `DataFrame` can be further transformed, sliced, projected, and inspected using the extensive set of APIs available on the class. Lets print out the first 5 rows.<jupyter_code>df.head(5)<jupyter_output><empty_output><jupyter_text>Or it can be inspected for schema.<jupyter_code>df.dtypes<jupyter_output><empty_output><jupyter_text>Or further transformed locally, for example to perform grouping.<jupyter_code>groups = df.groupby('endpoint')
groups.dtypes
for name, df_group in groups:
  print '%s - %d items' % (name, len(df_group))
  print df_group.head(3)
  print<jupyter_output>Popular - 7658 items
                   timestamp  latency endpoint
0 2014-06-15 07:00:01.514197       22  Popular
1 2014-06-15 07:00:01.700211       21  Popular
2 2014-06-15 07:00:01.973441       21  Popular

Recent - 734 items
                    timestamp  latency endpoint
15 2014-06-15 07:02:21.453438      427   Recent
21 2014-06-15 07:03:48.191783      331   Recent
25 2014-06-15 07:04:57.352634      317   Recent<jupyter_text>And then analyze a dimension per group.<jupyter_code>groups['latency'].describe()<jupyter_output><empty_output><jupyter_text>Or even run a set of custom aggregation functions.<jupyter_code>def unique(x):
  return sorted(set(list(x)))

groups['latency'].agg({ 'list': lambda x: list(x), 'unique': lambda x: unique(x) })<jupyter_output><empty_output><jupyter_text>Data VisualizationDataFrame provides built-in visualization capabilities using `matplotlib`. **Conversion to TimeSeries Data**Right now, the DataFrame instance we have contains timestamps, latencies, and endpoints. Lets reshape this DataFrame so each endpoint is a column instead, i.e. is an independent series. The timestamp itself will be the index row. For timestamps for which one of the endpoints has no data point, we'll just use the previous value.<jupyter_code>df_series = df.pivot(index='timestamp', columns='endpoint', values='latency').fillna(method = 'backfill')
df_series[10:20]
len(df_series)
df_series.plot(logy = True)<jupyter_output><empty_output><jupyter_text>**Resampling**The plot above is fairly useless. The time-series data we have is at arbitrary timestamps. Essentially whenever a request was processed. It is irregularly spaced, and fairly large number of data points. DataFrames provide the ability to resample a time-series into more useful time windows.<jupyter_code>df_series.resample(rule = '10min').plot(logy = True)<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup