labVersion = 'cs105x-lab2-1.1.0'

# COMMAND ----------

# MAGIC %md

# MAGIC ## How to complete this lab

# MAGIC

# MAGIC This lab is broken up into sections with bite-sized examples for demonstrating Spark functionality for log processing.

# MAGIC

# MAGIC It consists of 5 parts:

# MAGIC * *Part 1:* Introduction and Imports

# MAGIC * *Part 2:* Exploratory Data Analysis

# MAGIC * *Part 3*: Analysis Walk-Through on the Web Server Log File

# MAGIC * *Part 4*: Analyzing Web Server Log File

# MAGIC * *Part 5*: Exploring 404 Response Codes

# MAGIC

# MAGIC Also, at the very bottom:

# MAGIC

# MAGIC * *Appendix A*: Submitting Your Exercises to the Autograder

# COMMAND ----------

# MAGIC %md

# MAGIC ## Part 1: Introduction and Imports

# MAGIC

# MAGIC ### A note about DataFrame column references

# MAGIC

# MAGIC In Python, it's possible to access a DataFrame's columns either by attribute (`df.age`) or by indexing (`df['age']`). Referring to a column by attribute (`df.age`) is very Pandas-like, and it's highly convenient, especially when you're doing interactive data exploration. But it can fail, for reasons that aren't obvious. For example:

# COMMAND ----------

throwaway_df = sqlContext.createDataFrame([('Anthony', 10), ('Julia', 20), ('Fred', 5)], ('name', 'count'))

# throwaway_df.select(throwaway_df.count).show() # This line does not work. Please comment it out later.

# COMMAND ----------

# MAGIC %md

# MAGIC To understand why that failed, you have to understand how the attribute-column syntax is implemented.

# MAGIC

# MAGIC When you type `throwaway_df.count`, Python looks for an _existing_ attribute or method called `count` on the `throwaway_df` object. If it finds one, it uses it. Otherwise, it calls a special Python function (`__getattr__`), which defaults to throwing an exception. Spark has overridden `__getattr__` to look for a column on the DataFrame.

# MAGIC

# MAGIC **This means you can only use the attribute (dot) syntax to refer to a column if the DataFrame does not _already_ have an attribute with the column's name.**

# MAGIC

# MAGIC In the above example, there's already a `count()` method on the `DataFrame` class, so `throwaway_df.count` does not refer to our "count" column; instead, it refers to the `count()` _method_.

# MAGIC

# MAGIC To avoid this problem, you can refer to the column using subscript notation: `throwaway_df['count']`. This syntax will _always_ work.

# COMMAND ----------

throwaway_df.select(throwaway_df['count']).show()

# COMMAND ----------

# MAGIC %md

# MAGIC ### (1a) Library Imports

# MAGIC

# MAGIC

# MAGIC We can import standard Python libraries ([modules](https://docs.python.org/2/tutorial/modules.html)) the usual way. An `import` statement will import the specified module. In this lab, we will provide any imports that are necessary.

# MAGIC

# MAGIC Let's import some of the libraries we'll need:

# MAGIC

# MAGIC * `re`: The regular expression library

# MAGIC * `datetime`: Date and time functions

# MAGIC * `Test`: Our Databricks test helper library

# COMMAND ----------

import re

import datetime

from databricks_test_helper import Test

# COMMAND ----------

# Quick test of the regular expression library

m = re.search('(?<=abc)def', 'abcdef')

m.group(0)

# COMMAND ----------

# Quick test of the datetime library

print 'This was last run on: {0}'.format(datetime.datetime.now())

# COMMAND ----------

# MAGIC %md

# MAGIC ### (1b) Getting help

# MAGIC

# MAGIC Remember: There are some useful Python built-ins for getting help.

# COMMAND ----------

# MAGIC %md

# MAGIC You can use Python's [dir()](https://docs.python.org/2/library/functions.html?highlight=dir#dir) function to get a list of all the attributes (including methods) accessible through the `sqlContext` object.

# COMMAND ----------

# List sqlContext's attributes

dir(sqlContext)

# COMMAND ----------

# MAGIC %md

# MAGIC Alternatively, you can use Python's [help()](https://docs.python.org/2/library/functions.html?highlight=help#help) function to get an easier to read list of all the attributes, including examples, that the `sqlContext` object has.

# COMMAND ----------

# Use help to obtain more detailed information

help(sqlContext)

# COMMAND ----------

# Help can be used on any Python object

help(map)

help(Test)

# COMMAND ----------

# MAGIC %md

# MAGIC ## Part 2: Exploratory Data Analysis

# MAGIC

# MAGIC Let's begin looking at our data. For this lab, we will use a data set from NASA Kennedy Space Center web server in Florida. The full data set is freely available at <http://ita.ee.lbl.gov/html/contrib/NASA-HTTP.html>, and it contains all HTTP requests for two months. We are using a subset that only contains several days' worth of requests. The log file has already been downloaded for you.

