pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dictionary to DataFrame\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pre-defined lists\n",
    "names = [\"United States\", \"Australia\", \"Japan\", \"India\", \"Russia\", \"Morocco\", \"Egypt\"]\n",
    "dr = [True, False, False, False, True, True, True]\n",
    "cpc = [809, 731, 588, 18, 200, 70, 45]\n",
    "\n",
    "# Import pandas as pd\n",
    "import pandas as pd\n",
    "\n",
    "# Create dictionary my_dict with three key:value pairs: my_dict\n",
    "my_dict = {\"country\": names, \"drives_right\": dr, \"cars_per_cap\": cpc}\n",
    "\n",
    "# Build a DataFrame cars from my_dict: cars\n",
    "cars = pd.DataFrame(my_dict)\n",
    "\n",
    "# Definition of row_labels\n",
    "row_labels = [\"US\", \"AUS\", \"JPN\", \"IN\", \"RU\", \"MOR\", \"EG\"]\n",
    "\n",
    "# Specify row labels of cars\n",
    "cars.index = row_labels\n",
    "\n",
    "# Print cars\n",
    "print(cars)\n",
    "print(cars.describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CSV to DataFrame\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import pandas as pd\n",
    "import pandas as pd\n",
    "\n",
    "# Fix import by including index_col\n",
    "cars = pd.read_csv(\"datasets/cars.csv\", index_col=0)\n",
    "# Print out cars\n",
    "print(cars)\n",
    "print(\"----------------------------------------------\")\n",
    "print(cars.shape)\n",
    "print(\"----------------------------------------------\")\n",
    "print(cars.info())\n",
    "print(\"----------------------------------------------\")\n",
    "# Summary statistics for numerical data\n",
    "print(cars.describe())\n",
    "print(\"----------------------------------------------\")\n",
    "# Print the values of homelessness\n",
    "print(cars.values)\n",
    "print(\"----------------------------------------------\")\n",
    "# Know how many null values a column has\n",
    "print(cars.isnull().sum())\n",
    "print(\"----------------------------------------------\")\n",
    "# Print the column index of homelessness\n",
    "print(cars.columns)\n",
    "print(\"----------------------------------------------\")\n",
    "# Print the row index of homelessness\n",
    "print(cars.index)\n",
    "print(\"----------------------------------------------\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dataframe to CSV\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "output = pd.DataFrame({\"Country\": cars.index, \"cpc\": cars.cars_per_cap})\n",
    "output.to_csv(\"datasets/submission.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Col access using brackets\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "cars = pd.read_csv(\"datasets/cars.csv\", index_col=0)\n",
    "\n",
    "print(cars)\n",
    "print(\"----------------------------------------------\")\n",
    "print(cars[[\"country\", \"drives_right\"]])\n",
    "print(\"----------------------------------------------\")\n",
    "print(cars[[\"country\"]])\n",
    "print(\"----------------------------------------------\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Row access using brackets\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "cars = pd.read_csv(\"datasets/cars.csv\", index_col=0)\n",
    "\n",
    "print(cars)\n",
    "print(\"----------------------------------------------\")\n",
    "print(cars[1:4])\n",
    "print(\"----------------------------------------------\")\n",
    "print(cars.head(6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Rows & Cols access using loc & iloc\n",
    "\n",
    "`[Subsetting DataFrame]`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "cars = pd.read_csv(\"datasets/cars.csv\", index_col=0)\n",
    "\n",
    "print(cars)\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "\n",
    "# Access row by label\n",
    "print(cars.loc[[\"RU\"]])\n",
    "print(\"----------------------------------------------\")\n",
    "print(cars.iloc[[4]])\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "# print as series\n",
    "print(cars.loc[\"RU\"])\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "# print as dataframe\n",
    "print(cars.loc[[\"RU\"]])\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "\n",
    "# Access multiple rows by label\n",
    "print(cars.loc[[\"RU\", \"IN\", \"EG\"]])\n",
    "print(\"----------------------------------------------\")\n",
    "print(cars.iloc[[4, 3, 6]])\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "\n",
    "# Access row and column by label\n",
    "print(cars.loc[[\"RU\", \"IN\", \"EG\"], [\"country\", \"drives_right\"]])\n",
    "print(\"----------------------------------------------\")\n",
    "print(cars.iloc[[4, 3, 6], [1, 2]])\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "\n",
    "# All rows, some columns\n",
    "print(cars.loc[:, [\"country\", \"drives_right\"]])\n",
    "print(\"----------------------------------------------\")\n",
    "print(cars.iloc[:, [1, 2]])\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "\n",
    "# specific cell\n",
    "print(cars.loc[[\"RU\"], [\"country\"]])\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "# Sub-dataframe\n",
    "print(cars.loc[[\"RU\", \"IN\", \"EG\"], [\"country\", \"drives_right\"]])\n",
    "print(\"----------------------------------------------\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filtering from dataframe based on a condition\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import cars data\n",
    "import pandas as pd\n",
    "\n",
    "cars = pd.read_csv(\"datasets/cars.csv\", index_col=0)\n",
    "\n",
    "# Extract drives_right column as Series: dr\n",
    "dr = cars[\"drives_right\"]\n",
    "print(dr)\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "# Use dr to subset cars: sel\n",
    "sel = dr == True\n",
    "\n",
    "# Print sel\n",
    "print(cars[sel])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import cars data\n",
    "import pandas as pd\n",
    "\n",
    "cars = pd.read_csv(\"datasets/cars.csv\", index_col=0)\n",
    "\n",
    "# Create car_maniac: observations that have a cars_per_cap over 500\n",
    "cpc = cars[\"cars_per_cap\"]\n",
    "many_cars = cpc > 500\n",
    "car_maniac = cars[many_cars]\n",
    "\n",
    "# Print car_maniac\n",
    "print(cpc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import cars data\n",
    "import pandas as pd\n",
    "\n",
    "cars = pd.read_csv(\"datasets/cars.csv\", index_col=0)\n",
    "\n",
    "# Import numpy, you'll need this\n",
    "import numpy as np\n",
    "\n",
    "# Create medium: observations with cars_per_cap between 100 and 500\n",
    "cpc = cars[\"cars_per_cap\"]\n",
    "between = np.logical_and(cpc > 100, cpc < 500)\n",
    "medium = cars[between]\n",
    "\n",
    "# Print medium\n",
    "print(medium)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Iterate over a dataframe\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import cars data\n",
    "import pandas as pd\n",
    "\n",
    "cars = pd.read_csv(\"datasets/cars.csv\", index_col=0)\n",
    "\n",
    "# Iterate over rows of cars\n",
    "for label, row in cars.iterrows():\n",
    "    print(label)\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import cars data\n",
    "import pandas as pd\n",
    "\n",
    "cars = pd.read_csv(\"datasets/cars.csv\", index_col=0)\n",
    "\n",
    "# Adapt for loop\n",
    "for lab, row in cars.iterrows():\n",
    "    print(f\"{lab}: {row['cars_per_cap']}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Add column\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import cars data\n",
    "import pandas as pd\n",
    "\n",
    "cars = pd.read_csv(\"datasets/cars.csv\", index_col=0)\n",
    "print(cars)\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "cars[\"cpc per 1000\"] = cars[\"cars_per_cap\"] / 1000\n",
    "\n",
    "# Print cars\n",
    "print(cars)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add column using `loc`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import cars data\n",
    "import pandas as pd\n",
    "\n",
    "cars = pd.read_csv(\"datasets/cars.csv\", index_col=0)\n",
    "print(cars)\n",
    "print(\"----------------------------------------------\")\n",
    "# Code for loop that adds COUNTRY column\n",
    "for label, row in cars.iterrows():\n",
    "    cars.loc[label, \"COUNTRY\"] = row[\"country\"].upper()\n",
    "\n",
    "# Print cars\n",
    "print(cars)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add column using `apply()`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import cars data\n",
    "import pandas as pd\n",
    "\n",
    "cars = pd.read_csv(\"datasets/cars.csv\", index_col=0)\n",
    "print(cars)\n",
    "print(\"----------------------------------------------\")\n",
    "# Use .apply(str.upper) beacuse .upper() is a method\n",
    "cars[\"COUNTRY\"] = cars[\"country\"].apply(str.upper)\n",
    "# use .apply(len) because len() is a function\n",
    "cars[\"country length\"] = cars[\"country\"].apply(len)\n",
    "\n",
    "print(cars)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sorting based on Col\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import cars data\n",
    "import pandas as pd\n",
    "\n",
    "cars = pd.read_csv(\"datasets/cars.csv\", index_col=0)\n",
    "print(cars)\n",
    "print(\"----------------------------------------------\")\n",
    "# sorting based on one column\n",
    "cars_cpc = cars.sort_values(\"cars_per_cap\", ascending=False)\n",
    "print(cars_cpc)\n",
    "print(\"----------------------------------------------\")\n",
    "# sorting based on multiple columns\n",
    "cars_cpc_country = cars.sort_values([\"cars_per_cap\", \"country\"], ascending=[True, True])\n",
    "print(cars_cpc_country)\n",
    "print(\"----------------------------------------------\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Subsetting rows by categorical variables with `isin()`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import cars data\n",
    "import pandas as pd\n",
    "\n",
    "cars = pd.read_csv(\"datasets/cars.csv\", index_col=0)\n",
    "print(cars)\n",
    "print(\"----------------------------------------------\")\n",
    "countries = [\"Japan\", \"Russia\", \"Egypt\"]\n",
    "conditions = cars[\"country\"].isin(countries)\n",
    "print(cars[conditions])\n",
    "print(\"----------------------------------------------\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary Statistics\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "sales = pd.read_csv(\"datasets/sales.csv\", index_col=0)\n",
    "print(sales.head())\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "\n",
    "# Print the mean of weekly_sales\n",
    "print(sales[\"weekly_sales\"].mean())\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "# Print the median of weekly_sales\n",
    "print(sales[\"weekly_sales\"].median())\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "# Print the maximum of the date column\n",
    "print(sales[\"date\"].max())\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "# Print the minimum of the date column\n",
    "print(sales[\"date\"].min())\n",
    "print(\"----------------------------------------------\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Use `agg()`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "sales = pd.read_csv(\"datasets/sales.csv\", index_col=0)\n",
    "print(sales.head())\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "\n",
    "# A custom IQR function\n",
    "def iqr(column):\n",
    "    return column.quantile(0.75) - column.quantile(0.25)\n",
    "\n",
    "\n",
    "# Print IQR of the temperature_c column\n",
    "print(sales[\"temperature_c\"].agg(iqr))\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "# Update to print IQR of temperature_c, fuel_price_usd_per_l, & unemployment\n",
    "print(sales[[\"temperature_c\", \"fuel_price_usd_per_l\", \"unemployment\"]].agg(iqr))\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "# Update to print IQR and median of temperature_c, fuel_price_usd_per_l, & unemployment\n",
    "print(\n",
    "    sales[[\"temperature_c\", \"fuel_price_usd_per_l\", \"unemployment\"]].agg(\n",
    "        [iqr, np.median]\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Droping Duplicate `drop_duplicates(subset = ['',''])`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "sales = pd.read_csv(\"datasets/sales.csv\", index_col=0)\n",
    "\n",
    "# Drop duplicate store/type combinations\n",
    "store_types = sales.drop_duplicates(subset=[\"store\", \"type\"])\n",
    "print(store_types.head())\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "# Drop duplicate store/department combinations\n",
    "store_depts = sales.drop_duplicates(subset=[\"store\", \"department\"])\n",
    "print(store_depts.head())\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "# Subset the rows where is_holiday is True and drop duplicate dates\n",
    "holiday_dates = sales[sales[\"is_holiday\"] == True].drop_duplicates(\"date\")\n",
    "\n",
    "# Print date col of holiday_dates\n",
    "print(holiday_dates[\"date\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Counting categorical variables `value_counts(sort = True)`\n",
    "\n",
    "## Proportion `value_counts(normalize = True)`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "sales = pd.read_csv(\"datasets/sales.csv\", index_col=0)\n",
    "\n",
    "# Drop duplicate store/type combinations\n",
    "store_types = sales.drop_duplicates(subset=[\"store\", \"type\"])\n",
    "\n",
    "# Drop duplicate store/department combinations\n",
    "store_depts = sales.drop_duplicates(subset=[\"store\", \"department\"])\n",
    "\n",
    "# Count the number of stores of each type\n",
    "store_counts = store_types[\"type\"].value_counts()\n",
    "print(store_counts)\n",
    "\n",
    "# Get the proportion of stores of each type\n",
    "store_props = store_types[\"type\"].value_counts(normalize=True)\n",
    "print(store_props)\n",
    "\n",
    "# Count the number of each department number and sort\n",
    "dept_counts_sorted = store_depts[\"department\"].value_counts(sort=True)\n",
    "print(dept_counts_sorted)\n",
    "\n",
    "# Get the proportion of departments of each number and sort\n",
    "dept_props_sorted = store_depts[\"department\"].value_counts(sort=True, normalize=True)\n",
    "print(dept_props_sorted)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grouping using `grouby()`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "sales = pd.read_csv(\"datasets/sales.csv\", index_col=0)\n",
    "\n",
    "# Group by type; calc total weekly sales\n",
    "sales_by_type = sales.groupby(\"type\")[\"weekly_sales\"].sum()\n",
    "\n",
    "\n",
    "# Get proportion for each type\n",
    "sales_propn_by_type = sales_by_type / sum(sales_by_type)\n",
    "print(sales_propn_by_type)\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "# Group by type and is_holiday; calc total weekly sales\n",
    "sales_by_type_is_holiday = sales.groupby([\"type\", \"is_holiday\"])[\"weekly_sales\"].sum()\n",
    "print(sales_by_type_is_holiday)\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "# Group by type and is_holiday; calc max, min, mean & median weekly sales\n",
    "sales_by_type_is_holiday_stat = sales.groupby([\"type\", \"is_holiday\"])[\n",
    "    \"weekly_sales\"\n",
    "].agg([\"max\", \"min\", \"mean\", \"median\"])\n",
    "print(sales_by_type_is_holiday_stat)\n",
    "print(\"----------------------------------------------\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pivot Tables\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "sales = pd.read_csv(\"datasets/sales.csv\", index_col=0)\n",
    "\n",
    "# Pivot for mean weekly_sales for each store type\n",
    "mean_sales_by_type = sales.pivot_table(values=\"weekly_sales\", index=\"type\")\n",
    "\n",
    "# Print mean_sales_by_type\n",
    "print(mean_sales_by_type)\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "# Pivot for mean and median weekly_sales for each store type\n",
    "# Pivot for mean and median weekly_sales for each store type\n",
    "mean_med_sales_by_type = sales.pivot_table(\n",
    "    values=\"weekly_sales\", index=\"type\", aggfunc=[\"mean\", \"median\"]\n",
    ")\n",
    "\n",
    "# Print mean_med_sales_by_type\n",
    "print(mean_med_sales_by_type)\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "# Pivot for mean weekly_sales by store type and holiday\n",
    "mean_sales_by_type_holiday = sales.pivot_table(\n",
    "    values=\"weekly_sales\", index=\"type\", columns=\"is_holiday\", aggfunc=\"mean\"\n",
    ")\n",
    "\n",
    "# Print mean_sales_by_type_holiday\n",
    "print(mean_sales_by_type_holiday)\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "\n",
    "# Print mean weekly_sales by department and type; fill missing values with 0\n",
    "print(\n",
    "    sales.pivot_table(\n",
    "        values=\"weekly_sales\",\n",
    "        index=\"type\",\n",
    "        columns=\"department\",\n",
    "        aggfunc=\"mean\",\n",
    "        fill_value=0,\n",
    "    )\n",
    ")\n",
    "print(\"----------------------------------------------\")\n",
    "\n",
    "# Print the mean weekly_sales by department and type; fill missing values with 0s; sum all rows and cols\n",
    "print(\n",
    "    sales.pivot_table(\n",
    "        values=\"weekly_sales\",\n",
    "        index=\"type\",\n",
    "        columns=\"department\",\n",
    "        aggfunc=\"mean\",\n",
    "        fill_value=0,\n",
    "        margins=True,\n",
    "    )\n",
    ")\n",
    "print(\"----------------------------------------------\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}