ExcelSummaryStatistics.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Summary Statistics #\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Our Data## \n",
    "http://wdi.worldbank.org/tables\n",
    "We will be using datasets from the World Bank's World Development Indicators. The indicators collected relate to the people, environmnet, economy, and a whole lot more. The historical reach of these datasets vary per indicator, but there is data here reaching from 1960-2018. **In these examples I'll be pulling various indicators, and it'll be your task to find different indicators within this dataset (i.e. different files) to complete the assignments where indicated. *All tasks should be completed in Excel unless indicated otherwise.*** \n",
    "\n",
    "### What is a Jupyter Notebook ###\n",
    "Jupyter Notebooks are a way to interweave both code snippets, output, Markdown text, images, etc.. All of this can be interacted with via Browser (as you're doing now), and shared via standard platforms like GitHub. I'll be using them for this module as they allow me to insert functioning Python code directly into this file directly with Excel examples. If you are unfamiliar with Python right now, don't worry. You can focus on the Excel examples for now, and use this as a reference point as you begin Python later this semester. \n",
    "\n",
    "### An aside, for Excel ### \n",
    "The data I'll be using for this example is the 3.5 Freshwater indicator, stemming from the Environment tab. The data can be downloaded as a PDF or an Excel file. PDFs are messy and can be a pain to interact with, and Excel files are custom built to handle data in spreadsheets, so the Excel file is an easy choice. If you see a warning for mismatched file types, ignore it and click Yes to open the file. There appears to be some missing parameters at the end of the file which cause the error. \n",
    "\n",
    "Opening the file in Excel, it's clear that the curators of this dataset had Excel users in mind as their target users. There are merged cells running along the top, a data dictionary directly in the file (scroll down below the data), and perhaps worst of all, color coded cells! Your standard code package will need some extra arguments to handle those first two concerns, but the color coding is all but lost unless you view the data through a program with a graphic user interface (GUI) such as Excel or Google Sheets. \n",
    "\n",
    "![Excel Worksheet](WorksheetOverview.png)\n",
    "\n",
    "Scrolling to the bottom of my worksheet (pictured above), I can see what the blue colored-cells indicate (most recent value or growth rate), and the beginnings of my data dictionary. Looking at the data table itself, I can see that the bottom 12 rows are aggregate values for diferent geographic areas, and that missing values are denoted as \"..\". This information (the color legend, dictionary, row meaning, and missing values) all help determine what this data is, and what I need to do before I start processing it. Since I (somewhat arbitrarily in these examples) am only interested in the country values, I will be dropping the bottom 12 rows. These are all pieces of information that was intended to be given to you via the Excel window. Doing a manual check of proprietary files such as Excel in its intended program will save you confusion later, and will alert you to any non-standard components (such as a data dictionary) that you have to work around.   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Location (Center)##\n",
    "It's almost a daily occurance to hear someone rattle off the average value of some metric. Average grade on an exam, average score after a game, are all examples where we want to know where the center of our data is. There are different measures to observe your data's center, such as mean, median, and mode. All which give us a slighly different view of the data. It's your responsiblity as a data scientist to differentiate which measure will best fit your data, and be able to justify that.\n",
    "\n",
    "### Arithmetic Mean ###\n",
    "Mean, which is typically common shorthand for arithmetic mean, provides us with the sum of a metric for each observation divided by the number of observations. The mean is the most common measurment of data's center, but may not provide an accurate representation of \"center\" or \"middle\" if the data is skewed. \n",
    "\n",
    "To begin, I copied all relevant rows (in my example, up to the last country, Zimbabwe) and pasted into a new sheet. This isn't necessary, but it cuts out extra noise and provides me room to add these standard statistics. Additionally, I copied the column headers into a new (currently empty) table, so I had an easy way to verify that I'd run each summary statistic on every column. Clicking in a cell in Excel allows you to type a raw value (such as 4 or \"dog\"), but most Excel functionality stems from writing and using functions. \n",
    "\n",
    "I want to this average to be calculated using only the data values, not the column headers, meaning I will need data starting at row 5 and ending at 218. My first column of data starts on column B. These pieces of info are all we need to calculate the average. The average function expects a start cell (denoted by (columnNumber) + (rowNumber)) and and end cell. This syntax is very standard for datasheet functions, and we will be using it often. Typing `=avergage(B5:B218)` into a cell calculates my average for column B, 235.99 billion cu. m\n",
    "\n",
    "![Average Function](AverageSnap.gif)\n",
    "\n",
    "In the recording above, you'll notice once I ran the average for column B that I did a lot of things with my mouse pretty fast. I:\n",
    "- moved my mouse to the lower right corner of the cell, where a small green box appeared\n",
    "- selected (left-clicked) that small green box and dragged my mouse to the right \n",
    "- released the left-click, upon which Excel auto ran the average functions and output the results\n",
    "\n",
    "This is something unique to Excel, and is powered by Excel's ability to autogenerate and iterate functions based on user input. Once I had released the left-click, Excel automatically pasted the function I just ran, *increasing the column by one.* This means that the inital function `=average(B5:B218)` became `=average(C5:C218)` in the adjacent cell, and that is iterated for all columns. If I had dragged my mouse vertically down the page, Excel would have iterated the row numbers, changing from 5:218 to 6:219 and so on. This functionality is a huge time saver, and I will be using it throughout these guides. \n",
    "\n",
    "### Median ###\n",
    "The median value is the true \"middle\" of your data, seperating the lower half from the higher. Medians are less impacted by data shape and size, making it a robust metric to compare against. \n",
    "\n",
    "Calculating a median in Excel is very similar to the procedure for average (mean). In a new cell, enter `=MEDIAN(B5:B218)` and drag the selection to apply this to all necessary columns. \n",
    "\n",
    "### Mode ###\n",
    "Mode is the datapoint that appears the most. It is the most likely value to be found if a variable is measured. Modes are useful when we need to know what is the most likely event for a given variable/metric.\n",
    "\n",
    "Once again, the technical aspect is near identical to the ones above. In a new cell, enter `=MODE(B5:B218)` and increase the selection to all necessary columns. \n",
    "\n",
    "![Mean Overview](MeanOverview.png)\n",
    "\n",
    "As you can see in the screenshot above, our measures of centrality have provided fairly different results. Looking at the first column, we can see a mean of 235.9, a median of 34.4, and a mode of 0.4. That should raise some alarms for you that something in the data isn't normal. Mean is most likely to be impacted by skewed data, so our next stop is to examine the data's shape. We can assume this disparity is stemming from countries that utilize way more water than others, but now we have to identify the cause and prove or disprove that assumption.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Spread #\n",
    "Spread gives us insight into how far values are from the mean, and which quartiles or percentiles those values lie. This tells us how similar out our values are to each other, and identify outliers or abnormalities in the values. We will focus on the standard measures of spread: variance, standard deviation, and interquartile range (IQR)\n",
    "\n",
    "## Variance ##\n",
    "While basic, the variance measures how far values are spread from the dataset average. It is a descriptive value that'll provide insight into the general spread, and whether further data processing is required. \n",
    "\n",
    "Calculating variance operates identically to the functions above, and can be done by inputting `=VAR(B5:B218)` then expanding the function to additional columns as necessary. In Freshwater column B I have a mean of 235 and a variance of 465,422, which is quite large. Hopefully some future measures will let me know why.\n",
    "\n",
    "## Standard Deviation ##\n",
    "The square root of variance, standard deviation indicates the range values are spread over, with a lower standard deviation indicating that most values lie close to the mean. Larger standard deviations indicate more a wider range of data, and can be indicative of outliers or skewed data, among many other things.\n",
    "\n",
    "Just like before, we'll head to a new cell and input the Excel function. For standard deviation it is `=STDEV(B5:B218)`\n",
    "\n",
    "## Interquartile Range (IQR) ##\n",
    "IQR is the middle 50% of the data, and identifies data distribution in quartiles. IQRs are most commonly used to generate box plots and identify outliers, and are a quick way to look at the spread of data and begin to understand your data shape. A small IQR indicates a smaller spread of data, and more data clustered along the average. \n",
    "\n",
    "Generating the IQR with Excel is done via the `QUARTILE()` function, which takes two arguments. The first you are familiar with, and is the range of data to apply the calculation to. The second argument denotes which quartile you want, ranging from 0 (minimum value) to 4 (maximum value) with 1, 2, and 3 being 25%, 50%, and 75% quartiles respectively. Since I first want to calculate the 25% quartile, I will enter the function `=QUARTILE(B5:B218, 1)` and expand this to all necessary columns. Once done, in the subsequent rows use `=QUARTILE(B5:B218, 2)` and `=QUARTILE(B5:B218, 3)` to pull the 50% and 75% quartiles respectively. You'll notice the 50% quartile is the median, which you calculated earlier. \n",
    "\n",
    "![Spread Overview](SpreadOverview.png)\n",
    "\n",
    "Looking at my spread measurments, I'm more sure than ever the difference between my median and mode is stemming from countries that withdrawl freshwater at a much higher or lower rate than the average. Just looking at column B, my 75% quartile value is 142, and I can see that Brazil far exceeds that with 5,661 billion cu. m. We're getting close to understanding the data, but first we need to know the shape before we make any findings."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Shape #\n",
    "Shape helps determine which statistical tests will and will not fit your data well. Large skews and abnormal shapes can cause the statistical analysis to be a poorer approximation of your data, and at times can be all together wrong if your data shape is too abnormal. The most common way to view shape is via a histogram, but as that can get noisy if your monitoring several variables I will be showcasing the skewness function.\n",
    "\n",
    "## Skewness ## \n",
    "Skewness measures how central the mean is in relation to the spread of values. A positive value means the data is left-skewed with a right tail of more positive values. A negative skew indicates the opposite, showing a right-skewed dataset with a leftward tail of more negative values. \n",
    "\n",
    "Calculating skew in Excel is as simple as the previous examples, and is called via the function `SKEW()`. To measure skew, I typed `=SKEW(B5:B218)` and expanded this to all columns. \n",
    "\n",
    "![Skew Overview](SkewOverview.png)\n",
    "\n",
    "Our skew, like we anticipated, is positive (and thus left-skewed) for column B, indicating that there are countries far exceeding the average freshwater withdrawls. At this point of the journey you would begin doing some exploritory analysis, likely outputting a sorted list of countries or showing the top 20% of freshwater users. But, for the sake of this guide we will leave those questions unanswered. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>billion cu. m</th>\n",
       "      <th>billion cu. m</th>\n",
       "      <th>% of internal resources</th>\n",
       "      <th>% for agriculture</th>\n",
       "      <th>% for industry</th>\n",
       "      <th>% of domestic</th>\n",
       "      <th>2010 $ per cu. m</th>\n",
       "      <th>% of urban population</th>\n",
       "      <th>% of rural population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>215</td>\n",
       "      <td>215</td>\n",
       "      <td>215</td>\n",
       "      <td>215</td>\n",
       "      <td>215</td>\n",
       "      <td>215</td>\n",
       "      <td>215</td>\n",
       "      <td>215</td>\n",
       "      <td>215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>159</td>\n",
       "      <td>89</td>\n",
       "      <td>123</td>\n",
       "      <td>62</td>\n",
       "      <td>51</td>\n",
       "      <td>53</td>\n",
       "      <td>66</td>\n",
       "      <td>88</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>..</td>\n",
       "      <td>..</td>\n",
       "      <td>..</td>\n",
       "      <td>..</td>\n",
       "      <td>..</td>\n",
       "      <td>..</td>\n",
       "      <td>..</td>\n",
       "      <td>..</td>\n",
       "      <td>..</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>30</td>\n",
       "      <td>75</td>\n",
       "      <td>69</td>\n",
       "      <td>111</td>\n",
       "      <td>85</td>\n",
       "      <td>115</td>\n",
       "      <td>105</td>\n",
       "      <td>40</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       billion cu. m billion cu. m  % of internal resources   \\\n",
       "count            215            215                      215   \n",
       "unique           159             89                      123   \n",
       "top               ..             ..                       ..   \n",
       "freq              30             75                       69   \n",
       "\n",
       "       % for agriculture  % for industry  % of domestic  2010 $ per cu. m  \\\n",
       "count                 215             215            215              215   \n",
       "unique                 62              51             53               66   \n",
       "top                    ..              ..             ..               ..   \n",
       "freq                  111              85            115              105   \n",
       "\n",
       "       % of urban population % of rural population  \n",
       "count                    215                   215  \n",
       "unique                    88                   120  \n",
       "top                       ..                    ..  \n",
       "freq                      40                    46  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_excel('3.5_Freshwater.xls', index_col=0, skiprows=2, skipfooter=87)\n",
    "\n",
    "df.describe()\n"
   ]
  },
  {
   "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": "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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}