# COMMAND ----------

# Specify path to downloaded log file

import sys

import os

log_file_path = 'dbfs:/' + os.path.join('databricks-datasets', 'cs100', 'lab2', 'data-001', 'apache.access.log.PROJECT')

# COMMAND ----------

# MAGIC %md

# MAGIC ### (2a) Loading the log file

# MAGIC

# MAGIC Now that we have the path to the file, let's load it into a DataFrame. We'll do this in steps. First, we'll use `sqlContext.read.text()` to read the text file. This will produce a DataFrame with a single string column called `value`.

# COMMAND ----------

base_df = sqlContext.read.text(log_file_path)

# Let's look at the schema

base_df.printSchema()

# COMMAND ----------

# MAGIC %md

# MAGIC Let's take a look at some of the data.

# COMMAND ----------

base_df.show(truncate=False)

# COMMAND ----------

# MAGIC %md

# MAGIC ### (2b) Parsing the log file

# COMMAND ----------

# MAGIC %md

# MAGIC If you're familiar with web servers at all, you'll recognize that this is in [Common Log Format](https://www.w3.org/Daemon/User/Config/Logging.html#common-logfile-format). The fields are:

# MAGIC

# MAGIC _remotehost rfc931 authuser [date] "request" status bytes_

# MAGIC

# MAGIC | field | meaning |

# MAGIC | ------------- | ---------------------------------------------------------------------- |

# MAGIC | _remotehost_ | Remote hostname (or IP number if DNS hostname is not available). |

# MAGIC | _rfc931_ | The remote logname of the user. We don't really care about this field. |

# MAGIC | _authuser_ | The username of the remote user, as authenticated by the HTTP server. |

# MAGIC | _[date]_ | The date and time of the request. |

# MAGIC | _"request"_ | The request, exactly as it came from the browser or client. |

# MAGIC | _status_ | The HTTP status code the server sent back to the client. |

# MAGIC | _bytes_ | The number of bytes (`Content-Length`) transferred to the client. |

# MAGIC

# MAGIC

# MAGIC Next, we have to parse it into individual columns. We'll use the special built-in [regexp\_extract()](http://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.functions.regexp_extract)

# MAGIC function to do the parsing. This function matches a column against a regular expression with one or more [capture groups](http://regexone.com/lesson/capturing_groups) and allows you to extract one of the matched groups. We'll use one regular expression for each field we wish to extract.

# MAGIC

# MAGIC If you can't read these regular expressions, don't worry. Trust us: They work. If you find regular expressions confusing (and they certainly _can_ be), and you want to learn more about them, start with the

# MAGIC [RegexOne web site](http://regexone.com/). You might also find [_Regular Expressions Cookbook_](http://shop.oreilly.com/product/0636920023630.do), by Jan Goyvaerts and Steven Levithan, to be helpful.

# MAGIC

# MAGIC _Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems._ (attributed to Jamie Zawinski)

# COMMAND ----------

from pyspark.sql.functions import split, regexp_extract

split_df = base_df.select(regexp_extract('value', r'^([^\s]+\s)', 1).alias('host'),

regexp_extract('value', r'^.*\s+(\d+)$', 1).cast('integer').alias('content_size'))

split_df.show(truncate=False)

# COMMAND ----------

# MAGIC %md

# MAGIC ### (2c) Data Cleaning

# MAGIC

# MAGIC Let's see how well our parsing logic worked. First, let's verify that there are no null rows in the original data set.

# COMMAND ----------

base_df.filter(base_df['value'].isNull()).count()

# COMMAND ----------

# MAGIC %md

# MAGIC If our parsing worked properly, we'll have no rows with null column values. Let's check.

# COMMAND ----------

bad_rows_df = split_df.filter(split_df['host'].isNull() |

split_df['content_size'].isNull())

bad_rows_df.count()

# COMMAND ----------

# MAGIC %md

# MAGIC Not good. We have some null values. Something went wrong. Which columns are affected?

# MAGIC

# MAGIC (Note: This approach is adapted from an [excellent answer](http://stackoverflow.com/a/33901312) on StackOverflow.)

# COMMAND ----------

from pyspark.sql.functions import col, sum

def count_null(col_name):

return sum(col(col_name).isNull().cast('integer')).alias(col_name)

# Build up a list of column expressions, one per column.

#

# This could be done in one line with a Python list comprehension, but we're keeping

# it simple for those who don't know Python very well.

exprs = []

for col_name in split_df.columns:

exprs.append(count_null(col_name))

# Run the aggregation. The *exprs converts the list of expressions into

# variable function arguments.

split_df.agg(*exprs).show()

# COMMAND ----------

