code/codegraf/009/009c.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Summarizing and reorganizing DataFrames\n",
    "\n",
    "Load the state CO<sub>2</sub> data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://github.com/HeardLibrary/digital-scholarship/raw/master/data/codegraf/co2_data.xlsx'\n",
    "state_co2 = pd.read_excel(url)\n",
    "state_co2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grouping and summarizing data\n",
    "\n",
    "Grouping data allows us to pull out particular group members and summarize data by group."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "co2_state_grouped = state_co2.groupby(['State'])\n",
    "co2_state_grouped.get_group(('Texas'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate sum of all sectors by state\n",
    "co2_state_grouped.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "co2_sector_grouped = state_co2.groupby(['Sector'])\n",
    "co2_sector_grouped.get_group(('Industrial')).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate sum of all states by sector\n",
    "total_co2_sector = co2_sector_grouped.sum()\n",
    "total_co2_sector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Transpose, then sum\n",
    "print(total_co2_sector.T)\n",
    "total_co2_sector.T.sum()\n",
    "# Notice that since the sum is one-dimensional, the output is a series, not a DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate average instead of sum\n",
    "total_co2_sector.T.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Changing the DataFrame organization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "state_co2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "double_label = state_co2.copy().set_index(['Sector', 'State'])\n",
    "double_label.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "year_total = double_label.sum(axis='columns')\n",
    "year_total.head(12)\n",
    "# The data are one-dimensional, so the result is a series with two labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "column_df = year_total.unstack('Sector')\n",
    "column_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Changing between wide and long formats\n",
    "\n",
    "Data organized as above is said to be in \"wide\" format. Wide tables are often easier to edit and review. But many statistical analyses and visualizations require the data to be in \"long\" (or \"tall\") format. Data in long format are also part of making data \"tidy\" (see [this page](https://cran.r-project.org/web/packages/tidyr/vignettes/tidy-data.html) for more). \n",
    "\n",
    "Pandas has built-in functions and methods to change transform DataFrames between \"long\" and \"wide\" forms.\n",
    "\n",
    "The `pd.melt()` function can be used to trandform wide data into long. Data to be used as *grouping variables* (a.k.a. *factors* or \"group indicators\") must be in columns rather than index labels."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change State from an index label to a regular column.\n",
    "wide = column_df.reset_index('State')\n",
    "wide.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The arguments of the `pd.melt()` function are the DataFrame name followed by a list of columns to be used as grouping variables. Remaining columns are data values for the combinations of grouping variables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "long = pd.melt(wide, ['State'])\n",
    "long"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To transform a long DataFrame to a wide one, we can use the `.pivot()` method. The first two arguments passed into the method are the row and column indices. The third argument is the column to be used as the data values.\n",
    "\n",
    "Any long table can be turned wide in two ways."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "state_wide = long.pivot('State', 'Sector', 'value')\n",
    "state_wide.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sector_wide = long.pivot('Sector', 'State', 'value')\n",
    "sector_wide.head()"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}