{ "cells": [ { "cell_type": "markdown", "metadata": { "id": "pA6-UKy7ZgG9" }, "source": [ "# https://pandas.pydata.org/docs/user_guide/index.html" ] }, { "cell_type": "markdown", "metadata": { "id": "eu5cXvdFZtQT" }, "source": [ "## Pandas DataFrame Practice" ] }, { "cell_type": "markdown", "metadata": { "id": "D2X3Yn0NZi1q" }, "source": [ "### Imports" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "id": "jWqs7vCjZeZs" }, "outputs": [], "source": [ "# Import pandas and numpy\n", "import pandas as pd\n", "import numpy as np" ] }, { "cell_type": "markdown", "metadata": { "id": "yUFvMtNxZ1Li" }, "source": [ "### 1. Employee Performance Analysis\n", "\n", "| EmpID | Name | Department | HireDate |\n", "| ----- | ------- | ---------- | ---------- |\n", "| 101 | Alice | HR | 2018-06-01 |\n", "| 102 | Bob | IT | 2019-07-15 |\n", "| 103 | Charlie | Sales | 2020-03-22 |\n", "| 104 | Diana | HR | 2021-01-10 |\n", "\n", "----\n", "\n", "| EmpID | Year | Rating |\n", "| ----- | ---- | ------ |\n", "| 101 | 2021 | 3 |\n", "| 101 | 2022 | 4 |\n", "| 102 | 2021 | 5 |\n", "| 103 | 2021 | 4 |\n", "| 104 | 2022 | 2 |\n", "\n", "####Tasks:\n", "\n", "\n", "1. Merge both datasets on EmpID.\n", "2. Calculate each employee’s average rating across years.\n", "3. Filter for employees with an average rating of 4 or higher.\n", "4. Create a summary DataFrame grouped by Department showing the average rating of high-performing employees.\n", "\n", "\n", "\n", "\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "W9gsYywddevl" }, "outputs": [], "source": [ "employees_data = {\n", " 'EmpID': [101, 102, 103, 104],\n", " 'Name': ['Alice', 'Bob', 'Charlie', 'Diana'],\n", " 'Department': ['HR', 'IT', 'Sales', 'HR'],\n", " 'HireDate': ['2018-06-01', '2019-07-15', '2020-03-22', '2021-01-10']\n", "}\n", "\n", "performance_data = {\n", " 'EmpID': [101, 101, 102, 103, 104],\n", " 'Year': [2021, 2022, 2021, 2021, 2022],\n", " 'Rating': [3, 4, 5, 4, 2]\n", "}" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "LW4089Y5Z1Rh" }, "outputs": [], "source": [ "#Task 1" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "6HczCfiNcEGE" }, "outputs": [], "source": [ "#Task 2" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "bEMaf77LcEKM" }, "outputs": [], "source": [ "#Task 3" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "bcPmzQfYcEPT" }, "outputs": [], "source": [ "#Task 4" ] }, { "cell_type": "markdown", "metadata": { "id": "3gSZh_W5cElU" }, "source": [ "### 2. Product Sales and Inventory\n", "| ProductID | Date | UnitsSold |\n", "| --------- | ---------- | --------- |\n", "| 1 | 2024-01-01 | 10 |\n", "| 2 | 2024-01-01 | 5 |\n", "| 1 | 2024-01-02 | 15 |\n", "| 3 | 2024-01-02 | 7 |\n", "\n", "---\n", "| ProductID | ProductName | UnitPrice | Stock |\n", "| --------- | ----------- | --------- | ----- |\n", "| 1 | Widget A | 20 | 100 |\n", "| 2 | Widget B | 35 | 200 |\n", "| 3 | Widget C | 15 | 0 |\n", "\n", "\n", "Tasks:\n", "1. Merge sales and inventory data on ProductID.\n", "2. Compute Revenue for each sale (UnitsSold * UnitPrice).\n", "3. Aggregate total revenue per product and sort descending.\n", "4. Identify which products are out of stock.\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "3srlnU0Pd5Ex" }, "outputs": [], "source": [ "sales_data = {\n", " 'ProductID': [1, 2, 1, 3],\n", " 'Date': ['2024-01-01', '2024-01-01', '2024-01-02', '2024-01-02'],\n", " 'UnitsSold': [10, 5, 15, 7]\n", "}\n", "\n", "inventory_data = {\n", " 'ProductID': [1, 2, 3],\n", " 'ProductName': ['Widget A', 'Widget B', 'Widget C'],\n", " 'UnitPrice': [20, 35, 15],\n", " 'Stock': [100, 200, 0]\n", "}" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "4a-Ymv0Ic5Nw" }, "outputs": [], "source": [ "#Task 1" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "yeKMsdCmc5Ny" }, "outputs": [], "source": [ "#Task 2" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "LJx2eZSac5Ny" }, "outputs": [], "source": [ "#Task 3" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "CTdvQLajc5Nz" }, "outputs": [], "source": [ "#Task 4" ] }, { "cell_type": "markdown", "metadata": { "id": "u2gbY1IOck3y" }, "source": [ "### 3. Flight Delay Statistics\n", "\n", "| FlightID | Airline | Origin | Destination | DepartureTime | ArrivalTime | DelayMinutes |\n", "| -------- | -------- | ------ | ----------- | ------------- | ----------- | ------------ |\n", "| 1001 | Delta | JFK | LAX | 08:00 | 11:00 | 10 |\n", "| 1002 | United | LAX | ORD | 09:00 | 15:00 | 0 |\n", "| 1003 | Delta | JFK | ORD | 10:00 | 13:00 | 45 |\n", "| 1004 | American | ORD | LAX | 07:00 | 09:30 | 15 |\n", "| ... | ... | ... | ... | ... | ... | ... |\n", "\n", "---\n", "Tasks:\n", "\n", "1. Convert DepartureTime and ArrivalTime to datetime.time and calculate the flight duration in minutes.\n", "2. Compute the average delay per airline.\n", "3. Identify the top 2 origin airports with the highest average delay.\n", "4. Create a pivot table with Airline as rows, Origin as columns, and average delay as values." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "eBuD30bCeAAl" }, "outputs": [], "source": [ "flights_data = {\n", " 'FlightID': [1001, 1002, 1003, 1004],\n", " 'Airline': ['Delta', 'United', 'Delta', 'American'],\n", " 'Origin': ['JFK', 'LAX', 'JFK', 'ORD'],\n", " 'Destination': ['LAX', 'ORD', 'ORD', 'LAX'],\n", " 'DepartureTime': ['08:00', '09:00', '10:00', '07:00'],\n", " 'ArrivalTime': ['11:00', '15:00', '13:00', '09:30'],\n", " 'DelayMinutes': [10, 0, 45, 15]\n", "}\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "JFHM_Husc7yB" }, "outputs": [], "source": [ "#Task 1" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "vr7re1xUc7yC" }, "outputs": [], "source": [ "#Task 2" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "Qr0zF8kAc7yD" }, "outputs": [], "source": [ "#Task 3" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "Sw3dzszEc7yD" }, "outputs": [], "source": [ "#Task 4" ] } ], "metadata": { "colab": { "provenance": [] }, "kernelspec": { "display_name": "Python 3", "name": "python3" }, "language_info": { "name": "python" } }, "nbformat": 4, "nbformat_minor": 0 }