# MAGIC %md

# MAGIC Okay, they're all in the `content_size` column. Let's see if we can figure out what's wrong. Our original parsing regular expression for that column was:

# MAGIC

# MAGIC ```

# MAGIC regexp_extract('value', r'^.*\s+(\d+)$', 1).cast('integer').alias('content_size')

# MAGIC ```

# MAGIC

# MAGIC The `\d+` selects one or more digits at the end of the input line. Is it possible there are lines without a valid content size? Or is there something wrong with our regular expression? Let's see if there are any lines that do not end with one or more digits.

# MAGIC

# MAGIC **Note**: In the expression below, `~` means "not".

# COMMAND ----------

bad_content_size_df = base_df.filter(~ base_df['value'].rlike(r'\d+$'))

bad_content_size_df.count()

# COMMAND ----------

# MAGIC %md

# MAGIC That's it! The count matches the number of rows in `bad_rows_df` exactly.

# MAGIC

# MAGIC Let's take a look at some of the bad column values. Since it's possible that the rows end in extra white space, we'll tack a marker character onto the end of each line, to make it easier to see trailing white space.

# COMMAND ----------

from pyspark.sql.functions import lit, concat

bad_content_size_df.select(concat(bad_content_size_df['value'], lit('*'))).show(truncate=False)

# COMMAND ----------

# MAGIC %md

# MAGIC Ah. The bad rows correspond to error results, where no content was sent back and the server emitted a "`-`" for the `content_size` field. Since we don't want to discard those rows from our analysis, let's map them to 0.

# COMMAND ----------

# MAGIC %md

# MAGIC ### (2d) Fix the rows with null content\_size

# MAGIC

# MAGIC The easiest solution is to replace the null values in `split_df` with 0. The DataFrame API provides a set of functions and fields specifically designed for working with null values, among them:

# MAGIC

# MAGIC * [fillna()](http://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.DataFrame.fillna), which fills null values with specified non-null values.

# MAGIC * [na](http://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.DataFrame.na), which returns a [DataFrameNaFunctions](http://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.DataFrameNaFunctions) object with many functions for operating on null columns.

# MAGIC

# MAGIC We'll use `fillna()`, because it's simple. There are several ways to invoke this function. The easiest is just to replace _all_ null columns with known values. But, for safety, it's better to pass a Python dictionary containing (column\_name, value) mappings. That's what we'll do.

# COMMAND ----------

# Replace all null content_size values with 0.

cleaned_df = split_df.na.fill({'content_size': 0})

# COMMAND ----------

# Ensure that there are no nulls left.

exprs = []

for col_name in cleaned_df.columns:

exprs.append(count_null(col_name))

cleaned_df.agg(*exprs).show()

# COMMAND ----------

# MAGIC %md

# MAGIC ### (2e) Parsing the timestamp.

# MAGIC

# MAGIC Okay, now that we have a clean, parsed DataFrame, we have to parse the timestamp field into an actual timestamp. The Common Log Format time is somewhat non-standard. A User-Defined Function (UDF) is the most straightforward way to parse it.

# COMMAND ----------

month_map = {

'Jan': 1, 'Feb': 2, 'Mar':3, 'Apr':4, 'May':5, 'Jun':6, 'Jul':7,

'Aug':8, 'Sep': 9, 'Oct':10, 'Nov': 11, 'Dec': 12

}

def parse_clf_time(s):

)

u_parse_time = udf(parse_clf_time)

logs_df = cleaned_df.select('*', u_parse_time(cleaned_df['timestamp']).cast('timestamp').alias('time')).drop('timestamp')

total_log_entries = logs_df.count()

# COMMAND ----------

logs_df.printSchema()

# COMMAND ----------

display(logs_df)

# COMMAND ----------

# MAGIC %md

# MAGIC Let's cache `logs_df`. We're going to be using it quite a bit from here forward.

# COMMAND ----------

logs_df.cache()

# COMMAND ----------

# MAGIC %md

# MAGIC ## Part 3: Analysis Walk-Through on the Web Server Log File

# MAGIC

# MAGIC Now that we have a DataFrame containing the parsed log file as a set of Row objects, we can perform various analyses.

# MAGIC

# MAGIC ### (3a) Example: Content Size Statistics

# MAGIC

# MAGIC Let's compute some statistics about the sizes of content being returned by the web server. In particular, we'd like to know what are the average, minimum, and maximum content sizes.

# MAGIC

# MAGIC We can compute the statistics by calling `.describe()` on the `content_size` column of `logs_df`. The `.describe()` function returns the count, mean, stddev, min, and max of a given column.

# COMMAND ----------

# Calculate statistics based on the content size.

content_size_summary_df = logs_df.describe(['content_size'])

content_size_summary_df.show()

# COMMAND ----------

# MAGIC %md

