06extra1-reading-tabular-data-into-dataframes.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Untitled0.ipynb",
      "provenance": [],
      "authorship_tag": "ABX9TyMtNUljdgxwD6MlQaqdLB2Q",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/brainhack-uiowa/python-novice-inflammation-colab/blob/master/06extra1-reading-tabular-data-into-dataframes.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iCrT57AqpVBD",
        "colab_type": "code",
        "outputId": "1798d03a-e0d6-4fb3-e4f8-65d0481f9392",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 330
        }
      },
      "source": [
        "# setup\n",
        "!wget http://swcarpentry.github.io/python-novice-gapminder/files/python-novice-gapminder-data.zip\n",
        "!unzip -n python-novice-gapminder-data.zip"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "--2020-06-14 02:25:53--  http://swcarpentry.github.io/python-novice-gapminder/files/python-novice-gapminder-data.zip\n",
            "Resolving swcarpentry.github.io (swcarpentry.github.io)... 185.199.111.153, 185.199.108.153, 185.199.109.153, ...\n",
            "Connecting to swcarpentry.github.io (swcarpentry.github.io)|185.199.111.153|:80... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: 38471 (38K) [application/zip]\n",
            "Saving to: ‘python-novice-gapminder-data.zip’\n",
            "\n",
            "python-novice-gapmi 100%[===================>]  37.57K  --.-KB/s    in 0.01s   \n",
            "\n",
            "2020-06-14 02:25:54 (2.55 MB/s) - ‘python-novice-gapminder-data.zip’ saved [38471/38471]\n",
            "\n",
            "Archive:  python-novice-gapminder-data.zip\n",
            "  inflating: data/gapminder_all.csv  \n",
            "  inflating: data/gapminder_gdp_africa.csv  \n",
            "  inflating: data/gapminder_gdp_americas.csv  \n",
            "  inflating: data/gapminder_gdp_asia.csv  \n",
            "  inflating: data/gapminder_gdp_europe.csv  \n",
            "  inflating: data/gapminder_gdp_oceania.csv  \n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZjC8_aTLqD85",
        "colab_type": "text"
      },
      "source": [
        "Original lesson and solutions can be found at [Software Carpentry](https://swcarpentry.github.io/python-novice-gapminder/07-reading-tabular/index.html)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3EXt17mwo0Uw",
        "colab_type": "text"
      },
      "source": [
        "#Reading Tabular Data into DataFrames"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "V4wIxZr4o3cp",
        "colab_type": "text"
      },
      "source": [
        "## Use the Pandas library to do statistics on tabular data.\n",
        "\n",
        "\n",
        "- Pandas is a widely-used Python library for statistics, particularly on tabular data.\n",
        "- Borrows many features from R’s dataframes.\n",
        "  - A 2-dimensional table whose columns have names and potentially have different data types.\n",
        "- Load it with `import pandas as pd`. The alias pd is commonly used for Pandas.\n",
        "- Read a Comma Separate Values (CSV) data file with `pd.read_csv`.\n",
        "  - Argument is the name of the file to be read.\n",
        "  - Assign result to a variable to store the data that was read.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xFwM3UQrodQn",
        "colab_type": "code",
        "outputId": "9a8c9fe4-049b-4857-da2e-2d72f1db659d",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 104
        }
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "data = pd.read_csv('data/gapminder_gdp_oceania.csv')\n",
        "print(data)\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "       country  gdpPercap_1952  ...  gdpPercap_2002  gdpPercap_2007\n",
            "0    Australia     10039.59564  ...     30687.75473     34435.36744\n",
            "1  New Zealand     10556.57566  ...     23189.80135     25185.00911\n",
            "\n",
            "[2 rows x 13 columns]\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ansJlFH0pkMt",
        "colab_type": "text"
      },
      "source": [
        "\n",
        "- The columns in a dataframe are the observed variables, and the rows are the observations.\n",
        "- Pandas uses backslash \\ to show wrapped lines when output is too wide to fit the screen.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dNNa9Ec6pp8j",
        "colab_type": "text"
      },
      "source": [
        "### File Not Found\n",
        "Our lessons store their `data` files in a data sub-directory, which is why the path to the file is `data/gapminder_gdp_oceania.csv`. If you forget to include `data/`, or if you include it but your copy of the file is somewhere else, you will get a runtime error that ends with a line like this:\n",
        "\n",
        "```\n",
        "OSError: File b'gapminder_gdp_oceania.csv' does not exist\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "O4wpKLKBqQYa",
        "colab_type": "text"
      },
      "source": [
        "##Use `index_col` to specify that a column’s values should be used as row headings.\n",
        "\n",
        "- Row headings are numbers (0 and 1 in this case).\n",
        "- Really want to index by country.\n",
        "- Pass the name of the column to `read_csv` as its `index_col` parameter to do this.\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qytSTZ4iqdbH",
        "colab_type": "code",
        "outputId": "eb61846a-357e-4150-e3d5-5fcc689c2836",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 121
        }
      },
      "source": [
        "data = pd.read_csv('data/gapminder_gdp_oceania.csv', index_col='country')\n",
        "print(data)\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "             gdpPercap_1952  gdpPercap_1957  ...  gdpPercap_2002  gdpPercap_2007\n",
            "country                                      ...                                \n",
            "Australia       10039.59564     10949.64959  ...     30687.75473     34435.36744\n",
            "New Zealand     10556.57566     12247.39532  ...     23189.80135     25185.00911\n",
            "\n",
            "[2 rows x 12 columns]\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AEjLARKhq6v1",
        "colab_type": "text"
      },
      "source": [
        "## Use `DataFrame.info` to find out more about a dataframe."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7Y8abZ9PrALC",
        "colab_type": "code",
        "outputId": "2ded8d25-c246-48c7-c9c5-08f1cbbb833d",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 347
        }
      },
      "source": [
        "data.info()\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Index: 2 entries, Australia to New Zealand\n",
            "Data columns (total 12 columns):\n",
            " #   Column          Non-Null Count  Dtype  \n",
            "---  ------          --------------  -----  \n",
            " 0   gdpPercap_1952  2 non-null      float64\n",
            " 1   gdpPercap_1957  2 non-null      float64\n",
            " 2   gdpPercap_1962  2 non-null      float64\n",
            " 3   gdpPercap_1967  2 non-null      float64\n",
            " 4   gdpPercap_1972  2 non-null      float64\n",
            " 5   gdpPercap_1977  2 non-null      float64\n",
            " 6   gdpPercap_1982  2 non-null      float64\n",
            " 7   gdpPercap_1987  2 non-null      float64\n",
            " 8   gdpPercap_1992  2 non-null      float64\n",
            " 9   gdpPercap_1997  2 non-null      float64\n",
            " 10  gdpPercap_2002  2 non-null      float64\n",
            " 11  gdpPercap_2007  2 non-null      float64\n",
            "dtypes: float64(12)\n",
            "memory usage: 208.0+ bytes\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gT26G3ZErGJZ",
        "colab_type": "text"
      },
      "source": [
        "\n",
        "- This is a DataFrame\n",
        "- Two rows named 'Australia' and 'New Zealand'\n",
        "- Twelve columns, each of which has two actual 64-bit floating point values.\n",
        "  - We will talk later about null values, which are used to represent missing observations.\n",
        "- Uses 208 bytes of memory.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "K5ZfRwMQrOHS",
        "colab_type": "text"
      },
      "source": [
        "## The `DataFrame.columns` variable stores information about the dataframe’s columns.\n",
        "\n",
        "- Note that this is data, not a method.\n",
        "  - Like `math.pi`.\n",
        "  - So do not use `()` to try to call it.\n",
        "- Called a *member variable*, or just *member*.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TNAN2u2irj9d",
        "colab_type": "code",
        "outputId": "29046119-5c21-4907-f2c3-48ce7c660aba",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 86
        }
      },
      "source": [
        "print(data.columns)\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Index(['gdpPercap_1952', 'gdpPercap_1957', 'gdpPercap_1962', 'gdpPercap_1967',\n",
            "       'gdpPercap_1972', 'gdpPercap_1977', 'gdpPercap_1982', 'gdpPercap_1987',\n",
            "       'gdpPercap_1992', 'gdpPercap_1997', 'gdpPercap_2002', 'gdpPercap_2007'],\n",
            "      dtype='object')\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EBiEm9ZnrndB",
        "colab_type": "text"
      },
      "source": [
        "## Use `DataFrame.T` to transpose a dataframe.\n",
        "\n",
        "- Sometimes want to treat columns as rows and vice versa.\n",
        "- Transpose (written `.T`) doesn’t copy the data, just changes the program’s view of it.\n",
        "- Like `columns`, it is a member variable.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "143fHYNBryNP",
        "colab_type": "code",
        "outputId": "97f4eb07-83f5-4c78-adea-b77c6bf425af",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 243
        }
      },
      "source": [
        "print(data.T)\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "country           Australia  New Zealand\n",
            "gdpPercap_1952  10039.59564  10556.57566\n",
            "gdpPercap_1957  10949.64959  12247.39532\n",
            "gdpPercap_1962  12217.22686  13175.67800\n",
            "gdpPercap_1967  14526.12465  14463.91893\n",
            "gdpPercap_1972  16788.62948  16046.03728\n",
            "gdpPercap_1977  18334.19751  16233.71770\n",
            "gdpPercap_1982  19477.00928  17632.41040\n",
            "gdpPercap_1987  21888.88903  19007.19129\n",
            "gdpPercap_1992  23424.76683  18363.32494\n",
            "gdpPercap_1997  26997.93657  21050.41377\n",
            "gdpPercap_2002  30687.75473  23189.80135\n",
            "gdpPercap_2007  34435.36744  25185.00911\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eZy4IQN9sCaz",
        "colab_type": "text"
      },
      "source": [
        "##Use `DataFrame.describe` to get summary statistics about data.\n",
        "\n",
        "`DataFrame.describe()` gets the summary statistics of only the columns that have numerical data. All other columns are ignored, unless you use the argument `include='all'`.\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "s5NUPFN5sJQG",
        "colab_type": "code",
        "outputId": "254f4cae-4d9e-4f63-8470-70b8396ff87a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 208
        }
      },
      "source": [
        "print(data.describe())\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "       gdpPercap_1952  gdpPercap_1957  ...  gdpPercap_2002  gdpPercap_2007\n",
            "count        2.000000        2.000000  ...        2.000000        2.000000\n",
            "mean     10298.085650    11598.522455  ...    26938.778040    29810.188275\n",
            "std        365.560078      917.644806  ...     5301.853680     6540.991104\n",
            "min      10039.595640    10949.649590  ...    23189.801350    25185.009110\n",
            "25%      10168.840645    11274.086022  ...    25064.289695    27497.598692\n",
            "50%      10298.085650    11598.522455  ...    26938.778040    29810.188275\n",
            "75%      10427.330655    11922.958888  ...    28813.266385    32122.777857\n",
            "max      10556.575660    12247.395320  ...    30687.754730    34435.367440\n",
            "\n",
            "[8 rows x 12 columns]\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "snOG1gqFsPjF",
        "colab_type": "text"
      },
      "source": [
        "- Not particularly useful with just two records, but very helpful when there are thousands."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pgSANhTssTvB",
        "colab_type": "text"
      },
      "source": [
        "###Exercise: Reading Other Data\n",
        "Read the data in `gapminder_gdp_americas.csv` (which should be in the same directory as `gapminder_gdp_oceania.csv`) into a variable called `americas` and display its summary statistics."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LAYfSyVPsdCk",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gZs14MCMsdm9",
        "colab_type": "text"
      },
      "source": [
        "###Exercise: Inspecting Data.\n",
        "After reading the data for the Americas, use `help(americas.head)` and `help(americas.tail)` to find out what `DataFrame.head` and `DataFrame.tail` do.\n",
        "\n",
        "1. What method call will display the first three rows of this data?\n",
        "2. What method call will display the last three columns of this data? (Hint: you may need to change your view of the data.)\n",
        "\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qvydcX2qsvik",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bILsxImMswOd",
        "colab_type": "text"
      },
      "source": [
        "###Exercise: Reading Files in Other Directories\n",
        "The data for your current project is stored in a file called `microbes.csv`, which is located in a folder called `field_data`. You are doing analysis in a notebook called `analysis.ipynb` in a sibling folder called `thesis`:\n",
        "```\n",
        "your_home_directory\n",
        "+-- field_data/\n",
        "|   +-- microbes.csv\n",
        "+-- thesis/\n",
        "    +-- analysis.ipynb\n",
        "```\n",
        "What value(s) should you pass to read_csv to read microbes.csv in analysis.ipynb?"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TXMUNjTHtoHO",
        "colab_type": "text"
      },
      "source": [
        "####Solution (Brainhack provided b/c prerequisite background not covered):\n",
        "We need to specify the path to the file of interest in the call to `pd.read_csv`. We first need to ‘jump’ out of the folder `thesis` using ‘../’ and then into the folder `field_data using ‘field_data/’. Then we can specify the filename `microbes.csv`. The result is as follows:\n",
        "\n",
        "```\n",
        "data_microbes = pd.read_csv('../field_data/microbes.csv')\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zebPhYbnuJSz",
        "colab_type": "text"
      },
      "source": [
        "### Exercise: Writing Data\n",
        "As well as the `read_csv` function for reading data from a file, Pandas provides a `to_csv` function to write dataframes to files. Applying what you’ve learned about reading from files, write one of your dataframes to a file called `processed.csv`. You can use `help` to get information on how to use `to_csv`."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FzeILXX2upOj",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WyfvXhsWuUKT",
        "colab_type": "text"
      },
      "source": [
        "## Key Points\n",
        "\n",
        "\n",
        "- Use the Pandas library to get basic statistics out of tabular data.\n",
        "- Use `index_col` to specify that a column’s values should be used as row headings.\n",
        "- Use `DataFrame.info` to find out more about a dataframe.\n",
        "- The `DataFrame.columns` variable stores information about the dataframe’s columns.\n",
        "- Use `DataFrame.T` to transpose a dataframe.\n",
        "- Use `DataFrame.describe` to get summary statistics about data.\n"
      ]
    }
  ]
}