<jupyter_start><jupyter_text>SQL and Pandas DataFramesGoogle Cloud Datalab allows you to use SQL to interact with Google BigQuery. However, SQL is just the starting point. Datalab enables you to combine the power of declarative SQL with imperative code (Python) to perform interesting data analysis, visualization, and transformation tasks.You can use a number of Python data analysis, data wrangling, and visualization libraries, such as `numpy`, `pandas`, `matplotlib`, and many others. Several of these libraries build on top of a `DataFrame` object.This notebook shows how to 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 google.datalab.bigquery as bq
import pandas as pd<jupyter_output><empty_output><jupyter_text>From SQL to DataFramesIn the [BigQuery APIs notebook](https://8081-dot-12652225-dot-devshell.appspot.com/notebooks/datalab/docs/tutorials/BigQuery/BigQuery%20APIs.ipynb), we've covered how to define a SQL query, execute it, and get a reference to the results in the form of a `DataFrame`. Let's start with a query to see what more we can do.<jupyter_code>%%bq query -n requests
SELECT timestamp, latency, endpoint
FROM `cloud-datalab-samples.httplogs.logs_20140615`
WHERE endpoint = 'Popular' OR endpoint = 'Recent'
%%bq sample --count 5 --query requests
df = requests.execute(output_options=bq.QueryOutput.dataframe()).result()
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. Let's print out the first five 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
734 2014-06-15 12:32:44.619481      256  Popular
735 2014-06-15 15:33:51.416032      256  Popular
736 2014-06-15 07:11:53.720912      256  Popular

Recent - 734 items
                   timestamp  latency endpoint
0 2014-06-15 19:40:43.414446      256   Recent
1 2014-06-16 03:32:55.519498      256   Recent
2 2014-06-16 01:38:26.398083      256   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**Our DataFrame instance contains timestamps, latencies, and endpoints. Let's reshape this DataFrame so that each endpoint is a column, that 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 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 not meaningful. Our time-series data is taken arbitrary timestamps, whenever a request was processed. It is irregularly spaced, and there are a large number of data points. DataFrames provide the ability to resample a time-series into more meaningful time windows.<jupyter_code>df_series.resample(rule='10min').mean().plot(logy = True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08967054743621501, 'fraction_numerical': 0.04656923458013376, 'mean_word_length': 5.587275693311582, 'pattern_counts': {'":': 0, '<': 36, '<?xml version=': 0, '>': 36, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '145838', 'license': 'MIT', 'n_tokens_mistral': 1355, 'n_tokens_neox': 1199, 'n_words': 422, 'path': 'SQL and Pandas DataFrames.ipynb', 'repo_name': 'kevinpulido89/GCP', 'size': '4037'}