# MAGIC

# MAGIC Alternatively, we can use SQL to directly calculate these statistics. You can explore the many useful functions within the `pyspark.sql.functions` module in the [documentation](https://spark.apache.org/docs/latest/api/python/pyspark.sql.html#module-pyspark.sql.functions).

# MAGIC

# MAGIC After we apply the `.agg()` function, we call `.first()` to extract the first value, which is equivalent to `.take(1)[0]`.

# COMMAND ----------

from pyspark.sql import functions as sqlFunctions

content_size_stats = (logs_df

.first())

print 'Using SQL functions:'

print 'Content Size Avg: {1:,.2f}; Min: {0:.2f}; Max: {2:,.0f}'.format(*content_size_stats)

# COMMAND ----------

# MAGIC %md

# MAGIC ### (3b) Example: HTTP Status Analysis

# MAGIC

# MAGIC Next, let's look at the status values that appear in the log. We want to know which status values appear in the data and how many times. We again start with `logs_df`, then group by the `status` column, apply the `.count()` aggregation function, and sort by the `status` column.

# COMMAND ----------

status_to_count_df =(logs_df

.cache())

status_to_count_length = status_to_count_df.count()

print 'Found %d response codes' % status_to_count_length

status_to_count_df.show()

assert status_to_count_length == 7

assert status_to_count_df.take(100) == [(200, 940847), (302, 16244), (304, 79824), (403, 58), (404, 6185), (500, 2), (501, 17)]

# COMMAND ----------

# MAGIC %md

# MAGIC ### (3c) Example: Status Graphing

# MAGIC

# MAGIC Now, let's visualize the results from the last example. We can use the built-in `display()` function to show a bar chart of the count for each response code. After running this cell, select the bar graph option, and then use "Plot Options..." and drag `status` to the key entry field and drag `count` to the value entry field. See the diagram, below, for an example.

# MAGIC

# MAGIC <img src="http://spark-mooc.github.io/web-assets/images/cs105x/plot_options_1.png" style="float: right; margin-right: 30px; border: 1px solid #999999"/>

# COMMAND ----------

display(status_to_count_df)

# COMMAND ----------

# MAGIC %md

# MAGIC You can see that this is not a very effective plot. Due to the large number of '200' codes, it is very hard to see the relative number of the others. We can alleviate this by taking the logarithm of the count, adding that as a column to our DataFrame and displaying the result.

# COMMAND ----------

log_status_to_count_df = status_to_count_df.withColumn('log(count)', sqlFunctions.log(status_to_count_df['count']))

display(log_status_to_count_df)

# COMMAND ----------

# MAGIC %md

# MAGIC While this graph is an improvement, we might want to make more adjustments. The [`matplotlib` library](http://matplotlib.org/) can give us more control in our plot and is also useful outside the Databricks environment. In this case, we're essentially just reproducing the Databricks graph using `matplotlib`. However, `matplotlib` exposes far more controls than the Databricks graph, allowing you to change colors, label the axes, and more. We're using a set of helper functions from the [`spark_notebook_helpers`](https://pypi.python.org/pypi/spark_notebook_helpers/1.0.1) library.

# COMMAND ----------

# np is just an alias for numpy.

# cm and plt are aliases for matplotlib.cm (for "color map") and matplotlib.pyplot, respectively.

# prepareSubplot is a helper.

from spark_notebook_helpers import prepareSubplot, np, plt, cm

# COMMAND ----------

help(prepareSubplot)

# COMMAND ----------

# MAGIC %md

# MAGIC We're using the "Set1" color map. See the list of Qualitative Color Maps at <http://matplotlib.org/examples/color/colormaps_reference.html> for more details. Feel free to change the color map to a different one, like "Accent".

# COMMAND ----------

data = log_status_to_count_df.drop('count').collect()

x, y = zip(*data)

index = np.arange(len(x))

bar_width = 0.7

colorMap = 'Set1'

cmap = cm.get_cmap(colorMap)

fig, ax = prepareSubplot(np.arange(0, 6, 1), np.arange(0, 14, 2))

plt.bar(index, y, width=bar_width, color=cmap(0))

plt.xticks(index + bar_width/2.0, x)

display(fig)

# COMMAND ----------

# MAGIC %md

# MAGIC ### (3d) Example: Frequent Hosts

# MAGIC

# MAGIC Let's look at hosts that have accessed the server frequently (e.g., more than ten times). As with the response code analysis in (3b), we create a new DataFrame by grouping `successLogsDF` by the 'host' column and aggregating by count.

# MAGIC

# MAGIC We then filter the result based on the count of accesses by each host being greater than ten. Then, we select the 'host' column and show 20 elements from the result.

# COMMAND ----------

# Any hosts that has accessed the server more than 10 times.

