15 - Lesson - Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas\n",
    "### Goal\n",
    "- Learn basics of Pandas\n",
    "- Read CSV files with Pandas\n",
    "- Explore the DataFrame\n",
    "- Learn about GroupBy\n",
    "- Datetime and GroupBy\n",
    "- How to export to Excel\n",
    "- Create a chart: [Example](https://xlsxwriter.readthedocs.io/example_chart_column.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "d = {'Cars': ['Porsche', 'Porsche', 'Ferrari', 'Ferrari'], 'Speed': [200, 215, 305, 195]}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['Cars'] == 'Porsche']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby('Cars').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "speed_check = pd.read_csv(\"files/speed_check.csv\", delimiter=';', parse_dates=True, index_col=\"Date\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "speed_check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "speed_check.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "speed_check.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "speed_check.groupby(\"Car\").mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly = speed_check.groupby(pd.Grouper(freq='M')).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly.index = monthly.index.month_name()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "speed_check.to_excel(\"SpeedCheck.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "workbook = pd.ExcelWriter(\"SpeedCheck.xlsx\")\n",
    "speed_check.groupby(\"Car\").mean().to_excel(workbook, sheet_name=\"Cars\")\n",
    "monthly.to_excel(workbook, sheet_name='Monthly')\n",
    "workbook.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "workbook = pd.ExcelWriter(\"SpeedCheck.xlsx\")\n",
    "speed_check.groupby(\"Car\").mean().to_excel(workbook, sheet_name=\"Cars\")\n",
    "monthly.to_excel(workbook, sheet_name='Monthly')\n",
    "\n",
    "chart1 = workbook.book.add_chart({'type': 'column'})\n",
    "\n",
    "# Configure the first series.\n",
    "chart1.add_series({\n",
    "    'name':       'Cars',\n",
    "    'categories': '=Cars!$A$2:$A$4',\n",
    "    'values':     '=Cars!$B$2:$B$4',\n",
    "})\n",
    "\n",
    "workbook.sheets['Cars'].insert_chart('D2', chart1)\n",
    "\n",
    "workbook.close()"
   ]
  },
  {
   "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}