02-adventures-in-getting-data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Finding other ways to get data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As it so happens..... pandas also has a function for importing Excel spreadsheets (i.e. we don't necessarily need the openpyxl if we aren't doing more sophisticated things with Excel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_excel('data/iris-excel-starter.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This still has the buggy data.\n",
    "\n",
    "Let's say that we have a couple friends who have already corrected this data for us."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Fisher"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fisher has already given us one source of data:\n",
    "\n",
    "<img src='data-sci-images/fisher-table-all.png' style='height:500px'>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Um....\n",
    "\n",
    "That requires us to convert an image with text into an array of data..... let's not do that (though there are libraries and applications for doing optical character recognition and importing images!)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Colleague #1\n",
    "\n",
    "One of our colleagues has given us a JSON file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('data/iris-v1.json') as f:\n",
    "    x = json.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "x"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Putting this into a DataFrame requires that we flatten the dictionary...."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_json('data/iris-v1.json')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Apparently Pandas by default wants us to do that too."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_json('data/iris-v1-Copy1.json')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could do more, but let's move on.  If you would like to work through some details, check this out:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_json('data/iris-v2.json')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But let's try another data set."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Colleague #2\n",
    "\n",
    "Another of our colleagues has given use a CSV."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('data/iris.csv') as f:\n",
    "    x = csv.reader(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('data/iris.csv') as f:\n",
    "    x = csv.reader(f)\n",
    "    for row in x:\n",
    "        print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "csvlists = []\n",
    "with open('data/iris.csv') as f:\n",
    "    x = csv.reader(f)\n",
    "    for row in x:\n",
    "        csvlists.append(row)\n",
    "dfcsv = pd.DataFrame(csvlists[1:],columns=csvlists[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfcsv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "...or ...."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_csv('data/iris.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Colleague #3\n",
    "\n",
    "Colleague #3 exported tab-separated values.\n",
    "\n",
    "(Look at 'data/iris-tab.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_csv('data/iris-tab.txt',delimiter='\\t')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Online resources\n",
    "\n",
    "Let's say we're uneasy about consulting our colleagues on this one.\n",
    "\n",
    "The UCI machine learning repository hosts this data.\n",
    "\n",
    "https://archive.ics.uci.edu/ml/datasets/iris"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In Python, you can execute shell commands.  For example, let's execute a command with wget."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!wget https://archive.ics.uci.edu/ml/machine-learning-databases/iris/iris.names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\"GNU Wget is a free software package for retrieving files using HTTP, HTTPS, FTP and FTPS, the most widely used Internet protocols. It is a non-interactive commandline tool, so it may easily be called from scripts, cron jobs, terminals without X-Windows support, etc.\"\n",
    "<br>-- https://www.gnu.org/software/wget/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above uses \"!\" to execute shell commands from this notebook.\n",
    "\n",
    "The way to run shell commands from a Python program:\n",
    "* use `os.system()`\n",
    "* use `os.popen()`\n",
    "* use the `subprocess` module"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "os.system('wget https://archive.ics.uci.edu/ml/machine-learning-databases/iris/iris.names')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Consulting that file -- it's a text file, and gives us good information, but maybe more than we want right now."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x = requests.get('https://archive.ics.uci.edu/ml/machine-learning-databases/iris/iris.data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(x.content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x.text.split('\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To make a DataFrame from a list, we need the list items (that is, the eventual rows) to be lists."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This can be obtained via list comprehension."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Starter examples of list comprehension\n",
    "[i for i in [1,2,3]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "[i for i in ['What,is,this','a,comma,full,sentence']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "[i.split(',') for i in ['What,is,this','a,comma,full,sentence']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "xarr = [i.split(',') for i in x.text.split('\\n')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Essentially this is a Pythonic way of saying:\n",
    "```\n",
    "make a new list\n",
    "whose elements are i.split(',')\n",
    "-- e.g. ['5.1','3.5','1.4','0.2','Iris-setosa'] from '5.1,3.5,1.4,0.2,Iris-setosa'\n",
    "for every element i from x.text.split('\\n') \n",
    "-- e.g. the list of strings above\n",
    "```    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame(xarr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame(xarr).info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We should still fix the data types."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas through the web"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lo and behold, you can even pass a website into Panda's read_csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "irisdf = pd.read_csv('https://raw.githubusercontent.com/jbrownlee/Datasets/master/iris.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For pd.read_csv, \"Any valid string path is acceptable. The string could be a URL. Valid URL schemes include http, ftp, s3, gs, and file.\"\n",
    "<br> --https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html\n",
    "\n",
    "This means you can import from AWS S3 buckets too! (though this further requires installing S3Fs and handling authentication variables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "irisdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = 'https://raw.githubusercontent.com/jbrownlee/Datasets/master/iris.csv'\n",
    "irisdf = pd.read_csv(path, header=None)\n",
    "irisdf.columns = ['sepalLength','sepalWidth','petalLength','petalWidth','species']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "irisdf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas has many IO capabilities:\n",
    "\n",
    "<img src='data-sci-images/pdio.png' width=700>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## There are many ways to grab data, and many places from which to grab it.\n",
    "\n",
    "## But now we will turn to visualizing our data"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "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.8.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}