host_sum_df =(logs_df

.groupBy('host')

.count())

host_more_than_10_df = (host_sum_df

.filter(host_sum_df['count'] > 10)

.select(host_sum_df['host']))

print 'Any 20 hosts that have accessed more then 10 times:\n'

host_more_than_10_df.show(truncate=False)

# COMMAND ----------

# MAGIC %md

# MAGIC ### (3e) Example: Visualizing Paths

# MAGIC

# MAGIC Now, let's visualize the number of hits to paths (URIs) in the log. To perform this task, we start with our `logs_df` and group by the `path` column, aggregate by count, and sort in descending order.

# MAGIC

# MAGIC Next we visualize the results using `matplotlib`. We previously imported the `prepareSubplot` function and the `matplotlib.pyplot` library, so we do not need to import them again. We extract the paths and the counts, and unpack the resulting list of `Rows` using a `map` function and `lambda` expression.

# COMMAND ----------

paths_df = (logs_df

.groupBy('path')

.count()

.sort('count', ascending=False))

paths_counts = (paths_df

.select('path', 'count')

.map(lambda r: (r[0], r[1]))

.collect())

paths, counts = zip(*paths_counts)

colorMap = 'Accent'

cmap = cm.get_cmap(colorMap)

index = np.arange(1000)

fig, ax = prepareSubplot(np.arange(0, 1000, 100), np.arange(0, 70000, 10000))

plt.xlabel('Paths')

plt.ylabel('Number of Hits')

plt.plot(index, counts[:1000], color=cmap(0), linewidth=3)

plt.axhline(linewidth=2, color='#999999')

display(fig)

# COMMAND ----------

# MAGIC %md

# MAGIC We can also visualize the results as a line graph using the built-in Databricks `display` function to graph the results. After calling this function on `paths_df`, select the line graph option.

# MAGIC

# MAGIC The graph is plotted using the first 1,000 rows of data. To see a more complete plot, click on the "Plot over all results" link. Be prepared to wait a minute or so.

# COMMAND ----------

display(paths_df)

# COMMAND ----------

# MAGIC %md

# MAGIC ### (3f) Example: Top Paths

# MAGIC

# MAGIC For the final example, we'll find the top paths (URIs) in the log. Because we sorted `paths_df` for plotting, all we need to do is call `.show()` and pass in `n=10` and `truncate=False` as the parameters to show the top ten paths without truncating.

# COMMAND ----------

# Top Paths

print 'Top Ten Paths:'

paths_df.show(n=10, truncate=False)

expected = [

]

assert paths_df.take(10) == expected, 'incorrect Top Ten Paths'

# COMMAND ----------

# MAGIC %md

# MAGIC ### Part 4: Analyzing Web Server Log File

# MAGIC

# MAGIC Now it is your turn to perform analyses on the web server log files.

# COMMAND ----------

# MAGIC %md

# MAGIC **(4a) Exercise: Top Ten Error Paths**

# MAGIC

# MAGIC What are the top ten paths which did not have return code 200? Create a sorted list containing the paths and the number of times that they were accessed with a non-200 return code and show the top ten.

# MAGIC

# MAGIC Think about the steps that you need to perform to determine which paths did not have a 200 return code, how you will uniquely count those paths and sort the list.

# COMMAND ----------

# TODO: Replace <FILL IN> with appropriate code

# You are welcome to structure your solution in a different way, so long as

# you ensure the variables used in the next Test section are defined

# DataFrame containing all accesses that did not return a code 200

from pyspark.sql.functions import desc

not200DF = logs_df.filter('status<>200')

not200DF.show(10)

# Sorted DataFrame containing all paths and the number of times they were accessed with non-200 return code

logs_sum_df = not200DF.groupBy('path').count().sort('count', ascending = False)

print 'Top Ten failed URLs:'

logs_sum_df.show(10, False)

# COMMAND ----------

# TEST Top ten error paths (4a)

top_10_err_urls = [(row[0], row[1]) for row in logs_sum_df.take(10)]

top_10_err_expected = [

]

Test.assertEquals(logs_sum_df.count(), 7675, 'incorrect count for logs_sum_df')

Test.assertEquals(top_10_err_urls, top_10_err_expected, 'incorrect Top Ten failed URLs')

# COMMAND ----------

# MAGIC %md

# MAGIC ### (4b) Exercise: Number of Unique Hosts

# MAGIC

# MAGIC How many unique hosts are there in the entire log?

# MAGIC

# MAGIC There are multiple ways to find this. Try to find a more optimal way than grouping by 'host'.

# COMMAND ----------

# TODO: Replace <FILL IN> with appropriate code

unique_host_count = logs_df.select('host').distinct().count()

print 'Unique hosts: {0}'.format(unique_host_count)

# COMMAND ----------

# TEST Number of unique hosts (4b)

