Intro to Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "japanese-officer",
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "sophisticated-briefing",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create dataframe from scratch using a dictionary\n",
    "temp_dict = {\"Fruit\":[\"Apple\",\"Banana\"],\n",
    "            \"Drinks\":[\"Fanta\",\"Coke\"]}\n",
    "print (temp_dict)\n",
    "pd.DataFrame(temp_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "invalid-index",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a df using list\n",
    "a = [1,2,3,4,5]\n",
    "b = ['a','e','i']\n",
    "pd.DataFrame([a,b])\n",
    "temp = pd.DataFrame([a,b]).T\n",
    "temp.columns =[\"first\",'second']\n",
    "temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "italic-subsection",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Import Titanic file (csv) as Pandas Dataframe\n",
    "df =  pd.read_csv(\"titanic.csv\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "sustained-delaware",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "induced-moscow",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Let's check data types and see if this makes sense. If not, we will convert it to their proper type.\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "artificial-punch",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['PassengerId'] = df['PassengerId'].astype('category')\n",
    "df['Survived'] = df['Survived'].astype('category')\n",
    "df['Pclass'] = df['Pclass'].astype('category')\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fantastic-kitchen",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Let us check if there are any empty values (nulls)\n",
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "neural-transcription",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Age has 177 nulls! Out of how many? \n",
    "# What to do with this? Delete? Or fill the gap with some statistics? Let's fill null with average age!\n",
    "avg_age = int(df.Age.mean())\n",
    "print (avg_age)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "chubby-brunswick",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Ok, lets create a new column and fill with avg\n",
    "df['age_av'] = df['Age']\n",
    "df['age_av'] = df['age_av'].fillna(avg_age)\n",
    "df['age_av'].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "geological-advertiser",
   "metadata": {},
   "outputs": [],
   "source": [
    "bins = range(0,90,10)\n",
    "plt.hist(df['age_av'], bins= bins, color = 'g', rwidth = 0.8)\n",
    "plt.hist(df['Age'], bins = bins, color = 'red', alpha = 0.5)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "secondary-theology",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Next thing, we can forward fill. I.e forward last good value to nulls\n",
    "df['age_fill'] = df['Age']\n",
    "df['age_fill'] = df['age_fill'].fillna(method ='ffill')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "material-martin",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.hist(df['age_fill'], bins= bins, color = 'g', rwidth = 0.8)\n",
    "plt.hist(df['Age'], bins = bins, color = 'red', alpha = 0.5)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "elect-romantic",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Next thing, we can interpolate\n",
    "df['age_lin'] = df['Age']\n",
    "df['age_lin'] = df['age_lin'].interpolate(method ='linear')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "passive-bible",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.hist(df['age_lin'], bins= bins, color = 'g', rwidth = 0.8)\n",
    "plt.hist(df['Age'], bins = bins, color = 'red', alpha = 0.5)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "numerous-picking",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[df.Age.isnull()][['Age',\"age_lin\",\"age_av\",\"age_fill\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "treated-duplicate",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[['Age',\"age_lin\",\"age_av\",\"age_fill\"]].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "civic-avenue",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "manual-runner",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "adolescent-friday",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Conditional filtering\n",
    "df.loc[(df.Name.str.startswith('S')) & (df.Age >50)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "abroad-civilian",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Split column\n",
    "#df.Name[0].split(',')[0]\n",
    "df['LastName']=[x.split(',')[0] for x in df.Name]\n",
    "df['FirstName'] = [x.split(',')[1] for x in df.Name]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "stock-lingerie",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "changing-group",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.corr()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "objective-edmonton",
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns\n",
    "sns.heatmap(df[['Age',\"Fare\",\"SibSp\",\"Parch\"]].corr(), annot = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "tribal-pasta",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Groupby\n",
    "df['Survived'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "healthy-white",
   "metadata": {},
   "outputs": [],
   "source": [
    "#How many males survived?\n",
    "df.loc[df.Sex == 'male'].Survived.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "angry-anatomy",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby(['Sex','Survived'])['Survived'].count() # sum(), mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "mobile-boston",
   "metadata": {},
   "outputs": [],
   "source": [
    "#HW1:\n",
    "# How many different Pclasses are there?\n",
    "# Of those different Pclasses, do male occupy more or female?\n",
    "# Is there any relationship between Pclass and whether they survived?\n",
    "# Is there any relationship between Age and whether they survived?\n",
    "# Who paid more in average? Male or female?\n",
    "# 'Cabin' has too many null values. Remove that column from dataframe. \n",
    "# Extract Middle name of the person from Name column (if exists) as save it as 'MiddleName' column."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "conceptual-nature",
   "metadata": {},
   "source": [
    "# Handling DateTime Using Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ranking-wednesday",
   "metadata": {},
   "outputs": [],
   "source": [
    "url='https://raw.githubusercontent.com/jenfly/opsd/master/opsd_germany_daily.csv'\n",
    "data = pd.read_csv(url,sep=\",\")\n",
    "data.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "matched-positive",
   "metadata": {},
   "outputs": [],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "worst-heritage",
   "metadata": {},
   "outputs": [],
   "source": [
    "# to explicitly convert the date column to type DATETIME\n",
    "data['Date'] = pd.to_datetime(data['Date'])\n",
    "data.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "handmade-irrigation",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set datetime as index\n",
    "data = data.set_index('Date')\n",
    "data.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "legislative-cherry",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Look at freq. Currently it is set to None.\n",
    "#What this means is that it is not known if data is collected by the hour,\n",
    "#by day, my minute, etc.\n",
    "# Now let's set the frequency to Daily.\n",
    "data_freq = data.asfreq('D')\n",
    "data_freq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "polished-storm",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_freq.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "enabling-christmas",
   "metadata": {},
   "outputs": [],
   "source": [
    "## If there were missing dates, the above code inserts nan. \n",
    "## So, let us do some imputation. \n",
    "data.shape, data_freq.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ahead-score",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_freq.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "precious-harvest",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_freq = data.asfreq('D', method = 'ffill')\n",
    "data_freq.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "declared-strengthening",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Resample Weekly\n",
    "data_columns = ['Consumption', 'Wind', 'Solar', 'Wind+Solar']\n",
    "data_weekly_mean = data[data_columns].resample('W').mean() # W stands for weekly\n",
    "data_weekly_mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "upper-small",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rolling average ( Doing 7 day-rolling aveerage)\n",
    "data_columns = ['Consumption', 'Wind', 'Solar', 'Wind+Solar']\n",
    "data_7d_rol = data[data_columns].rolling(window = 7, center = True).mean()\n",
    "data_7d_rol"
   ]
  },
  {
   "cell_type": "raw",
   "id": "billion-scholarship",
   "metadata": {},
   "source": [
    "center = True means for the time bin, say Jan 1 to Jan 8, the rolling mean would be calculated and placed next to the center of the bin i.e. Jan 4. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "functional-windsor",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_365d_rol = data[data_columns].rolling(window = 365, center = True).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "electric-tension",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=[15,5])\n",
    "plt.plot(data['Consumption'], marker='.', linestyle=\"None\")\n",
    "plt.plot(data_7d_rol['Consumption'], linewidth=2)\n",
    "plt.plot(data_365d_rol['Consumption'],linewidth=3)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "raised-caribbean",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Splitting date\n",
    "import datetime\n",
    "data['Year'] = data.index.year\n",
    "data['Month'] = data.index.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "honest-blink",
   "metadata": {},
   "outputs": [],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "tender-anthony",
   "metadata": {},
   "source": [
    "## HW2:\n",
    "1. Download COVID data for US. (Simple google will help you find those in CSV format).\n",
    "2. Set date as index.\n",
    "3. Filter data such that you only have data for maximum 3-4 states surrounding you.\n",
    "4. Fill the null dataset with linear interpolation. \n",
    "4. Plot daily, weekly and monthly running average of those states (one graph per state).\n",
    "5. Create a boxplot that shows variation of cases by month. \n",
    "6. Create a boxplot that shows variation of cases by week of day. \n",
    "7. Determine how closely they are correlated between different states. "
   ]
  }
 ],
 "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.6"
  },
  "vscode": {
   "interpreter": {
    "hash": "8fbdaa8efdbe90a434c704d84a12e0d683dfc88403df96cad7f0331de859e6dc"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}