09-Pandas 4.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas Continued\n",
    "\n",
    "Today we will cover how to create new column, remove a column, join dataframes together then grouping and aggregating data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "data_1 = {\n",
    "    \"first\":[\"John\",\"Mark\",\"David\",\"Peter\",\"James\"],\n",
    "    \"last\":[\"Reilly\",\"Boyle\",\"Smith\",\"Doe\",\"Bond\"],\n",
    "    \"email\":[\"John.Reilly@strath.ac.uk\",\"Mark.Boyle@MB.com\",\"DavidSmith2020@Smith.co.uk\", \"PeterDoe@PeterDoe.com\",\"JamesBond007@MI6.gov.uk\"],\n",
    "    \"id\":[1,2,3,4,5]\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1 = pd.DataFrame(data_1)\n",
    "df_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_2 = {\n",
    "    \"first\":[\"Scott\",\"Gary\"],\n",
    "    \"last\":[\"Reid\", \"Russell\"],\n",
    "    \"email\":[\"Scott.Reid@SR.com\",\"GaryRussell@Gary.co.uk\"],\n",
    "    \"id\":[11,12]\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2 = pd.DataFrame(data_2)\n",
    "df_2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To create a new column called \"name\" which takes the first and last name and join them together, you simply assign a new variable to the dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1[\"name\"] = df_1[\"first\"] + \" \" + df_1[\"last\"]\n",
    "df_1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets remove the first and last columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1.drop(columns=[\"first\",\"last\"])\n",
    "df_1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "remember that doesn't save and you need to do inplace=True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1.drop(columns=[\"first\",\"last\"], inplace=True)\n",
    "df_1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "lets replace the columns first and last using the new column name. We will be using the str method and the split method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1[\"name\"].str.split(\" \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In pandas there is an argument called expand which takes this series of lists into seperate columns/dataframe. If you try to assign this series to the column pandas will raise an error as you are passing a series which isn't the same length as the columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1[\"name\"].str.split(\" \",expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1[[\"first\",\"last\"]] = df_1[\"name\"].str.split(\" \",expand=True)\n",
    "df_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1.drop(columns=[\"name\"], inplace=True)\n",
    "df_1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to add new data but you do not have enough data to match the number of columns, you can use append with the argument ignore_index=True. \n",
    "\n",
    "The data missing will be represented as NaN."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1.append({\"first\":\"Billy\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1.append({\"first\":\"Billy\"}, ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets say you have 2 dataframe and you want to join them together, you can use the append method as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1.append(df_2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see the index has now become non-unique which can cause issues when you are trying to sort or analyse the data. Just like above pass True to the argument of ignore_index and pandas will automatically auto-index to the df_1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1.append(df_2, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Append does not have an inplace argument so to save the changes you have to assign it to a variable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df_1.append(df_2, ignore_index=True)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see how to remove a row, we can use the drop method with the argument index. Remember to save the changes use inplace=True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(index=6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You might not always know the number you want to remove or you want to remove many rows based off of a filter. To use a filter simple create the filter and pass it into the index argument with the index property."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(index=df[df[\"last\"]== \"Bond\"].index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How does that work? You use a filter to create a new dataframe and then use the index property which returns a list of the indexs in the new dataframe.\n",
    "\n",
    "To make it clear, I would recommend moving the filter to a new line and pass it as an arguement."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df[\"last\"]== \"Bond\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df[\"last\"]== \"Bond\"].index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_last = df[df[\"last\"]== \"Bond\"]\n",
    "df.drop(index=filter_last.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Grouping and Aggregating\n",
    "\n",
    "Now lets move onto grouping and aggregating our data, for these examples we are going to use the Glasgow_Weather.csv again."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather_df = pd.read_csv(\"Glasgow_Weather.csv\",index_col=\"day\")\n",
    "weather_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have previously learnt how to find out how many rows each column has of data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather_df.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we want to find the break down of each column and the answer given we can use the method value_counts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather_df[\"desc\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see in the desc column there were 5 descriptions given and the number of times it was choosen next to it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather_df[\"summary\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather_df[\"summary\"].value_counts().head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The percetange can be found by passing the value of True to the argument normalize."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather_df[\"desc\"].value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see rain was describe 47% of the time for this data set.\n",
    "\n",
    "We can group our data by setting up a groupby object, in this example we are grouping our data by the desc column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather_df.groupby([\"desc\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "desc_groupby = weather_df.groupby([\"desc\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "desc_groupby.get_group(\"clear-day\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see this is the same as our filtering we have used previously. The power of groupby comes when you want to find the data on more than one desc.\n",
    "\n",
    "Let's say we want to find value_counts of all the summary where the desc equals clear_day, we would need to type something quite long and difficult to read and only be able to show values of the single desc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather_df.loc[weather_df[\"desc\"] == \"clear-day\"][\"summary\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Groupby allows you to do the above on all desc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "desc_groupby[\"summary\"].value_counts().head(60)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "desc_groupby[\"summary\"].value_counts(normalize=True).head(60)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want only a single desc then use the loc method and pass the desc you want."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "desc_groupby[\"summary\"].value_counts(normalize=False).loc[\"rain\"]   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "desc_groupby[\"summary\"].value_counts(normalize=False).loc[\"fog\"]   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also use the methods we have already seen before like sum, max, min, median......"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "desc_groupby[\"tempMax\"].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "desc_groupby[\"tempMax\"].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "desc_groupby[\"tempMax\"].median()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also run all these functions at once and create a new dataframe with the results. This is done by using the agg method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "desc_groupby[\"tempMax\"].agg([\"min\",\"max\",\"median\",\"sum\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "desc_groupby[\"tempMax\"].agg([\"min\",\"max\",\"median\",\"sum\"]).loc[\"fog\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "desc_groupby.agg([\"min\",\"max\",\"median\",\"sum\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "desc_groupby.agg([\"min\",\"max\",\"median\",\"sum\"])[[\"tempMin\",\"tempMax\",\"cloudCover\"]]"
   ]
  },
  {
   "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}