Test.assertEquals(unique_host_count, 54507, 'incorrect unique_host_count')

# COMMAND ----------

# MAGIC %md

# MAGIC ### (4c) Exercise: Number of Unique Daily Hosts

# MAGIC

# MAGIC For an advanced exercise, let's determine the number of unique hosts in the entire log on a day-by-day basis. This computation will give us counts of the number of unique daily hosts. We'd like a DataFrame sorted by increasing day of the month which includes the day of the month and the associated number of unique hosts for that day. Make sure you cache the resulting DataFrame `daily_hosts_df` so that we can reuse it in the next exercise.

# MAGIC

# MAGIC Think about the steps that you need to perform to count the number of different hosts that make requests *each* day.

# MAGIC *Since the log only covers a single month, you can ignore the month.* You may want to use the [`dayofmonth` function](https://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.functions.dayofmonth) in the `pyspark.sql.functions` module.

# MAGIC

# MAGIC **Description of each variable**

# MAGIC

# MAGIC **`day_to_host_pair_df`**

# MAGIC

# MAGIC A DataFrame with two columns

# MAGIC

# MAGIC | column | explanation |

# MAGIC | ------ | -------------------- |

# MAGIC | `host` | the host name |

# MAGIC | `day` | the day of the month |

# MAGIC

# MAGIC There will be one row in this DataFrame for each row in `logs_df`. Essentially, you're just trimming and transforming each row of `logs_df`. For example, for this row in `logs_df`:

# MAGIC

# MAGIC ```

# MAGIC gw1.att.com - - [23/Aug/1995:00:03:53 -0400] "GET /shuttle/missions/sts-73/news HTTP/1.0" 302 -

# MAGIC ```

# MAGIC

# MAGIC your `day_to_host_pair_df` should have:

# MAGIC

# MAGIC ```

# MAGIC gw1.att.com 23

# MAGIC ```

# MAGIC

# MAGIC **`day_group_hosts_df`**

# MAGIC

# MAGIC This DataFrame has the same columns as `day_to_host_pair_df`, but with duplicate (`day`, `host`) rows removed.

# MAGIC

# MAGIC **`daily_hosts_df`**

# MAGIC

# MAGIC A DataFrame with two columns:

# MAGIC

# MAGIC | column | explanation |

# MAGIC | ------- | -------------------------------------------------- |

# MAGIC | `day` | the day of the month |

# MAGIC | `count` | the number of unique requesting hosts for that day |

# COMMAND ----------

# TODO: Replace <FILL IN> with appropriate code

from pyspark.sql.functions import dayofmonth

day_to_host_pair_df = logs_df.select(logs_df.host, dayofmonth(logs_df.time).alias('day'))

day_group_hosts_df = day_to_host_pair_df.distinct()

daily_hosts_df = day_group_hosts_df.groupBy('day').count()

daily_hosts_df.cache()

print 'Unique hosts per day:'

daily_hosts_df.show(30, False)

# COMMAND ----------

# TEST Number of unique daily hosts (4c)

daily_hosts_list = (daily_hosts_df

.map(lambda r: (r[0], r[1]))

.take(30))

Test.assertEquals(day_to_host_pair_df.count(), total_log_entries, 'incorrect row count for day_to_host_pair_df')

Test.assertEquals(daily_hosts_df.count(), 21, 'incorrect daily_hosts_df.count()')

Test.assertEquals(daily_hosts_list, [(1, 2582), (3, 3222), (4, 4190), (5, 2502), (6, 2537), (7, 4106), (8, 4406), (9, 4317), (10, 4523), (11, 4346), (12, 2864), (13, 2650), (14, 4454), (15, 4214), (16, 4340), (17, 4385), (18, 4168), (19, 2550), (20, 2560), (21, 4134), (22, 4456)], 'incorrect daily_hosts_df')

Test.assertTrue(daily_hosts_df.is_cached, 'incorrect daily_hosts_df.is_cached')

# COMMAND ----------

# MAGIC %md

# MAGIC ### (4d) Exercise: Visualizing the Number of Unique Daily Hosts

# MAGIC

# MAGIC Using the results from the previous exercise, we will use `matplotlib` to plot a line graph of the unique hosts requests by day. We need a list of days called `days_with_hosts` and a list of the number of unique hosts for each corresponding day called `hosts`.

# MAGIC

# MAGIC **WARNING**: Simply calling `collect()` on your transformed DataFrame won't work, because `collect()` returns a list of Spark SQL `Row` objects. You must _extract_ the appropriate column values from the `Row` objects. Hint: A loop will help.

# COMMAND ----------

# TODO: Your solution goes here

days_with_hosts = []

hosts = []

for day, count in daily_hosts_df.collect():

days_with_hosts.append(day)

hosts.append(count)

print(days_with_hosts)

