# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Portable Hadoop Environment - Exercise 4
#
# Welcome, if you're reading this then you've successfully opened the Jupyter Notebook for Exercise 4.  We will now cover several tasks to help you analyze Hive data using Python from within this notebook.  
#
# The first step is importing the necessary libraries.
#
# In the following two cells you can see that we've installed the matplotlib and pyhive libraries by using !pip.  This executes pip in on the client node and installs modules automatically.  Then we've imported those modules.

# For any missing modules, you can use pip from within a cell as seen here:
# !pip install matplotlib
# !pip install pyhive

# +
# Pandas is a python data handling library
# Matplot lib is used for plotting charts and graphs
# Pyhive is the library used for accessing Hive

import pandas as pd
import matplotlib.pyplot as plt
from pyhive import hive
# -

# Now that the modules have been imported successfully we can get to work on the data!
#
# We're going to reuse the data loaded in Exercise 2.  The next cell shows how to connect to hive and execute a query which populates a Pandas dataframe for further analysis.

# +
# In the Portable Hadoop Environment, you only need the host and port specified for the connection
conn = hive.Connection(host='hive-server',port='10000')

# Panda's read_sql method makes querying hive incredibly simple)
df = pd.read_sql(con=conn, sql='SELECT * FROM truckingco.trucks t JOIN truckingco.drivers d on t.location = d.location')

# Now we can take a look at the first 5 records
df.head(5)
# -

# This is great.  Now that we've created the dataframe we can start aggregating and plotting graphs.
#
# Lets start by doing just a little bit of data cleanup. We'll do this by creating a new dataframe but only selecting a subset of columns from the previous one.

data = df[["t.location","d.firstname","d.lastname","d.startdate","d.rate"]]
data.head(5)

# Okay now with that data, let's start by doing a quick analysis on the "d.rate" column, or the payrate for the drivers.

data.agg(
{
    "d.rate": ["min", "max", "median", "mean"]
})

# That's some good information to report on.  Now lets do some work with dataframes to get the counts for each driver's routes driven.

# +
# Concatenating to create a column that contains the drivers full name
data["d.fullname"] = data["d.firstname"] + " " + data["d.lastname"]

# Setting the index of the df to the newly created column
data.set_index(["d.fullname"])

# Using value_counts() to get the counts for each driver and creating a new dataframe to hold that result
driver_counts = data["d.fullname"].value_counts()

driver_counts
# -

# Now let's plot a graph for the number of times driven by each driver.

# +
plt.plot(driver_counts)

plt.xlabel('Driver Name')
plt.ylabel('# Driven')

plt.title('Count of routes driven by driver')

plt.show()
# -

# As you can see using Python and Jupyter notes is a great way to disect and analyze data from Hive.  We've only begun to scratch the surface with this example.  Feel free to play around further with this notebook or try creating your own.  Once you're finished please proceed to Challenge 2!

StarcoderdataJupyterScriptsDedupFiltered