6 - Data Processing in Shell.ipynb

{
 "cells": [
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "1 - Downloading Data on the Command Line"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Downloading single file using curl\n",
    "\n",
    "# Download and rename the file in the same step\n",
    "curl -o Spotify201812.zip -L https://assets.datacamp.com/production/repositories/4180/datasets/eb1d6a36fa3039e4e00064797e1a1600d267b135/201812SpotifyData.zip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Download all 100 data files\n",
    "curl -O https://s3.amazonaws.com/assets.datacamp.com/production/repositories/4180/datasets/files/datafile[001-100].txt\n",
    "\n",
    "# Print all downloaded files to directory\n",
    "ls datafile*.txt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# if problem man curl or curl --help"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Downloading single file using wget\n",
    "\n",
    "# Fill in the two option flags \n",
    "wget -c -b https://assets.datacamp.com/production/repositories/4180/datasets/eb1d6a36fa3039e4e00064797e1a1600d267b135/201812SpotifyData.zip\n",
    "\n",
    "# Verify that the Spotify file has been downloaded\n",
    "ls \n",
    "\n",
    "# Preview the log file \n",
    "cat wget-log"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Creating wait time using Wget\n",
    "\n",
    "# View url_list.txt to verify content\n",
    "cat url_list.txt\n",
    "\n",
    "# Create a mandatory 1 second pause between downloading all files in url_list.txt\n",
    "wget --wait=1 -i url_list.txt\n",
    "\n",
    "# Take a look at all files downloaded\n",
    "ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Data downloading with Wget and curl\n",
    "\n",
    "# Use curl, download and rename a single file from URL\n",
    "curl -o Spotify201812.zip -L https://assets.datacamp.com/production/repositories/4180/datasets/eb1d6a36fa3039e4e00064797e1a1600d267b135/201812SpotifyData.zip\n",
    "\n",
    "# Unzip, delete, then re-name to Spotify201812.csv\n",
    "unzip Spotify201812.zip && rm Spotify201812.zip\n",
    "mv 201812SpotifyData.csv Spotify201812.csv\n",
    "\n",
    "# View url_list.txt to verify content\n",
    "cat url_list.txt\n",
    "\n",
    "# Use Wget, limit the download rate to 2500KB/s, download all files in url_list.txt\n",
    "wget --limit-rate=2500k -i url_list.txt\n",
    "\n",
    "# Take a look at all files downloaded\n",
    "ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "2 - Data Cleaning and Munging on the Command Line"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Installation and documentation for csvkit\n",
    "\n",
    "# Upgrade csvkit using pip  \n",
    "pip install --upgrade csvkit\n",
    "\n",
    "# Print manual for in2csv\n",
    "in2csv -h\n",
    "\n",
    "# Print manual for csvlook\n",
    "csvlook -h"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Converting and previewing data with csvkit\n",
    "\n",
    "# Use ls to find the name of the zipped file\n",
    "ls\n",
    "\n",
    "# Use Linux's built in unzip tool to unpack the zipped file \n",
    "unzip SpotifyData.zip\n",
    "\n",
    "# Check to confirm name and location of unzipped file\n",
    "ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Converting and previewing data with csvkit\n",
    "\n",
    "# Use ls to find the name of the zipped file\n",
    "ls\n",
    "\n",
    "# Use Linux's built in unzip tool to unpack the zipped file \n",
    "unzip SpotifyData.zip\n",
    "\n",
    "# Check to confirm name and location of unzipped file\n",
    "ls\n",
    "\n",
    "# Convert SpotifyData.xlsx to csv\n",
    "in2csv SpotifyData.xlsx > SpotifyData.csv\n",
    "\n",
    "# Print a preview in console using a csvkit suite command \n",
    "csvlook SpotifyData.csv "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## File conversion and summary statistics with csvkit\n",
    "\n",
    "# Check to confirm name and location of the Excel data file\n",
    "ls\n",
    "\n",
    "# Convert sheet \"Worksheet1_Popularity\" to CSV\n",
    "in2csv SpotifyData.xlsx --sheet \"Worksheet1_Popularity\" > Spotify_Popularity.csv\n",
    "\n",
    "# Check to confirm name and location of the new CSV file\n",
    "ls\n",
    "\n",
    "# Print high level summary statistics for each column\n",
    "csvstat Spotify_Popularity.csv \n",
    "\n",
    "\n",
    "# Check to confirm name and location of the Excel data file\n",
    "ls\n",
    "\n",
    "# Convert sheet \"Worksheet2_MusicAttributes\" to CSV\n",
    "in2csv SpotifyData.xlsx --sheet \"Worksheet2_MusicAttributes\" > Spotify_MusicAttributes.csv\n",
    "\n",
    "# Check to confirm name and location of the new CSV file\n",
    "ls\n",
    "\n",
    "# Print preview of Spotify_MusicAttributes\n",
    "csvlook Spotify_MusicAttributes.csv\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Printing column headers with csvkit\n",
    "\n",
    "# Check to confirm name and location of data file\n",
    "ls\n",
    "\n",
    "# Print a list of column headers in data file \n",
    "csvcut -n Spotify_MusicAttributes.csv"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "If you have trouble remember the name of a specific csvkit command, visit the web-based documentation page for csvkit. If you remember the command but are not sure how to use it, use -h or --help to pull up the command specific help page."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Filtering data by column with csvkit\n",
    "\n",
    "# Print a list of column headers in the data \n",
    "csvcut -n Spotify_MusicAttributes.csv\n",
    "\n",
    "# Print the first column, by position\n",
    "csvcut -c 1 Spotify_MusicAttributes.csv\n",
    "\n",
    "\n",
    "# Print a list of column headers in the data \n",
    "csvcut -n Spotify_MusicAttributes.csv\n",
    "\n",
    "# Print the first, third, and fifth column, by position\n",
    "csvcut -c 1,3,5 Spotify_MusicAttributes.csv\n",
    "\n",
    "\n",
    "# Print a list of column headers in the data \n",
    "csvcut -n Spotify_MusicAttributes.csv\n",
    "\n",
    "# Print the first column, by name\n",
    "csvcut -c \"track_id\" Spotify_MusicAttributes.csv\n",
    "\n",
    "# Print a list of column headers in the data \n",
    "csvcut -n Spotify_MusicAttributes.csv\n",
    "\n",
    "# Print the track id, song duration, and loudness, by name \n",
    "csvcut -c \"track_id\",\"duration_ms\",\"loudness\" Spotify_MusicAttributes.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Filtering data by row with csvkit\n",
    "\n",
    "# Print a list of column headers in the data \n",
    "csvcut -n Spotify_MusicAttributes.csv\n",
    "\n",
    "# Filter for row(s) where track_id = 118GQ70Sp6pMqn6w1oKuki\n",
    "csvgrep -c \"track_id\" -m 118GQ70Sp6pMqn6w1oKuki Spotify_MusicAttributes.csv\n",
    "\n",
    "\n",
    "# Print a list of column headers in the data \n",
    "csvcut -n Spotify_MusicAttributes.csv\n",
    "\n",
    "# Filter for row(s) where danceability = 0.812\n",
    "csvgrep -c \"danceability\" -m 0.812 Spotify_MusicAttributes.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Stacking files with csvkit\n",
    "\n",
    "# Stack the two files and save results as a new file\n",
    "csvstack SpotifyData_PopularityRank6.csv SpotifyData_PopularityRank7.csv > SpotifyPopularity.csv\n",
    "\n",
    "# Preview the newly created file \n",
    "csvlook SpotifyPopularity.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Chaining commands using operators\n",
    "\n",
    "# If csvlook succeeds, then run csvstat \n",
    "csvlook Spotify_Popularity.csv && csvstat Spotify_Popularity.csv\n",
    "\n",
    "\n",
    "# Use the output of csvsort as input to csvlook\n",
    "csvsort -c 2 Spotify_Popularity.csv | csvlook\n",
    "\n",
    "\n",
    "# Take top 15 rows from sorted output and save to new file\n",
    "csvsort -c 2 Spotify_Popularity.csv | head -n 15 > Spotify_Popularity_Top15.csv\n",
    "\n",
    "# Preview the new file \n",
    "csvlook Spotify_Popularity_Top15.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Data processing with csvkit\n",
    "\n",
    "# Convert the Spotify201809 tab into its own csv file \n",
    "in2csv Spotify_201809_201810.xlsx --sheet \"Spotify201809\" > Spotify201809.csv\n",
    "\n",
    "# Check to confirm name and location of data file\n",
    "ls\n",
    "\n",
    "# Preview file preview using a csvkit function\n",
    "csvlook Spotify201809.csv\n",
    "\n",
    "# Create a new csv with 2 columns: track_id and popularity\n",
    "csvcut -c \"track_id\",\"popularity\" Spotify201809.csv > Spotify201809_subset.csv\n",
    "\n",
    "# While stacking the 2 files, create a data source column\n",
    "csvstack -g \"Sep2018\",\"Oct2018\" Spotify201809_subset.csv Spotify201810_subset.csv > Spotify_all_rankings.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "3 - Database Operations on the Command Line"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Practice pulling data from database\n",
    "\n",
    "# Verify database name \n",
    "ls\n",
    "\n",
    "# Save query to new file Spotify_MusicAttributes_5Rows.csv\n",
    "sql2csv --db \"sqlite:///SpotifyDatabase.db\" \\\n",
    "        --query \"SELECT * FROM Spotify_Popularity LIMIT 5\" \\\n",
    "        > Spotify_Popularity_5Rows.csv\n",
    "\n",
    "# Verify newly created file\n",
    "ls\n",
    "\n",
    "# Print preview of newly created file\n",
    "csvlook Spotify_Popularity_5Rows.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Applying SQL to a local CSV file\n",
    "\n",
    "# Reformat the output using csvlook \n",
    "csvsql --query \"SELECT * FROM Spotify_MusicAttributes ORDER BY duration_ms LIMIT 1\" \\\n",
    "\tSpotify_MusicAttributes.csv | csvlook\n",
    "\n",
    "\n",
    "# Re-direct output to new file: LongestSong.csv\n",
    "csvsql --query \"SELECT * FROM Spotify_MusicAttributes ORDER BY duration_ms LIMIT 1\" \\\n",
    "\tSpotify_MusicAttributes.csv > LongestSong.csv\n",
    "    \n",
    "# Preview newly created file \n",
    "csvlook LongestSong.csv\n",
    "\n",
    "\n",
    "# Re-direct output to new file: LongestSong.csv\n",
    "csvsql --query \"SELECT * FROM Spotify_MusicAttributes ORDER BY duration_ms LIMIT 1\" \\\n",
    "\tSpotify_MusicAttributes.csv > LongestSong.csv | csvlook\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Cleaner scripting via shell variables\n",
    "\n",
    "# Preview CSV file\n",
    "ls\n",
    "\n",
    "# Store SQL query as shell variable\n",
    "sqlquery=\"SELECT * FROM Spotify_MusicAttributes ORDER BY duration_ms LIMIT 1\"\n",
    "\n",
    "# Apply SQL query to Spotify_MusicAttributes.csv\n",
    "csvsql --query \"$sqlquery\" Spotify_MusicAttributes.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Joining local CSV files using SQL\n",
    "\n",
    "# Store SQL query as shell variable\n",
    "sql_query=\"SELECT ma.*, p.popularity FROM Spotify_MusicAttributes ma INNER JOIN Spotify_Popularity p ON ma.track_id = p.track_id\"\n",
    "\n",
    "# Join 2 local csvs into a new csv using the saved SQL\n",
    "csvsql --query \"$sql_query\" Spotify_MusicAttributes.csv Spotify_Popularity.csv > Spotify_FullData.csv\n",
    "\n",
    "# Preview newly created file\n",
    "csvstat Spotify_FullData.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Practice pushing data back to database\n",
    "\n",
    "# Preview file\n",
    "ls\n",
    "\n",
    "# Upload Spotify_MusicAttributes.csv to database\n",
    "csvsql --db \"sqlite:///SpotifyDatabase.db\" --insert Spotify_MusicAttributes.csv\n",
    "\n",
    "# Store SQL query as shell variable\n",
    "sqlquery=\"SELECT * FROM Spotify_MusicAttributes\"\n",
    "\n",
    "# Apply SQL query to re-pull new table in database\n",
    "sql2csv --db \"sqlite:///SpotifyDatabase.db\" --query \"$sqlquery\" "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Database and SQL with csvkit\n",
    "\n",
    "# Store SQL for querying from SQLite database \n",
    "sqlquery_pull=\"SELECT * FROM SpotifyMostRecentData\"\n",
    "\n",
    "# Apply SQL to save table as local file \n",
    "sql2csv --db \"sqlite:///SpotifyDatabase.db\" --query \"$sqlquery_pull\" > SpotifyMostRecentData.csv\n",
    "\n",
    "# Store SQL for UNION of the two local CSV files\n",
    "sqlquery_union=\"SELECT * FROM SpotifyMostRecentData UNION ALL SELECT * FROM Spotify201812\"\n",
    "\n",
    "# Apply SQL to union the two local CSV files and save as local file\n",
    "csvsql \t--query \"$sqlquery_union\" SpotifyMostRecentData.csv Spotify201812.csv > UnionedSpotifyData.csv\n",
    "\n",
    "# Push UnionedSpotifyData.csv to database as a new table\n",
    "csvsql --db \"sqlite:///SpotifyDatabase.db\" --insert UnionedSpotifyData.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "4 - Data Pipeline on the Command Line"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Executing Python script on the command line\n",
    "\n",
    "# in one step, create a new file and pass the print function into the file\n",
    "echo \"print('This is my first Python script')\" > my_first_python_script.py\n",
    "\n",
    "# check file location \n",
    "ls\n",
    "\n",
    "# check file content \n",
    "cat my_first_python_script.py\n",
    "\n",
    "# execute Python script file directly from command line  \n",
    "python my_first_python_script.py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Installing Python dependencies\n",
    "\n",
    "# Add scikit-learn to the requirements.txt file\n",
    "echo \"scikit-learn\" > requirements.txt\n",
    "\n",
    "# Preview file content\n",
    "cat requirements.txt\n",
    "\n",
    "# Install the required dependencies\n",
    "pip install -r requirements.txt\n",
    "\n",
    "# Verify that Scikit-Learn is now installed\n",
    "pip list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Running a Python model\n",
    "\n",
    "# Re-install requirements\n",
    "pip install -r requirements.txt\n",
    "\n",
    "# Preview Python model script for import dependencies\n",
    "cat create_model.py\n",
    "\n",
    "# Verify that dependencies are installed\n",
    "pip list\n",
    "\n",
    "# Execute Python model script, which outputs a pkl file\n",
    "python create_model.py\n",
    "\n",
    "# Verify that the model.pkl file has been created \n",
    "ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Scheduling a job with crontab\n",
    "\n",
    "# Verify that there are no CRON jobs currently scheduled\n",
    "crontab -l \n",
    "\n",
    "# Create Python file hello_world.py\n",
    "echo \"print('hello world')\" > hello_world.py\n",
    "\n",
    "# Preview Python file \n",
    "cat hello_world.py\n",
    "\n",
    "# Add as job that runs every minute on the minute to crontab\n",
    "echo \"* * * * * python hello_world.py\" | crontab\n",
    "\n",
    "# Verify that the CRON job has been added\n",
    "crontab -l"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Model production on the command line\n",
    "\n",
    "# Preview both Python script and requirements text file\n",
    "cat create_model.py\n",
    "cat requirements.txt\n",
    "\n",
    "# Pip install Python dependencies in requirements file\n",
    "pip install -r requirements.txt\n",
    "\n",
    "# Run Python script on command line\n",
    "python create_model.py\n",
    "\n",
    "# Add CRON job that runs create_model.py every minute\n",
    "echo \"* * * * * python create_model.py\" | crontab\n",
    "\n",
    "# Verify that the CRON job has been scheduled via CRONTAB\n",
    "crontab -l"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "tf-gpu-2.0",
   "language": "python",
   "name": "tf_gpu2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}