print(hosts)

# COMMAND ----------

# TEST Visualizing unique daily hosts (4d)

test_days = range(1, 23)

test_days.remove(2)

Test.assertEquals(days_with_hosts, test_days, 'incorrect days')

Test.assertEquals(hosts, [2582, 3222, 4190, 2502, 2537, 4106, 4406, 4317, 4523, 4346, 2864, 2650, 4454, 4214, 4340, 4385, 4168, 2550, 2560, 4134, 4456], 'incorrect hosts')

# COMMAND ----------

fig, ax = prepareSubplot(np.arange(0, 30, 5), np.arange(0, 5000, 1000))

colorMap = 'Dark2'

cmap = cm.get_cmap(colorMap)

plt.plot(days_with_hosts, hosts, color=cmap(0), linewidth=3)

plt.axis([0, max(days_with_hosts), 0, max(hosts)+500])

plt.xlabel('Day')

plt.ylabel('Hosts')

plt.axhline(linewidth=3, color='#999999')

plt.axvline(linewidth=2, color='#999999')

display(fig)

# COMMAND ----------

# MAGIC %md

# MAGIC You can also pass in the `day_host_count_df` DataFrame into Databricks plots to plot a line or bar graph of the unique hosts requests by day.

# COMMAND ----------

display(daily_hosts_df)

# COMMAND ----------

# MAGIC %md

# MAGIC ### (4e) Exercise: Average Number of Daily Requests per Host

# MAGIC

# MAGIC Next, let's determine the average number of requests on a day-by-day basis. We'd like a list by increasing day of the month and the associated average number of requests per host for that day. Make sure you cache the resulting DataFrame `avg_daily_req_per_host_df` so that we can reuse it in the next exercise.

# MAGIC

# MAGIC To compute the average number of requests per host, find the total number of requests per day (across all hosts) and divide that by the number of unique hosts per day (which we found in part 4c and cached as `daily_hosts_df`).

# MAGIC

# MAGIC *Since the log only covers a single month, you can skip checking for the month.*

# COMMAND ----------

# TODO: Replace <FILL IN> with appropriate code

total_req_per_day_df = (

)

daily_hosts_df.registerTempTable("daily_hosts_df")

total_req_per_day_df.registerTempTable("total_req_per_day_df")

sql = 'SELECT t1.day AS day, t1.count / t2.count AS avg_reqs_per_host_per_day \

FROM total_req_per_day_df t1, daily_hosts_df t2 \

WHERE t1.day=t2.day'

avg_daily_req_per_host_df = sqlContext.sql(sql).cache()

print 'Average number of daily requests per Hosts is:\n'

avg_daily_req_per_host_df.show()

# COMMAND ----------

# TEST Average number of daily requests per hosts (4e)

avg_daily_req_per_host_list = (

avg_daily_req_per_host_df.select('day', avg_daily_req_per_host_df['avg_reqs_per_host_per_day'].cast('integer').alias('avg_requests'))

.collect()

)

values = [(row[0], row[1]) for row in avg_daily_req_per_host_list]

print values

Test.assertEquals(values, [(1, 13), (3, 12), (4, 14), (5, 12), (6, 12), (7, 13), (8, 13), (9, 14), (10, 13), (11, 14), (12, 13), (13, 13), (14, 13), (15, 13), (16, 13), (17, 13), (18, 13), (19, 12), (20, 12), (21, 13), (22, 12)], 'incorrect avgDailyReqPerHostDF')

Test.assertTrue(avg_daily_req_per_host_df.is_cached, 'incorrect avg_daily_req_per_host_df.is_cached')

# COMMAND ----------

# MAGIC %md

# MAGIC ### (4f) Exercise: Visualizing the Average Daily Requests per Unique Host

# MAGIC

# MAGIC Using the result `avg_daily_req_per_host_df` from the previous exercise, use `matplotlib` to plot a line graph of the average daily requests per unique host by day.

# MAGIC

# MAGIC `days_with_avg` should be a list of days and `avgs` should be a list of average daily requests (as integers) per unique hosts for each corresponding day. Hint: You will need to extract these from the Dataframe in a similar way to part 4d.

# COMMAND ----------

days_with_avg = []

avgs = []

for day, avg in avg_daily_req_per_host_df.collect():

days_with_avg.append(day)

avgs.append(avg)

# COMMAND ----------

# TEST Average Daily Requests per Unique Host (4f)

Test.assertEquals(days_with_avg, [1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22], 'incorrect days')

Test.assertEquals([int(a) for a in avgs], [13, 12, 14, 12, 12, 13, 13, 14, 13, 14, 13, 13, 13, 13, 13, 13, 13, 12, 12, 13, 12], 'incorrect avgs')

# COMMAND ----------

fig, ax = prepareSubplot(np.arange(0, 20, 5), np.arange(0, 16, 2))

