notebooks/1.8-python-basics-working-with-tabular-data-csvs.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Working with Tabular Data\n",
    "\n",
    "Lesson 12 - CSV files, load, read csv, manipulate within dictionaries stored as elements in a list, filter data based on criteria, leverage LLMs to suggest trip activities using extracted data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's learn about csv files that structure data into rows and columns (tabular data yes!)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Text files are great but sometimes you need a bit more organization and structure, that's where csv files come into play.\n",
    "\n",
    "Imagine you have a bunch of information about customer tickets organized in a .csv file that you would like to understand a bit more about."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Super popular library for working with tabular data\n",
    "import pandas as pd\n",
    "from ai_tools import ask_ai"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is a .csv file?\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_name</th>\n",
       "      <th>issue_description</th>\n",
       "      <th>priority</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jane Doe</td>\n",
       "      <td>Customer was charged twice for the same transa...</td>\n",
       "      <td>High</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>John Smith</td>\n",
       "      <td>Customer unable to log into their account, fac...</td>\n",
       "      <td>Medium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alice Johnson</td>\n",
       "      <td>Customer wants more information about product ...</td>\n",
       "      <td>Low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bob Brown</td>\n",
       "      <td>Customer has not received the order yet, track...</td>\n",
       "      <td>High</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Michael Lee</td>\n",
       "      <td>Customer wants to return a product and needs a...</td>\n",
       "      <td>Medium</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_name                                  issue_description priority\n",
       "0       Jane Doe  Customer was charged twice for the same transa...     High\n",
       "1     John Smith  Customer unable to log into their account, fac...   Medium\n",
       "2  Alice Johnson  Customer wants more information about product ...      Low\n",
       "3      Bob Brown  Customer has not received the order yet, track...     High\n",
       "4    Michael Lee  Customer wants to return a product and needs a...   Medium"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "data_customer_tickets = pd.read_csv(\"./extracted_ticket_issues.csv\")\n",
    "\n",
    "data_customer_tickets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data contains 3 columns:\n",
    "1. `customer_name` - names of the customers\n",
    "2. `issue_description` - description of the issue they had\n",
    "3. `priority` - reference to the level of priority of that task"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could use Python to get for example only the high priority issues:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     True\n",
       "1    False\n",
       "2    False\n",
       "3     True\n",
       "4    False\n",
       "Name: priority, dtype: bool"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# == indicates equivalence!\n",
    "data_customer_tickets[\"priority\"]==\"High\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "high_priority_issues = data_customer_tickets[data_customer_tickets[\"priority\"]==\"High\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can take a look at the issues themselves:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_name</th>\n",
       "      <th>issue_description</th>\n",
       "      <th>priority</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jane Doe</td>\n",
       "      <td>Customer was charged twice for the same transa...</td>\n",
       "      <td>High</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bob Brown</td>\n",
       "      <td>Customer has not received the order yet, track...</td>\n",
       "      <td>High</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  customer_name                                  issue_description priority\n",
       "0      Jane Doe  Customer was charged twice for the same transa...     High\n",
       "3     Bob Brown  Customer has not received the order yet, track...     High"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "high_priority_issues"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Awesome! What we could do now is for example use our `ask_ai` tool to categorize the issues for us to help organizing the information, and then feed that back into the table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'That sounds like an exciting course! There are so many applications for automation with Python and AI tools. Here are some ideas and topics you might consider covering in your course:\\n\\n### Python Automation\\n1. **Basics of Python**:\\n   - Data types, variables, control structures (loops, conditionals).\\n   - Functions and modules.\\n\\n2. **Web Scraping**:\\n   - Using libraries like BeautifulSoup and Scrapy to extract data from websites.\\n   - Understanding ethical scraping and web scraping best practices.\\n\\n3. **File Handling**:\\n   - Automating file management tasks (reading/writing files, organizing files).\\n   - Working with CSV, JSON, and Excel files using pandas.\\n\\n4. **Task Automation with Scripts**:\\n   - Scheduling tasks with `cron` (Linux) or Task Scheduler (Windows).\\n   - Automating repetitive tasks, such as sending emails or generating reports.\\n\\n5. **APIs and Automation**:\\n   - Making HTTP requests with `requests` and accessing RESTful APIs.\\n   - Automating interaction with services like Twitter, Slack, or Google Sheets.\\n\\n### AI Tools Integration\\n1. **Introduction to AI and Machine Learning**:\\n   - Basic concepts of AI/ML and popular libraries such as Scikit-learn and TensorFlow.\\n   - Overview of supervised vs. unsupervised learning.\\n\\n2. **Natural Language Processing (NLP)**:\\n   - Automating text analysis with libraries like NLTK or spaCy.\\n   - Using AI models like GPT for text generation and summarization tasks.\\n\\n3. **AI for Automation**:\\n   - Creating chatbots with NLP and frameworks like Rasa or Dialogflow.\\n   - Automating customer interactions and other administrative tasks.\\n\\n4. **Image and Video Processing with AI**:\\n   - Working with image data using OpenCV and PIL.\\n   - Automating image classification or object detection tasks.\\n\\n5. **Machine Learning Automation**:\\n   - Introduction to AutoML libraries like H2O.ai or Auto-Sklearn.\\n   - Automating model training and deployment pipelines.\\n\\n### Project Ideas\\n- **Web Scraping Project**: Build a scraper that gathers data from a website and analyzes it.\\n- **Email Automation**: Create a Python script that sends scheduled emails with reports or notifications.\\n- **Chatbot Project**: Develop a simple chatbot that answers FAQs using NLP.\\n- **Data Analysis Project**: Analyze a dataset using pandas, visualize the results with Matplotlib or Seaborn.\\n\\n### Best Practices\\n- Discuss coding best practices, version control using Git, and the importance of documentation.\\n- Encourage students to think about the ethical implications of automation and AI.\\n\\nThis structure should help provide a comprehensive curriculum for your course. If you need specific examples, exercises, or resources for any of these topics, feel free to ask!'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from ai_tools import ask_ai\n",
    "\n",
    "ask_ai(\"Hi! I am teaching a course about automations with Python and some AI tools!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Categorizing issue: Customer was charged twice for the same transaction.\n",
      "Category: Billing\n",
      "Categorizing issue: Customer has not received the order yet, tracking information shows a delay.\n",
      "Category: Delay\n"
     ]
    }
   ],
   "source": [
    "categories_list = []\n",
    "for issue in high_priority_issues[\"issue_description\"]:\n",
    "    print(f\"Categorizing issue: {issue}\")\n",
    "    category = ask_ai(f\"Categorize this issue in just one single word and OUTPUT ONLY THAT WORD:\\n\\n issue: {issue}\\n category: \\n\")\n",
    "    print(f\"Category: {category}\")\n",
    "    categories_list.append(category)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice we use concepts we've learned before by looping over the issues, saving them to a list."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now with that information in hand we can actually update the dataframe accordingly, first we create a new column in the dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_customer_tickets[\"issue_category\"] = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Update categories for high priority issues using the index from high_priority_issues\n",
    "for idx, category in zip(high_priority_issues.index, categories_list):\n",
    "    data_customer_tickets.loc[idx, \"issue_category\"] = category"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_name</th>\n",
       "      <th>issue_description</th>\n",
       "      <th>priority</th>\n",
       "      <th>issue_category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jane Doe</td>\n",
       "      <td>Customer was charged twice for the same transa...</td>\n",
       "      <td>High</td>\n",
       "      <td>Billing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>John Smith</td>\n",
       "      <td>Customer unable to log into their account, fac...</td>\n",
       "      <td>Medium</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alice Johnson</td>\n",
       "      <td>Customer wants more information about product ...</td>\n",
       "      <td>Low</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bob Brown</td>\n",
       "      <td>Customer has not received the order yet, track...</td>\n",
       "      <td>High</td>\n",
       "      <td>Delay</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Michael Lee</td>\n",
       "      <td>Customer wants to return a product and needs a...</td>\n",
       "      <td>Medium</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_name                                  issue_description priority  \\\n",
       "0       Jane Doe  Customer was charged twice for the same transa...     High   \n",
       "1     John Smith  Customer unable to log into their account, fac...   Medium   \n",
       "2  Alice Johnson  Customer wants more information about product ...      Low   \n",
       "3      Bob Brown  Customer has not received the order yet, track...     High   \n",
       "4    Michael Lee  Customer wants to return a product and needs a...   Medium   \n",
       "\n",
       "  issue_category  \n",
       "0        Billing  \n",
       "1           None  \n",
       "2           None  \n",
       "3          Delay  \n",
       "4           None  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_customer_tickets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that the issues for which we did not analyse still contain a `None` indicating they haven't been categorized yet!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating and Managing Structured Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Besides analysing data, we can also create our ownn tables with information we care about."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's start with a practical example - creating a camping trip gear checklist:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Camping Gear Checklist:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>item</th>\n",
       "      <th>priority</th>\n",
       "      <th>estimated_cost</th>\n",
       "      <th>packed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tent</td>\n",
       "      <td>Essential</td>\n",
       "      <td>299.99</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Sleeping Bag</td>\n",
       "      <td>Essential</td>\n",
       "      <td>149.99</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Backpack</td>\n",
       "      <td>Essential</td>\n",
       "      <td>199.99</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Hiking Boots</td>\n",
       "      <td>Essential</td>\n",
       "      <td>159.99</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Water Filter</td>\n",
       "      <td>High</td>\n",
       "      <td>89.99</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>First Aid Kit</td>\n",
       "      <td>Essential</td>\n",
       "      <td>49.99</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Headlamp</td>\n",
       "      <td>High</td>\n",
       "      <td>39.99</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Camp Stove</td>\n",
       "      <td>Medium</td>\n",
       "      <td>79.99</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            item   priority  estimated_cost  packed\n",
       "0           Tent  Essential          299.99   False\n",
       "1   Sleeping Bag  Essential          149.99   False\n",
       "2       Backpack  Essential          199.99   False\n",
       "3   Hiking Boots  Essential          159.99   False\n",
       "4   Water Filter       High           89.99   False\n",
       "5  First Aid Kit  Essential           49.99   False\n",
       "6       Headlamp       High           39.99   False\n",
       "7     Camp Stove     Medium           79.99   False"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Create a camping gear checklist\n",
    "camping_gear = {\n",
    "    \"item\": [\n",
    "        \"Tent\", \"Sleeping Bag\", \"Backpack\", \"Hiking Boots\",\n",
    "        \"Water Filter\", \"First Aid Kit\", \"Headlamp\", \"Camp Stove\"\n",
    "    ],\n",
    "    \"priority\": [\n",
    "        \"Essential\", \"Essential\", \"Essential\", \"Essential\",\n",
    "        \"High\", \"Essential\", \"High\", \"Medium\"\n",
    "    ],\n",
    "    \"estimated_cost\": [\n",
    "        299.99, 149.99, 199.99, 159.99,\n",
    "        89.99, 49.99, 39.99, 79.99\n",
    "    ],\n",
    "    \"packed\": [\n",
    "        False, False, False, False,\n",
    "        False, False, False, False\n",
    "    ]\n",
    "}\n",
    "\n",
    "# Convert to DataFrame\n",
    "gear_df = pd.DataFrame(camping_gear)\n",
    "print(\"Camping Gear Checklist:\")\n",
    "display(gear_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Working with Data Filters\n",
    "\n",
    "Let's demonstrate how to filter and analyze our data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total cost of essential gear: $859.95\n",
      "\n",
      "Unpacked essential items:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>item</th>\n",
       "      <th>estimated_cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tent</td>\n",
       "      <td>299.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Sleeping Bag</td>\n",
       "      <td>149.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Backpack</td>\n",
       "      <td>199.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Hiking Boots</td>\n",
       "      <td>159.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>First Aid Kit</td>\n",
       "      <td>49.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            item  estimated_cost\n",
       "0           Tent          299.99\n",
       "1   Sleeping Bag          149.99\n",
       "2       Backpack          199.99\n",
       "3   Hiking Boots          159.99\n",
       "5  First Aid Kit           49.99"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def analyze_gear_requirements():\n",
    "    # Filter essential items\n",
    "    essential_gear = gear_df[gear_df['priority'] == 'Essential']\n",
    "    \n",
    "    # Calculate total cost of essential items\n",
    "    essential_cost = essential_gear['estimated_cost'].sum()\n",
    "    \n",
    "    # Get unpacked essential items\n",
    "    unpacked_essential = essential_gear[~essential_gear['packed']]\n",
    "    \n",
    "    print(f\"Total cost of essential gear: ${essential_cost:.2f}\")\n",
    "    print(\"\\nUnpacked essential items:\")\n",
    "    display(unpacked_essential[['item', 'estimated_cost']])\n",
    "\n",
    "analyze_gear_requirements()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a Trip Itinerary\n",
    "\n",
    "Let's create a more complex example with a detailed trip itinerary:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Trip Itinerary:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>day</th>\n",
       "      <th>date</th>\n",
       "      <th>activity</th>\n",
       "      <th>location</th>\n",
       "      <th>distance_km</th>\n",
       "      <th>difficulty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2024-06-01</td>\n",
       "      <td>Arrival and Camp Setup</td>\n",
       "      <td>Basecamp Area</td>\n",
       "      <td>2</td>\n",
       "      <td>Easy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2024-06-02</td>\n",
       "      <td>Mountain Trail Hike</td>\n",
       "      <td>Mountain Ridge Trail</td>\n",
       "      <td>8</td>\n",
       "      <td>Hard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2024-06-03</td>\n",
       "      <td>Lake Exploration</td>\n",
       "      <td>Crystal Lake</td>\n",
       "      <td>5</td>\n",
       "      <td>Moderate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2024-06-04</td>\n",
       "      <td>Forest Adventure</td>\n",
       "      <td>Ancient Forest</td>\n",
       "      <td>6</td>\n",
       "      <td>Moderate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2024-06-05</td>\n",
       "      <td>Pack and Departure</td>\n",
       "      <td>Basecamp Area</td>\n",
       "      <td>2</td>\n",
       "      <td>Easy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   day       date                activity              location  distance_km  \\\n",
       "0    1 2024-06-01  Arrival and Camp Setup         Basecamp Area            2   \n",
       "1    2 2024-06-02     Mountain Trail Hike  Mountain Ridge Trail            8   \n",
       "2    3 2024-06-03        Lake Exploration          Crystal Lake            5   \n",
       "3    4 2024-06-04        Forest Adventure        Ancient Forest            6   \n",
       "4    5 2024-06-05      Pack and Departure         Basecamp Area            2   \n",
       "\n",
       "  difficulty  \n",
       "0       Easy  \n",
       "1       Hard  \n",
       "2   Moderate  \n",
       "3   Moderate  \n",
       "4       Easy  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def create_trip_itinerary():\n",
    "    itinerary_data = {\n",
    "        'day': range(1, 6),\n",
    "        'date': pd.date_range('2024-06-01', periods=5),\n",
    "        'activity': [\n",
    "            'Arrival and Camp Setup',\n",
    "            'Mountain Trail Hike',\n",
    "            'Lake Exploration',\n",
    "            'Forest Adventure',\n",
    "            'Pack and Departure'\n",
    "        ],\n",
    "        'location': [\n",
    "            'Basecamp Area',\n",
    "            'Mountain Ridge Trail',\n",
    "            'Crystal Lake',\n",
    "            'Ancient Forest',\n",
    "            'Basecamp Area'\n",
    "        ],\n",
    "        'distance_km': [2, 8, 5, 6, 2],\n",
    "        'difficulty': [\n",
    "            'Easy',\n",
    "            'Hard',\n",
    "            'Moderate',\n",
    "            'Moderate',\n",
    "            'Easy'\n",
    "        ]\n",
    "    }\n",
    "    \n",
    "    itinerary_df = pd.DataFrame(itinerary_data)\n",
    "    return itinerary_df\n",
    "\n",
    "# Create and display the itinerary\n",
    "trip_itinerary = create_trip_itinerary()\n",
    "print(\"Trip Itinerary:\")\n",
    "display(trip_itinerary)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analyzing Trip Statistics\n",
    "\n",
    "Let's add some analysis to our trip planning:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Trip Analysis:\n",
      "Total distance: 23 km\n",
      "\n",
      "Difficulty breakdown:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "difficulty\n",
       "Easy        2\n",
       "Moderate    2\n",
       "Hard        1\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Longest day: Day 2 - Mountain Trail Hike\n",
      "Distance: 8 km\n"
     ]
    }
   ],
   "source": [
    "def analyze_trip_metrics(itinerary_df):\n",
    "    # Calculate total distance\n",
    "    total_distance = itinerary_df['distance_km'].sum()\n",
    "    \n",
    "    # Get difficulty breakdown\n",
    "    difficulty_counts = itinerary_df['difficulty'].value_counts()\n",
    "    \n",
    "    # Find longest day\n",
    "    longest_day = itinerary_df.loc[itinerary_df['distance_km'].idxmax()]\n",
    "    \n",
    "    print(f\"Trip Analysis:\")\n",
    "    print(f\"Total distance: {total_distance} km\")\n",
    "    print(\"\\nDifficulty breakdown:\")\n",
    "    display(difficulty_counts)\n",
    "    print(f\"\\nLongest day: Day {longest_day['day']} - {longest_day['activity']}\")\n",
    "    print(f\"Distance: {longest_day['distance_km']} km\")\n",
    "\n",
    "analyze_trip_metrics(trip_itinerary)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exporting and Saving Data\n",
    "\n",
    "Let's see how to save our data for later use:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def export_trip_data(gear_df, itinerary_df, filename_prefix):\n",
    "    # Export to CSV\n",
    "    gear_df.to_csv(f\"{filename_prefix}_gear.csv\", index=False)\n",
    "    itinerary_df.to_csv(f\"{filename_prefix}_itinerary.csv\", index=False)\n",
    "\n",
    "# Export our data\n",
    "export_trip_data(gear_df, trip_itinerary, \"camping_trip\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Practical Exercise: Trip Budget Calculator\n",
    "\n",
    "Let's create a budget calculator for our trip:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Trip Budget Summary:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Category</th>\n",
       "      <th>Cost</th>\n",
       "      <th>Percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Gear</td>\n",
       "      <td>1069.92</td>\n",
       "      <td>79.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Food</td>\n",
       "      <td>150.00</td>\n",
       "      <td>11.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Fuel</td>\n",
       "      <td>50.00</td>\n",
       "      <td>3.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Miscellaneous</td>\n",
       "      <td>75.00</td>\n",
       "      <td>5.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Category     Cost  Percentage\n",
       "0           Gear  1069.92        79.6\n",
       "1           Food   150.00        11.2\n",
       "2           Fuel    50.00         3.7\n",
       "3  Miscellaneous    75.00         5.6"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def calculate_trip_budget(gear_df, itinerary_df):\n",
    "    # Equipment costs\n",
    "    total_gear_cost = gear_df['estimated_cost'].sum()\n",
    "    \n",
    "    # Daily expenses (example values)\n",
    "    daily_expenses = {\n",
    "        'food': 30,\n",
    "        'fuel': 10,\n",
    "        'miscellaneous': 15\n",
    "    }\n",
    "    \n",
    "    num_days = len(itinerary_df)\n",
    "    daily_total = sum(daily_expenses.values())\n",
    "    total_daily_costs = daily_total * num_days\n",
    "    \n",
    "    # Create budget summary\n",
    "    budget_summary = pd.DataFrame({\n",
    "        'Category': ['Gear', 'Food', 'Fuel', 'Miscellaneous'],\n",
    "        'Cost': [\n",
    "            total_gear_cost,\n",
    "            daily_expenses['food'] * num_days,\n",
    "            daily_expenses['fuel'] * num_days,\n",
    "            daily_expenses['miscellaneous'] * num_days\n",
    "        ]\n",
    "    })\n",
    "    \n",
    "    budget_summary['Percentage'] = (\n",
    "        budget_summary['Cost'] / budget_summary['Cost'].sum() * 100\n",
    "    ).round(1)\n",
    "    \n",
    "    return budget_summary\n",
    "\n",
    "# Calculate and display budget\n",
    "budget = calculate_trip_budget(gear_df, trip_itinerary)\n",
    "print(\"Trip Budget Summary:\")\n",
    "display(budget)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Key Takeaways\n",
    "\n",
    "- Pandas provides powerful tools for working with tabular data\n",
    "- DataFrames can be filtered and analyzed in various ways\n",
    "- Data can be exported to different formats (CSV, Excel)\n",
    "- Structured data makes analysis and planning easier\n",
    "- Always consider data types when creating DataFrames\n",
    "- Use appropriate column names and data organization\n",
    "- Remember to handle missing data appropriately\n",
    "\n",
    "In the next lesson, we'll explore more advanced pandas operations and data visualization techniques!"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "oreilly-automate-tasks",
   "language": "python",
   "name": "oreilly-automate-tasks"
  },
  "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.11.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}