Pandas_and_tidying_solutions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tidy data in Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Handy hints "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some Jupyter shortcut keys:\n",
    "\n",
    "- Esc : get into Command mode (leaves Edit mode)\n",
    "- Enter : edit a cell (puts you in Edit mode)\n",
    "- h : see help (see all commands)\n",
    "- Shift+Enter or Ctrl+Enter : run the code in the cell\n",
    "- a / b : add new cell above/below\n",
    "- m : turn current cell into a Markdown cell\n",
    "- y : turn current cell into a Code cell\n",
    "- 1-6 : turn current cell into a heading (a type of Markdown cell)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can get help on Python objects and functions with `help()` or the `?` operator."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-19T11:29:49.600774Z",
     "start_time": "2019-01-19T11:29:49.581459Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-19T11:29:50.000633Z",
     "start_time": "2019-01-19T11:29:49.987260Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# This causes Jupyter to display any matplotlib plots directly in the notebook\n",
    "# It also works for pandas and seaborn, since they use matplotlib to render plots\n",
    "%matplotlib inline\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-19T11:29:51.169379Z",
     "start_time": "2019-01-19T11:29:51.163406Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# pyplot (as plt) is the module we'll primarily use to instantiate matplotlib plot objects\n",
    "from matplotlib import pyplot as plt\n",
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can find the Pandas website and documentation at https://pandas.pydata.org/\n",
    "\n",
    "Pandas is a popular Python library for handling tabular data. It provides much of the same functionality for Python as do data frames in the R language. \n",
    "\n",
    "The fundamental data types in Pandas are a Series, representing a 1D array of data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = pd.Series([\"Diego\",\"Jessica\",\"Farah\"])\n",
    "s"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "and a DataFrame, representing a 2D table of data: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'Name': [\"Diego\",\"Jessica\",\"Farah\"],\n",
    "                   'Age': [34, 27, 50]})\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can think of a DataFrame like a spreadsheet or a table in a database; every column represents a variable in the dataset. Each column of a DataFrame has a particular type (ints, floats, datetimes, strings etc) and each column can be treated as a Pandas Series.\n",
    "\n",
    "Above, we constructed data manually using lists and dicts. For the rest of this workshop, we will work with real data. DataFrames are the natural type to use when reading in tabular data from, for instance, CSV files or Excel files.\n",
    "\n",
    "Here's example to read in some small datasets which we can use for demo purposes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "iris = pd.read_csv('iris.csv')\n",
    "cars = pd.read_csv('mtcars.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data we'll use for this workshop is from the [Long Term Evolution Experiment (LTEE)](http://myxo.css.msu.edu/ecoli/). This experiment has been running for over 30 years and over 50,000 E. coli generations, and is still ongoing. Twelve separate populations of E. coli have been propagated for the life of the experiment. Every 500 generations, each population is cloned and stored, allowing researchers to study evolutionary behaviour over the long term, and to \"rewind and replay\" alternate evolutionary trajectories by propagating from an earlier clone. \n",
    "\n",
    "Several interesting events have occurred during the experiment. Some populations have spontaneously developed hypermutator phenotypes. In addition, around generation 31,000 one population, Ara-3, spontaneously developed a rare and novel Cit+ mutation, giving it the ability to metabolise citrate in the substrate.\n",
    "\n",
    "There have been many publications from this experiment. A handful:\n",
    "\n",
    "- [Blount et al 2008: Historical contingency and the evolution of a key innovation in an experimental population of Escherichia coli](https://www.pnas.org/content/105/23/7899) - on the spontaneous development of citrate metabolisation and on potentiating mutations\n",
    "- [Tenaillon et al 2016: Tempo and mode of genome evolution in a 50,000-generation experiment](https://www.ncbi.nlm.nih.gov/pmc/articles/PMC4988878) - various investigations by sequencing and variant-calling over 50,000 generations of clones, including discussion of hypermutator phenotypes and genetic drift vs natural selection.\n",
    "\n",
    "Sequence data from clones is available, but for this workshop we'll just be using some published tabular data.\n",
    "\n",
    "A version of this dataset is also used by the [Data Carpentry lessons on Genomics](https://datacarpentry.org/genomics-workshop/)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the repository you'll find the files:\n",
    "\n",
    "- ltee_sampleruns.csv : sample and sequencing run metadata for the E. coli clones\n",
    "- ltee_mutations.csv : analysis output from variant calling on the E. coli clones\n",
    "- ltee_relative_fitness.tsv : relative fitness values for each population and generation up to generation 10,000\n",
    "- ltee_cell_size.tsv : cell sizes for each population and generation up to 10,000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's read in the sample and run metadata. Pandas has functions for reading in many data types. Try looking at the documentation for `read_csv()` by running `help(pd.read_csv)` or `pd.read_csv?`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sampleruns = pd.read_csv('ltee_sampleruns.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The number of rows and columns\n",
    "sampleruns.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# The first few rows\n",
    "sampleruns.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Column data types\n",
    "sampleruns.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Column names\n",
    "sampleruns.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Index (i.e. row names)\n",
    "# In this case we didn't provide an index and rows have simply been numbered for us by Pandas\n",
    "sampleruns.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise\n",
    "\n",
    "1. Use `pd.read_csv()` to read the file `ltee_mutations.csv` into a variable called `mutations`.\n",
    "2. Check the column headings and the number of rows in this dataset, and have a look at the first few rows. Compare the size of the dataset and the variables to `sampleruns`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mutations = pd.read_csv('ltee_mutations.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "mutations.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Indexing and slicing "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can extract a column from the DataFrame by indexing with square brackets, e.g. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extracting a column\n",
    "sampleruns['Strain ID']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Firstly, let's set our index (row names) to something more meaningful to make it easier to see what's going on. The Strain ID uniquely identifies each sample, so it is probably a sensible index. We can use `sampleruns.set_index()`, or we can assign to the index directly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sampleruns.index = sampleruns['Strain ID']\n",
    "sampleruns.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sampleruns.set_index('Strain ID')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The two most important ways to extract data from a DataFrame are `loc` and `iloc`. `loc` uses the index and the column names; `iloc` uses the row and column numbers, counting from zero. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sampleruns.loc['REL768B', 'Accession']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Row 0, column 9\n",
    "sampleruns.iloc[0,9]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rows 1-3, column 9\n",
    "sampleruns.iloc[1:4, 9]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# All columns\n",
    "sampleruns.loc['REL768B', :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lists let us specify any set of rows and columns, in any order\n",
    "sampleruns.loc[['REL768A','REL958A'], ['Read Type', 'Read Length']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can also use True/False values to perform boolean indexing. \n",
    "# Pandas will return the rows/columns matching the True values we pass in.\n",
    "# This will be useful later for filtering data\n",
    "iris.loc[0:5, [True, False, True, True, False]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise\n",
    "\n",
    "1. Set the index of `mutations` to be the same as the \"Strain ID\" column.\n",
    "2. Extract the population, generation, and number of total mutations for strain REL11345"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mutations.index = mutations['Strain ID']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mutations.loc['REL11345', ['Population','Generation', 'Total Mutations']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Manipulating Series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A single column of a DataFrame is a Series object. Series have a data type:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sampleruns['Sequencing Depth'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Like a DataFrame, a Series has an index. In this case we got our Series from a column of a DataFrame, so it will have the same index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sampleruns['Sequencing Depth'].index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are several convenience functions defined on Series. For instance, we can find the average sequencing depth:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sampleruns['Sequencing Depth'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similarly for numeric variables we have, for instance, `.min()` and `.max()`, `.median()`, `std()`, and `sum()`.\n",
    "\n",
    "`.describe()` is a convenience function for getting several summary statistics at once:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sampleruns['Sequencing Depth'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For non-numeric variable types such as strings and categoricals, we may want to look at the unique values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sampleruns['Read Type'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sampleruns['Read Type'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas makes use of numpy vectorisation, meaning we can do operations on Series with simple syntax, and it will be efficient to run."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assuming 500 generations takes 75 days\n",
    "mutations['Days'] = mutations['Generation'] * 0.15\n",
    "mutations.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(mutations['Total Deleted Base Pairs'] + mutations['Total Inserted Base Pairs']).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You might have noticed that the `Analysis Notes` column contains a lot of NaN's. This means \"not a number\" and represents a missing value - i.e. these cells are empty. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sampleruns.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can check which values are missing with `.isnull()`. This converts every value in the DataFrame (or Series) into a boolean True/False value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "sampleruns.isnull().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Adding up booleans will treat `True` as `1` and `False` as `0`. A common approach is to use `sum()` to count how many `True` values there are. So we can count missing values like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sampleruns.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`sampleruns` had 264 rows, so it looks like there are a few non-empty note cells. We could count this explicitly by taking the logical `not` of our True/False values, i.e. adding up cells where `isnull()` is `False`. For manipulating array-like data, we can't use the `not`, `and` and `or` boolean operators. Instead we need to use the bitwise operators `~`, `&`, and `|`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(~sampleruns.isnull()).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that Pandas summed each column. We can use `sum(axis=1)` to override this default and sum each row instead."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sorting and filtering "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can sort on a field, or list of fields, with `.sort_values()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Get a random example subset\n",
    "subset = sampleruns.sample(15)\n",
    "subset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "subset.sort_values('Generation')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "subset.sort_values(['Population','Generation'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can filter datasets using boolean indexing. This means that if we use a logical expression produce a boolean Series with a logical expression:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "subset['Population'] == 'Ara+5'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "we can then select out only the rows (or sometimes columns) where that logical expression is True:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "subset.loc[subset['Population'] == 'Ara+5', :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise \n",
    "\n",
    "1. Filter the `sampleruns` dataset to extract only rows which contain Analysis Notes, i.e. those where this field is not empty.\n",
    "2. Filter the `mutations` dataset to extract only samples with more than 1500 total mutations. \n",
    "3. Sort the resulting data from (2) by the number of \"Small Indels\". Have a look at the resulting Population and Generation columns.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sampleruns[ ~sampleruns['Analysis Notes'].isnull() ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mutations[mutations['Total Mutations'] > 1500].sort_values('Small Indels')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Interlude: tidy data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Link to \"untidy data\" discussion spreadsheet](https://docs.google.com/spreadsheets/d/1P94oEzgxNzlpvYiento53tZxJwaHYi8gpcdpDouu2jw/edit?usp=sharing)\n",
    "\n",
    "(Don't look too far into this spreadsheet before we get up to it, as it includes solutions which are a spoiler for the exercise.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reshaping data "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can reshape data using particular columns, with `melt` and `pivot` or `pivot_table`. We'll have a look at this below.\n",
    "\n",
    "We can also reshape data using the column names and index, with `stack` and `unstack`. This requires MultiIndexes, which we won't go into today."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here are two tiny \"wide\" datasets based on our \"untidy\" housing data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-19T11:51:16.434295Z",
     "start_time": "2019-01-19T11:51:16.254462Z"
    },
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "sales_wide1 = pd.read_csv('housing-data-wide1.csv')\n",
    "sales_wide1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-19T11:54:26.755381Z",
     "start_time": "2019-01-19T11:54:26.723723Z"
    },
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "sales_wide2 = pd.read_csv('housing-data-wide2.csv', parse_dates=['date1','date2'])\n",
    "sales_wide2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How can we reshape these into tidy form? \n",
    "\n",
    "The Pandas `melt` function will do this. For instance:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_wide1.melt()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This has put every variable (i.e. every column) into the new `variable` column. This probably isn't what we want. It's only the price columns that are \"wide\", the other variables were fine."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Retain columns property_id and bedrooms\n",
    "sales_wide1.melt(id_vars=['property_id','bedrooms'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is getting close to what we want. The `variable` column contains the original column names and tells us whether the price we're looking at was from the first or second sale (this may or may not be information we care about). The `value` column contains values in the melted columns, i.e. the actual price. \n",
    "\n",
    "Now we technically have long form and have eliminated the duplicated `price` variable; all prices are now in the `value` column. Notice that properties can now appear more than once in the table; conceptually, we have a row per sale rather than a row per property. \n",
    "\n",
    "We can tell `melt()` what to call the `variable` and `value` columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_tidy = sales_wide1.melt(id_vars=['property_id','bedrooms'], \n",
    "                 var_name='sale_number',\n",
    "                 value_name='price')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We still have empty extra rows where there was no sale in the original table, i.e. rows 3 and 5. We could use `dropna()` to get rid of these. A more generic approach would be to use filtering:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales = sales_wide1.melt(id_vars=['property_id','bedrooms'], \n",
    "                                     var_name='sale_number',\n",
    "                                     value_name='price')\n",
    "sales = sales[~sales['price'].isnull()]\n",
    "sales"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is pretty good! Now we could run commands like `sales[\"price\"].mean()` and get a sensible answer. We'll also be able to use the data easily to produce plots.\n",
    "\n",
    "If you want a challenge, think about how you could convert `sales_wide2` to tidy form - it's a fair bit harder.\n",
    "\n",
    "The inverse operation to `.melt()` is `.pivot()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales.pivot(index='property_id', columns='sale_number', values='price')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's have a look at our two remaining LTEE datasets. \n",
    "\n",
    "These two files record measurements of:\n",
    "\n",
    "- the cell size for every population for the first 10,000 generations, measured every 1000 generations\n",
    "- the relative fitness for every population for the first 10,000 generations, as measured by the growth rate of the strain relative to a reference strain."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# These are TSV files, so specify tab as the delimiter\n",
    "cellsize = pd.read_csv('ltee_cell_size.tsv', sep=\"\\t\")\n",
    "fitness = pd.read_csv('ltee_relative_fitness.tsv', sep=\"\\t\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cellsize"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To make life easier down the track, we'll rename the columns to match the variable and population names used in the `sampleruns` and `mutations` tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "cellsize.columns = (['Generation'] + ['Ara-{}'.format(n) for n  in range(1,7)] + \n",
    "                    ['Ara+{}'.format(n) for n  in range(1,7)])\n",
    "cellsize.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fitness.columns = (['Generation'] + ['Ara-{}'.format(n) for n  in range(1,7)] + \n",
    "                    ['Ara+{}'.format(n) for n  in range(1,7)])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise\n",
    "\n",
    "The cell size and relative fitness data is in wide form. Convert each one to tidy form. When thinking about which columns are \"wide\", it may help to aim to match the variables in the `sampleruns` and `mutations` tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cellsize_tidy = cellsize.melt(id_vars='Generation', var_name='Population', value_name='Cell size')\n",
    "fitness_tidy = fitness.melt(id_vars='Generation', var_name='Population', value_name='Relative fitness')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging data "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can merge two datasets together by matching corresponding variables.\n",
    "\n",
    "Our main options are `DataFrame.join()` and `pandas.merge()`. `merge()` is a little more flexible, so we'll demonstrate that.\n",
    "\n",
    "Recall the `cars` dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cars.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `origin` column here, which is a number 1-3, is actually intended to represent the country of origin. It's encoded as:\n",
    "\n",
    "- USA : 1\n",
    "- Europe : 2\n",
    "- Japan : 3\n",
    "\n",
    "Let's make a DataFrame to represent this mapping. We'll add a fourth code for Australia, which doesn't appear in the dataset!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "origin_codes = pd.DataFrame(\n",
    "    {\n",
    "        'origin': [1,2,3],\n",
    "        'origin_country': ['USA','Europe','Japan']\n",
    "    }\n",
    ")\n",
    "origin_codes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use `pandas.merge()` to join our `cars` table to our `origin_codes` table using the shared `origin` field, like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cars_withorigin = pd.merge(cars, origin_codes)\n",
    "cars_withorigin.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This \"just worked\" because Pandas correctly deduced that the identically-named field(s) were the ones to match on. Sometimes we might need to be more verbose. In this case, this accomplishes the same thing as the above:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# use origin from the left dataframe (cars) and from the right (origin_codes)\n",
    "# use how=\"left\" (keep all origin values that exist in the left dataframe)\n",
    "cars_withorigin = pd.merge(cars, origin_codes, left_on='origin', right_on='origin', how=\"left\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's try to merge our `sampleinfo` and `mutations` columns. This time there are three shared fields: 'Strain ID', 'Population', and 'Generation'. In fact only 'Strain ID' is needed to uniquely identify  rows, but we want to specify all matching variables so that Pandas knows to only include each of these variables once in the resulting dataframe.\n",
    "\n",
    "Since we have set the index to be `Strain ID` and also kept it as a column, Pandas will complain that `Strain ID` is ambigious - it doesn't know if we mean the index or the column. We don't need the index for this so we'll just reset the index first, which will turn it into a simple row numbering."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# reset both indexes to simple row numbering, dropping current index\n",
    "sampleruns.reset_index(drop=True, inplace=True)\n",
    "mutations.reset_index(drop=True, inplace=True)\n",
    "mutations.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge the tables\n",
    "ltee = pd.merge(sampleruns, mutations, on=['Strain ID','Population','Generation'])\n",
    "ltee.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ltee.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that the metadata on Mutator phenotypes is together with the information on actual mutations, we could try exploring the relationships between these fields. Here are a couple of previews of ways to do this!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.boxplot(data=ltee, x='Mutator', y='Total Mutations')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ltee.groupby('Mutator')['Total Mutations'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise\n",
    "\n",
    "Merge the cell size data on to the `ltee` table with `pd.merge()`, using the Generation and Population variables. This is only possible if it's been converted to tidy form first, with variable names and values corresponding to those in `1tee`! You will probably want to set `how=\"left\"` (if the cell size data is on the right), since the `ltee` table contains generations that don't exist in the cell size data, and we don't want to throw these rows away.\n",
    "\n",
    "Do the same with the relative fitness data.\n",
    "\n",
    "Have a look at missing and repeated values in the newly-added columns. What do you see?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ltee_merged1 = pd.merge(ltee, cellsize_tidy, on=['Generation', 'Population'], how=\"left\")\n",
    "ltee_merged2 = pd.merge(ltee_merged1, fitness_tidy, on=['Generation', 'Population'], how=\"left\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Writing to a file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To finish off, let's write out our tidied and merged table to a new file, for future analyses. To write to CSV, we can use the `to_csv` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use whatever you named your merged dataframe here!\n",
    "# Don't keep the index as we still have the Strain ID column\n",
    "ltee.to_csv('ltee_solution.csv', index=False)"
   ]
  }
 ],
 "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.6"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {
    "height": "138px",
    "width": "252px"
   },
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": "block",
   "toc_window_display": true
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}