colorMap = 'Set3'

cmap = cm.get_cmap(colorMap)

plt.plot(days_with_avg, avgs, color=cmap(0), linewidth=3)

plt.axis([0, max(days_with_avg), 0, max(avgs)+2])

plt.xlabel('Day')

plt.ylabel('Average')

plt.axhline(linewidth=3, color='#999999')

plt.axvline(linewidth=2, color='#999999')

display(fig)

# COMMAND ----------

# MAGIC %md

# MAGIC As a comparison to the prior plot, use the Databricks `display` function to plot a line graph of the average daily requests per unique host by day.

# COMMAND ----------

# TODO: Replace <FILL IN> with appropriate code

display(avg_daily_req_per_host_df)

# COMMAND ----------

# MAGIC %md

# MAGIC ## Part 5: Exploring 404 Status Codes

# MAGIC

# MAGIC Let's drill down and explore the error 404 status records. We've all seen those "404 Not Found" web pages. 404 errors are returned when the server cannot find the resource (page or object) the browser or client requested.

# COMMAND ----------

# MAGIC %md

# MAGIC ### (5a) Exercise: Counting 404 Response Codes

# MAGIC

# MAGIC Create a DataFrame containing only log records with a 404 status code. Make sure you `cache()` `not_found_df` as we will use it in the rest of this exercise.

# MAGIC

# MAGIC How many 404 records are in the log?

# COMMAND ----------

# TODO: Replace <FILL IN> with appropriate code

not_found_df = logs_df.filter('status=404')

not_found_df.cache()

print('Found {0} 404 URLs').format(not_found_df.count())

# COMMAND ----------

# TEST Counting 404 (5a)

Test.assertEquals(not_found_df.count(), 6185, 'incorrect not_found_df.count()')

Test.assertTrue(not_found_df.is_cached, 'incorrect not_found_df.is_cached')

# COMMAND ----------

# MAGIC %md

# MAGIC ### (5b) Exercise: Listing 404 Status Code Records

# MAGIC

# MAGIC Using the DataFrame containing only log records with a 404 status code that you cached in part (5a), print out a list up to 40 _distinct_ paths that generate 404 errors.

# MAGIC

# MAGIC **No path should appear more than once in your list.**

# COMMAND ----------

# TODO: Replace <FILL IN> with appropriate code

not_found_paths_df = not_found_df.select('path')

unique_not_found_paths_df = not_found_paths_df.distinct()

print '404 URLS:\n'

unique_not_found_paths_df.show(n=40, truncate=False)

# COMMAND ----------

# TEST Listing 404 records (5b)

bad_unique_paths_40 = set([row[0] for row in unique_not_found_paths_df.take(40)])

Test.assertEquals(len(bad_unique_paths_40), 40, 'bad_unique_paths_40 not distinct')

# COMMAND ----------

# MAGIC %md

# MAGIC ### (5c) Exercise: Listing the Top Twenty 404 Response Code paths

# MAGIC

# MAGIC Using the DataFrame containing only log records with a 404 response code that you cached in part (5a), print out a list of the top twenty paths that generate the most 404 errors.

# MAGIC

# MAGIC *Remember, top paths should be in sorted order*

# COMMAND ----------

# TODO: Replace <FILL IN> with appropriate code

top_20_not_found_df = (not_found_paths_df

.groupBy('path')

.count()

.sort(desc('count')))

print 'Top Twenty 404 URLs:\n'

top_20_not_found_df.show(n=20, truncate=False)

# COMMAND ----------

# TEST Top twenty 404 URLs (5c)

top_20_not_found = [(row[0], row[1]) for row in top_20_not_found_df.take(20)]

top_20_expected = [

]

Test.assertEquals(top_20_not_found, top_20_expected, 'incorrect top_20_not_found')

# COMMAND ----------

# MAGIC %md

# MAGIC ### (5d) Exercise: Listing the Top Twenty-five 404 Response Code Hosts

# MAGIC

# MAGIC Instead of looking at the paths that generated 404 errors, let's look at the hosts that encountered 404 errors. Using the DataFrame containing only log records with a 404 status codes that you cached in part (5a), print out a list of the top twenty-five hosts that generate the most 404 errors.

# COMMAND ----------

# TODO: Replace <FILL IN> with appropriate code

hosts_404_count_df = (not_found_df

.sort(desc('count')))

print 'Top 25 hosts that generated errors:\n'

hosts_404_count_df.show(n=25, truncate=False)

# COMMAND ----------

# TEST Top twenty-five 404 response code hosts (4d)

top_25_404 = [(row[0], row[1]) for row in hosts_404_count_df.take(25)]

Test.assertEquals(len(top_25_404), 25, 'length of errHostsTop25 is not 25')

expected = set([