<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 SQL and Pandas DataFrames
Google 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.
Step1:  From SQL to DataFrames
In the BigQuery APIs notebook, 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.
Step2:  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.
Step3:  Or it can be inspected for schema,
Step4:  or further transformed locally, for example to perform grouping,
Step5:  and then analyze a dimension per group,
Step6:  or even run a set of custom aggregation functions.
Step7:  Data Visualization
DataFrame 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.
Step8:  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.
<ASSISTANT_TASK:>
Python Code:
import google.datalab.bigquery as bq
import pandas as pd
Explanation: SQL and Pandas DataFrames
Google 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.
End of explanation
%%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)
Explanation: From SQL to DataFrames
In the BigQuery APIs notebook, 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.
End of explanation
df.head(5)
Explanation: 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.
End of explanation
df.dtypes
Explanation: Or it can be inspected for schema,
End of explanation
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()
Explanation: or further transformed locally, for example to perform grouping,
End of explanation
groups['latency'].describe()
Explanation: and then analyze a dimension per group,
End of explanation
def unique(x):
  return sorted(set(list(x)))
groups['latency'].agg({ 'list': lambda x: list(x), 'unique': lambda x: unique(x) })
Explanation: or even run a set of custom aggregation functions.
End of explanation
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)
Explanation: Data Visualization
DataFrame 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.
End of explanation
df_series.resample(rule='10min').mean().plot(logy = True)
Explanation: 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.
End of explanation
<END_TASK>