df-manipulation-pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----\n",
    "<div style=\"display: flex; align-items: center;\">\n",
    "    <img src=\"https://developers.lseg.com/content/dam/devportal/icons/logo/lseg-logo.svg\" width=\"20%\" style=\"vertical-align: top;\">\n",
    "</div>\n",
    "\n",
    "# [Dataframe Manipulation with Pandas, a Beginner's Guide](https://developers.lseg.com/en/article-catalog/article/dataframe-manipulation-with-pandas-a-beginners-guide)\n",
    "\n",
    "----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduction to Pandas and Dataframes\n",
    "\n",
    "[Pandas](https://pandas.pydata.org/), a very popular library in Python, helps us to work with data easily. You can think of it as a tool that allows us to play with data, like moving columns and rows in an Excel sheet. Pandas makes it easy to clean, modify, and analyze data, making it very useful in the data-related projects such as data science projects.\n",
    "\n",
    "[DataFrame](https://pandas.pydata.org/docs/reference/frame.html#dataframe) is like a table with rows and columns. It's similar to a table in SQL or an Excel spreadsheet, making it easy to work with structured data and allow you to organize data in a way that is easy to read and work with.\n",
    "\n",
    "## Setting Up Your Environment\n",
    "Here, I'm using **Python version 3.12.4** with Python libraries: **pandas version 2.2.2** and **lseg.data version 2.0.0** to retrieve the data.\n",
    "\n",
    "Pandas can be installed with the 'pip' command as below (Python and pip needed to be installed first). More detail of Pandas installation can be found in [this page](https://pandas.pydata.org/docs/getting_started/install.html)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Retrieving the data\n",
    "Data can be loaded into the DataFrame from different sources, such as importing it from CSV/Excel files, JSON data, SQL Database or retriving the data from any Python libraries.\n",
    "\n",
    "In this article, we're retrieving the data from [LSEG Data Library for Python](https://developers.lseg.com/en/api-catalog/lseg-data-platform/lseg-data-library-for-python), which provides a set of ease-of-use interfaces offering coders uniform access to the breadth and depth of financial data and services available on the LSEG Data Platform. The API is designed to provide consistent access through multiple access channels and target both Professional Developers and Financial Coders. Developers can choose to access content from the desktop, through their deployed streaming services, or directly to the cloud. With the LSEG Data Library, the same Python code can be used to retrieve data regardless of which access point you choose to connect to the platform.\n",
    "\n",
    "The example code can be found in [GitHub Example - Data Library Python](https://github.com/LSEG-API-Samples/Example.DataLibrary.Python/tree/lseg-data-examples), such as [EX-1.01.01-GetData.ipynb](https://github.com/LSEG-API-Samples/Example.DataLibrary.Python/blob/lseg-data-examples/Examples/1-Access/EX-1.01.01-GetData.ipynb). For example, let's retrieve data of MAMAA stocks (Meta, Amazon, Microsoft, Apple and Alphabet). To find the instruments and fields you're interested in, [Data Item Browser](https://developers.lseg.com/en/video-catalog/data-item-browser) can be used."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<lseg.data.session.Definition object at 0x20c82784310 {name='workspace'}>"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import lseg.data as ld\n",
    "import pandas as pd\n",
    "\n",
    "pd.set_option('display.max_rows', 10)\n",
    "\n",
    "ld.open_session()"
   ]
  },
  {
   "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>Instrument</th>\n",
       "      <th>Company Common Name</th>\n",
       "      <th>Company Also Known As Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>META.O</td>\n",
       "      <td>Meta Platforms Inc</td>\n",
       "      <td>Lian Shu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>META.O</td>\n",
       "      <td></td>\n",
       "      <td>Facebook</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>META.O</td>\n",
       "      <td></td>\n",
       "      <td>Meta Purattofomuzu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AMZN.O</td>\n",
       "      <td>Amazon.com Inc</td>\n",
       "      <td>Amazon Com Amzn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AMZN.O</td>\n",
       "      <td></td>\n",
       "      <td>Amazon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td></td>\n",
       "      <td>Ping Guo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td></td>\n",
       "      <td>Ping Guo Gong Si</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td></td>\n",
       "      <td>Ping Guo Dian Nao Gong Si</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td></td>\n",
       "      <td>Appuru</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>GOOGL.O</td>\n",
       "      <td>Alphabet Inc</td>\n",
       "      <td>Google</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>28 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Instrument Company Common Name Company Also Known As Name\n",
       "0      META.O  Meta Platforms Inc                   Lian Shu\n",
       "1      META.O                                       Facebook\n",
       "2      META.O                             Meta Purattofomuzu\n",
       "3      AMZN.O      Amazon.com Inc            Amazon Com Amzn\n",
       "4      AMZN.O                                         Amazon\n",
       "..        ...                 ...                        ...\n",
       "23     AAPL.O                                       Ping Guo\n",
       "24     AAPL.O                               Ping Guo Gong Si\n",
       "25     AAPL.O                      Ping Guo Dian Nao Gong Si\n",
       "26     AAPL.O                                         Appuru\n",
       "27    GOOGL.O        Alphabet Inc                     Google\n",
       "\n",
       "[28 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = ld.get_data(\n",
    "    universe=['META.O', 'AMZN.O', 'MSFT.O', 'AAPL.O', 'GOOGL.O'],\n",
    "    fields=['TR.CommonName',\n",
    "            'TR.AlsoKnownAsName']\n",
    ")\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1) Basic Dataframe Operations\n",
    "Basic operations that are commonly used to explore and understand data by viewing, selecting, and filtering data.\n",
    "\n",
    "#### 1.1 ) Viewing Data:\n",
    "\n",
    "1.1.1 ) See the first few rows of a dataframe using 'head()' - default is 5 rows and you can put the number of rows of data you want to get as DataFrame.head(n)\n",
    "    - If n is negative value, the function returns all rows except the last |n| rows\n",
    "    - If n is larger than the number of rows, this function returns all rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Instrument</th>\n",
       "      <th>Company Common Name</th>\n",
       "      <th>Company Also Known As Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>META.O</td>\n",
       "      <td>Meta Platforms Inc</td>\n",
       "      <td>Lian Shu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>META.O</td>\n",
       "      <td></td>\n",
       "      <td>Facebook</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>META.O</td>\n",
       "      <td></td>\n",
       "      <td>Meta Purattofomuzu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AMZN.O</td>\n",
       "      <td>Amazon.com Inc</td>\n",
       "      <td>Amazon Com Amzn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AMZN.O</td>\n",
       "      <td></td>\n",
       "      <td>Amazon</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Instrument Company Common Name Company Also Known As Name\n",
       "0     META.O  Meta Platforms Inc                   Lian Shu\n",
       "1     META.O                                       Facebook\n",
       "2     META.O                             Meta Purattofomuzu\n",
       "3     AMZN.O      Amazon.com Inc            Amazon Com Amzn\n",
       "4     AMZN.O                                         Amazon"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Instrument</th>\n",
       "      <th>Company Common Name</th>\n",
       "      <th>Company Also Known As Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>META.O</td>\n",
       "      <td>Meta Platforms Inc</td>\n",
       "      <td>Lian Shu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>META.O</td>\n",
       "      <td></td>\n",
       "      <td>Facebook</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Instrument Company Common Name Company Also Known As Name\n",
       "0     META.O  Meta Platforms Inc                   Lian Shu\n",
       "1     META.O                                       Facebook"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1.1.2) Similar to head, to retrieve the last few rows of data, 'tail()' can be used"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Instrument</th>\n",
       "      <th>Company Common Name</th>\n",
       "      <th>Company Also Known As Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td></td>\n",
       "      <td>Ping Guo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td></td>\n",
       "      <td>Ping Guo Gong Si</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td></td>\n",
       "      <td>Ping Guo Dian Nao Gong Si</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td></td>\n",
       "      <td>Appuru</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>GOOGL.O</td>\n",
       "      <td>Alphabet Inc</td>\n",
       "      <td>Google</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Instrument Company Common Name Company Also Known As Name\n",
       "23     AAPL.O                                       Ping Guo\n",
       "24     AAPL.O                               Ping Guo Gong Si\n",
       "25     AAPL.O                      Ping Guo Dian Nao Gong Si\n",
       "26     AAPL.O                                         Appuru\n",
       "27    GOOGL.O        Alphabet Inc                     Google"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1.2) Selecting Data:\n",
    "1.2.1 ) Select specific column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                      Lian Shu\n",
       "1                      Facebook\n",
       "2            Meta Purattofomuzu\n",
       "3               Amazon Com Amzn\n",
       "4                        Amazon\n",
       "                ...            \n",
       "23                     Ping Guo\n",
       "24             Ping Guo Gong Si\n",
       "25    Ping Guo Dian Nao Gong Si\n",
       "26                       Appuru\n",
       "27                       Google\n",
       "Name: Company Also Known As Name, Length: 28, dtype: string"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Company Also Known As Name']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1.2.2) Select multiple columns, use list of column as an input"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Instrument</th>\n",
       "      <th>Company Also Known As Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>META.O</td>\n",
       "      <td>Lian Shu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>META.O</td>\n",
       "      <td>Facebook</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>META.O</td>\n",
       "      <td>Meta Purattofomuzu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AMZN.O</td>\n",
       "      <td>Amazon Com Amzn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AMZN.O</td>\n",
       "      <td>Amazon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td>Ping Guo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td>Ping Guo Gong Si</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td>Ping Guo Dian Nao Gong Si</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td>Appuru</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>GOOGL.O</td>\n",
       "      <td>Google</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>28 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Instrument Company Also Known As Name\n",
       "0      META.O                   Lian Shu\n",
       "1      META.O                   Facebook\n",
       "2      META.O         Meta Purattofomuzu\n",
       "3      AMZN.O            Amazon Com Amzn\n",
       "4      AMZN.O                     Amazon\n",
       "..        ...                        ...\n",
       "23     AAPL.O                   Ping Guo\n",
       "24     AAPL.O           Ping Guo Gong Si\n",
       "25     AAPL.O  Ping Guo Dian Nao Gong Si\n",
       "26     AAPL.O                     Appuru\n",
       "27    GOOGL.O                     Google\n",
       "\n",
       "[28 rows x 2 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['Instrument', 'Company Also Known As Name']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1.2.3) Select rows using **'loc[]'** (label-based) and **'iloc[]'** (integer-based) indexing\n",
    "- Input of **loc[]** is  a single label, e.g. 5 or 'a',\n",
    "  *(5 is interpreted as a label of the index, and never as an integer position along the index)*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Instrument                                META.O\n",
       "Company Common Name                             \n",
       "Company Also Known As Name    Meta Purattofomuzu\n",
       "Name: 2, dtype: string"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Label of the index = 2\n",
    "df.loc[2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Input of **iloc[]** is an integer, e.g. 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Instrument                                META.O\n",
       "Company Common Name                             \n",
       "Company Also Known As Name    Meta Purattofomuzu\n",
       "Name: 2, dtype: string"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Integer position of the index = 2\n",
    "df.iloc[2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1.3) Filtering Data:\n",
    "Rows can be filtered based on conditions, such as, to select the row that has value of the columm larger than *n*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Instrument</th>\n",
       "      <th>BID</th>\n",
       "      <th>ASK</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>META.O</td>\n",
       "      <td>514.0</td>\n",
       "      <td>514.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AMZN.O</td>\n",
       "      <td>175.84</td>\n",
       "      <td>175.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MSFT.O</td>\n",
       "      <td>411.01</td>\n",
       "      <td>411.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td>220.03</td>\n",
       "      <td>220.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GOOGL.O</td>\n",
       "      <td>158.34</td>\n",
       "      <td>158.35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Instrument     BID     ASK\n",
       "0     META.O   514.0  514.19\n",
       "1     AMZN.O  175.84  175.86\n",
       "2     MSFT.O  411.01  411.06\n",
       "3     AAPL.O  220.03  220.05\n",
       "4    GOOGL.O  158.34  158.35"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = ld.get_data(\n",
    "    universe=['META.O', 'AMZN.O', 'MSFT.O', 'AAPL.O', 'GOOGL.O'],\n",
    "    fields=['BID','ASK']\n",
    ")\n",
    "display(df)"
   ]
  },
  {
   "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>Instrument</th>\n",
       "      <th>BID</th>\n",
       "      <th>ASK</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>META.O</td>\n",
       "      <td>514.0</td>\n",
       "      <td>514.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MSFT.O</td>\n",
       "      <td>411.01</td>\n",
       "      <td>411.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td>220.03</td>\n",
       "      <td>220.05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Instrument     BID     ASK\n",
       "0     META.O   514.0  514.19\n",
       "2     MSFT.O  411.01  411.06\n",
       "3     AAPL.O  220.03  220.05"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['BID'] > 200]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1.4) Changing Column Names:\n",
    "Column names of a dataframe can be changed by assigning a new list of names to 'df.columns', or using the 'rename()' method to rename specific columns.\n",
    "\n",
    "1.4.1 ) Renaming All Columns, replaces all column names with a new list of names, use the code below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>RIC</th>\n",
       "      <th>Bid Price</th>\n",
       "      <th>Ask Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>META.O</td>\n",
       "      <td>514.0</td>\n",
       "      <td>514.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AMZN.O</td>\n",
       "      <td>175.84</td>\n",
       "      <td>175.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MSFT.O</td>\n",
       "      <td>411.01</td>\n",
       "      <td>411.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td>220.03</td>\n",
       "      <td>220.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GOOGL.O</td>\n",
       "      <td>158.34</td>\n",
       "      <td>158.35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       RIC  Bid Price  Ask Price\n",
       "0   META.O      514.0     514.19\n",
       "1   AMZN.O     175.84     175.86\n",
       "2   MSFT.O     411.01     411.06\n",
       "3   AAPL.O     220.03     220.05\n",
       "4  GOOGL.O     158.34     158.35"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df.columns = ['RIC', 'Bid Price', 'Ask Price']\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1.4.2) Renaming Specific Columns, if you only want to rename one or a few columns, use 'rename()'\n",
    "\n",
    "*Please note that 'inplace=True' updates the edited dataframe itself (the default value of this parameter is False, which will not update the original dataframe, but the dataframe with changes needs to be assigned to the new variable of dataframe)*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = ld.get_data(\n",
    "    universe=['META.O', 'AMZN.O', 'MSFT.O', 'AAPL.O', 'GOOGL.O'],\n",
    "    fields=['BID','ASK']\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>Instrument</th>\n",
       "      <th>Bid Price</th>\n",
       "      <th>Ask Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>META.O</td>\n",
       "      <td>514.0</td>\n",
       "      <td>514.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AMZN.O</td>\n",
       "      <td>175.84</td>\n",
       "      <td>175.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MSFT.O</td>\n",
       "      <td>411.01</td>\n",
       "      <td>411.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td>220.06</td>\n",
       "      <td>220.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GOOGL.O</td>\n",
       "      <td>158.34</td>\n",
       "      <td>158.36</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Instrument  Bid Price  Ask Price\n",
       "0     META.O      514.0     514.07\n",
       "1     AMZN.O     175.84     175.86\n",
       "2     MSFT.O     411.01     411.06\n",
       "3     AAPL.O     220.06     220.09\n",
       "4    GOOGL.O     158.34     158.36"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df.rename(columns={'BID': 'Bid Price', 'ASK': 'Ask Price'}, inplace=True)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2) Data Cleaning with Pandas\n",
    "2.1 ) Handling missing data using 'dropna()'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>MSFT.O</th>\n",
       "      <th>BID</th>\n",
       "      <th>ASK</th>\n",
       "      <th>VWAP_VOL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-08-14</th>\n",
       "      <td>416.61</td>\n",
       "      <td>416.71</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-15</th>\n",
       "      <td>421.05</td>\n",
       "      <td>421.1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-16</th>\n",
       "      <td>418.54</td>\n",
       "      <td>418.59</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-19</th>\n",
       "      <td>421.51</td>\n",
       "      <td>421.53</td>\n",
       "      <td>12343531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-20</th>\n",
       "      <td>424.86</td>\n",
       "      <td>424.87</td>\n",
       "      <td>12347266</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "MSFT.O         BID     ASK  VWAP_VOL\n",
       "Date                                \n",
       "2024-08-14  416.61  416.71      <NA>\n",
       "2024-08-15  421.05   421.1      <NA>\n",
       "2024-08-16  418.54  418.59      <NA>\n",
       "2024-08-19  421.51  421.53  12343531\n",
       "2024-08-20  424.86  424.87  12347266"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = ld.get_history(\n",
    "    universe=['MSFT.O'],\n",
    "    fields=['BID', 'ASK', 'VWAP_VOL'],\n",
    "    start='2024-08-13',\n",
    "    end='2024-08-20'\n",
    ")\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>MSFT.O</th>\n",
       "      <th>BID</th>\n",
       "      <th>ASK</th>\n",
       "      <th>VWAP_VOL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-08-19</th>\n",
       "      <td>421.51</td>\n",
       "      <td>421.53</td>\n",
       "      <td>12343531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-20</th>\n",
       "      <td>424.86</td>\n",
       "      <td>424.87</td>\n",
       "      <td>12347266</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "MSFT.O         BID     ASK  VWAP_VOL\n",
       "Date                                \n",
       "2024-08-19  421.51  421.53  12343531\n",
       "2024-08-20  424.86  424.87  12347266"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_cleaned = df.dropna()\n",
    "display(df_cleaned)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2.2) Filling missing value with 'fillna()'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>MSFT.O</th>\n",
       "      <th>BID</th>\n",
       "      <th>ASK</th>\n",
       "      <th>VWAP_VOL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-08-14</th>\n",
       "      <td>416.61</td>\n",
       "      <td>416.71</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-15</th>\n",
       "      <td>421.05</td>\n",
       "      <td>421.1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-16</th>\n",
       "      <td>418.54</td>\n",
       "      <td>418.59</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-19</th>\n",
       "      <td>421.51</td>\n",
       "      <td>421.53</td>\n",
       "      <td>12343531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-20</th>\n",
       "      <td>424.86</td>\n",
       "      <td>424.87</td>\n",
       "      <td>12347266</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "MSFT.O         BID     ASK  VWAP_VOL\n",
       "Date                                \n",
       "2024-08-14  416.61  416.71         0\n",
       "2024-08-15  421.05   421.1         0\n",
       "2024-08-16  418.54  418.59         0\n",
       "2024-08-19  421.51  421.53  12343531\n",
       "2024-08-20  424.86  424.87  12347266"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_filled = df.fillna(0) # Fill with 0\n",
    "display(df_filled)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2.3) Removing duplicates by using 'drop_duplicates()' to remove duplicate rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>MSFT.O</th>\n",
       "      <th>BID</th>\n",
       "      <th>ASK</th>\n",
       "      <th>VWAP_VOL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-08-14</th>\n",
       "      <td>416.61</td>\n",
       "      <td>416.71</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-19</th>\n",
       "      <td>421.51</td>\n",
       "      <td>421.53</td>\n",
       "      <td>12343531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-20</th>\n",
       "      <td>424.86</td>\n",
       "      <td>424.87</td>\n",
       "      <td>12347266</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "MSFT.O         BID     ASK  VWAP_VOL\n",
       "Date                                \n",
       "2024-08-14  416.61  416.71      <NA>\n",
       "2024-08-19  421.51  421.53  12343531\n",
       "2024-08-20  424.86  424.87  12347266"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_unique = df.drop_duplicates('VWAP_VOL')\n",
    "display(df_unique)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2.4) Changing data types of column using 'astype()'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>MSFT.O</th>\n",
       "      <th>BID</th>\n",
       "      <th>ASK</th>\n",
       "      <th>VWAP_VOL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-08-14</th>\n",
       "      <td>416</td>\n",
       "      <td>416.71</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-15</th>\n",
       "      <td>421</td>\n",
       "      <td>421.1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-16</th>\n",
       "      <td>418</td>\n",
       "      <td>418.59</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-19</th>\n",
       "      <td>421</td>\n",
       "      <td>421.53</td>\n",
       "      <td>12343531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-20</th>\n",
       "      <td>424</td>\n",
       "      <td>424.87</td>\n",
       "      <td>12347266</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "MSFT.O      BID     ASK  VWAP_VOL\n",
       "Date                             \n",
       "2024-08-14  416  416.71      <NA>\n",
       "2024-08-15  421   421.1      <NA>\n",
       "2024-08-16  418  418.59      <NA>\n",
       "2024-08-19  421  421.53  12343531\n",
       "2024-08-20  424  424.87  12347266"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df['BID'] = df['BID'].astype(int)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3) Transforming Data\n",
    "Change data by creating new columns, applying function, and grouping data\n",
    "\n",
    "3.1 ) Adding new columns based on other columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>PTT.BK</th>\n",
       "      <th>LOW_1</th>\n",
       "      <th>HIGH_1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-08-02</th>\n",
       "      <td>31.75</td>\n",
       "      <td>32.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-05</th>\n",
       "      <td>31.25</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-06</th>\n",
       "      <td>31.25</td>\n",
       "      <td>31.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "PTT.BK      LOW_1  HIGH_1\n",
       "Date                     \n",
       "2024-08-02  31.75   32.25\n",
       "2024-08-05  31.25    32.0\n",
       "2024-08-06  31.25   31.75"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = ld.get_history(\n",
    "    universe=['PTT.BK'],\n",
    "    fields=['LOW_1', 'HIGH_1'],\n",
    "    start = '2024-08-01',\n",
    "    end = '2024-08-06'\n",
    ")\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>PTT.BK</th>\n",
       "      <th>LOW_1</th>\n",
       "      <th>HIGH_1</th>\n",
       "      <th>Sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-08-02</th>\n",
       "      <td>31.75</td>\n",
       "      <td>32.25</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-05</th>\n",
       "      <td>31.25</td>\n",
       "      <td>32.0</td>\n",
       "      <td>63.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-06</th>\n",
       "      <td>31.25</td>\n",
       "      <td>31.75</td>\n",
       "      <td>63.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "PTT.BK      LOW_1  HIGH_1    Sum\n",
       "Date                            \n",
       "2024-08-02  31.75   32.25   64.0\n",
       "2024-08-05  31.25    32.0  63.25\n",
       "2024-08-06  31.25   31.75   63.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df['Sum'] = df['LOW_1'] + df['HIGH_1']\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.2) Applying functions to a column using 'apply()'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = ld.get_history(\n",
    "    universe=['PTT.BK'],\n",
    "    fields=['LOW_1', 'HIGH_1'],\n",
    "    start = '2024-08-01',\n",
    "    end = '2024-08-06'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>PTT.BK</th>\n",
       "      <th>LOW_1</th>\n",
       "      <th>HIGH_1</th>\n",
       "      <th>plus 10</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-08-02</th>\n",
       "      <td>31.75</td>\n",
       "      <td>32.25</td>\n",
       "      <td>42.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-05</th>\n",
       "      <td>31.25</td>\n",
       "      <td>32.0</td>\n",
       "      <td>42.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-06</th>\n",
       "      <td>31.25</td>\n",
       "      <td>31.75</td>\n",
       "      <td>41.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "PTT.BK      LOW_1  HIGH_1  plus 10\n",
       "Date                              \n",
       "2024-08-02  31.75   32.25    42.25\n",
       "2024-08-05  31.25    32.0    42.00\n",
       "2024-08-06  31.25   31.75    41.75"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df['plus 10'] = df['HIGH_1'].apply(lambda x: x + 10)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.3) Grouping data and perform calculations using 'groupby()'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = ld.get_history(\n",
    "    universe=['PTT.BK'],\n",
    "    fields=['LOW_1', 'HIGH_1'],\n",
    "    start = '2024-08-01',\n",
    "    end = '2024-08-06'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>PTT.BK</th>\n",
       "      <th>HIGH_1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LOW_1</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>31.25</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31.75</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "PTT.BK  HIGH_1\n",
       "LOW_1         \n",
       "31.25        2\n",
       "31.75        1"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "grouped = df.groupby('LOW_1').count()\n",
    "display(grouped)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4) Joining and Merging Dataframes\n",
    "While working with multiple datasets, it's important to know how to combine dataframes \n",
    "\n",
    "4.1 ) Combine dataframes vertically or horizontally using 'concat()'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>MSFT.O</th>\n",
       "      <th>BID</th>\n",
       "      <th>ASK</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-08-02</th>\n",
       "      <td>408.46</td>\n",
       "      <td>408.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-05</th>\n",
       "      <td>395.25</td>\n",
       "      <td>395.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-06</th>\n",
       "      <td>399.41</td>\n",
       "      <td>399.44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "MSFT.O         BID     ASK\n",
       "Date                      \n",
       "2024-08-02  408.46   408.5\n",
       "2024-08-05  395.25  395.32\n",
       "2024-08-06  399.41  399.44"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>LSEG.L</th>\n",
       "      <th>BID</th>\n",
       "      <th>ASK</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-08-02</th>\n",
       "      <td>9740</td>\n",
       "      <td>9742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-05</th>\n",
       "      <td>9584</td>\n",
       "      <td>9588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-06</th>\n",
       "      <td>9514</td>\n",
       "      <td>9516</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "LSEG.L       BID   ASK\n",
       "Date                  \n",
       "2024-08-02  9740  9742\n",
       "2024-08-05  9584  9588\n",
       "2024-08-06  9514  9516"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df1 = ld.get_history(\n",
    "    universe=['MSFT.O'],\n",
    "    fields=['BID', 'ASK'],\n",
    "    start = '2024-08-01',\n",
    "    end = '2024-08-06'\n",
    ")\n",
    "\n",
    "df2 = ld.get_history(\n",
    "    universe=['LSEG.L'],\n",
    "    fields=['BID', 'ASK'],\n",
    "    start = '2024-08-01',\n",
    "    end = '2024-08-06'\n",
    ")\n",
    "\n",
    "display(df1)\n",
    "display(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>BID</th>\n",
       "      <th>ASK</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-08-02</th>\n",
       "      <td>408.46</td>\n",
       "      <td>408.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-05</th>\n",
       "      <td>395.25</td>\n",
       "      <td>395.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-06</th>\n",
       "      <td>399.41</td>\n",
       "      <td>399.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-02</th>\n",
       "      <td>9740.0</td>\n",
       "      <td>9742.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-05</th>\n",
       "      <td>9584.0</td>\n",
       "      <td>9588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-06</th>\n",
       "      <td>9514.0</td>\n",
       "      <td>9516.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               BID     ASK\n",
       "Date                      \n",
       "2024-08-02  408.46   408.5\n",
       "2024-08-05  395.25  395.32\n",
       "2024-08-06  399.41  399.44\n",
       "2024-08-02  9740.0  9742.0\n",
       "2024-08-05  9584.0  9588.0\n",
       "2024-08-06  9514.0  9516.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "concat_df = pd.concat([df1, df2])\n",
    "display(concat_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4.2) Joining dataframes with 'join()' for joining them on the index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>MSFT.O</th>\n",
       "      <th>BID</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-08-02</th>\n",
       "      <td>408.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-05</th>\n",
       "      <td>395.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-06</th>\n",
       "      <td>399.41</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "MSFT.O         BID\n",
       "Date              \n",
       "2024-08-02  408.46\n",
       "2024-08-05  395.25\n",
       "2024-08-06  399.41"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>MSFT.O</th>\n",
       "      <th>ASK</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-08-02</th>\n",
       "      <td>408.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-05</th>\n",
       "      <td>395.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-06</th>\n",
       "      <td>399.44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "MSFT.O         ASK\n",
       "Date              \n",
       "2024-08-02   408.5\n",
       "2024-08-05  395.32\n",
       "2024-08-06  399.44"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df1 = ld.get_history(\n",
    "    universe=['MSFT.O'],\n",
    "    fields=['BID'],\n",
    "    start = '2024-08-01',\n",
    "    end = '2024-08-06'\n",
    ")\n",
    "df2 = ld.get_history(\n",
    "    universe=['MSFT.O'],\n",
    "    fields=['ASK'],\n",
    "    start = '2024-08-01',\n",
    "    end = '2024-08-06'\n",
    ")\n",
    "\n",
    "display(df1)\n",
    "display(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>MSFT.O</th>\n",
       "      <th>BID</th>\n",
       "      <th>ASK</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-08-02</th>\n",
       "      <td>408.46</td>\n",
       "      <td>408.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-05</th>\n",
       "      <td>395.25</td>\n",
       "      <td>395.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-06</th>\n",
       "      <td>399.41</td>\n",
       "      <td>399.44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "MSFT.O         BID     ASK\n",
       "Date                      \n",
       "2024-08-02  408.46   408.5\n",
       "2024-08-05  395.25  395.32\n",
       "2024-08-06  399.41  399.44"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "joined_df = df1.join(df2)\n",
    "display(joined_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4.3) Merging dataframes based on a key column using 'merge()'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>MSFT.O</th>\n",
       "      <th>BID</th>\n",
       "      <th>ASK</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-08-02</th>\n",
       "      <td>408.46</td>\n",
       "      <td>408.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-05</th>\n",
       "      <td>395.25</td>\n",
       "      <td>395.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-06</th>\n",
       "      <td>399.41</td>\n",
       "      <td>399.44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "MSFT.O         BID     ASK\n",
       "Date                      \n",
       "2024-08-02  408.46   408.5\n",
       "2024-08-05  395.25  395.32\n",
       "2024-08-06  399.41  399.44"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "merged_df = pd.merge(df1, df2, on='Date')\n",
    "display(merged_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5) Sorting and Ranking Data\n",
    "Sort and rank data within a dataframe\n",
    "\n",
    "5.1 ) Sort by specific columns using 'sort_values()'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>MSFT.O</th>\n",
       "      <th>BID</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-08-06</th>\n",
       "      <td>399.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-07</th>\n",
       "      <td>398.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-08</th>\n",
       "      <td>402.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-09</th>\n",
       "      <td>406.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-12</th>\n",
       "      <td>406.92</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "MSFT.O         BID\n",
       "Date              \n",
       "2024-08-06  399.41\n",
       "2024-08-07  398.26\n",
       "2024-08-08  402.93\n",
       "2024-08-09  406.02\n",
       "2024-08-12  406.92"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = ld.get_history(\n",
    "    universe=['MSFT.O'],\n",
    "    fields=['BID'],\n",
    "    start = '2024-08-05',\n",
    "    end = '2024-08-12'\n",
    ")\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>MSFT.O</th>\n",
       "      <th>BID</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-08-07</th>\n",
       "      <td>398.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-06</th>\n",
       "      <td>399.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-08</th>\n",
       "      <td>402.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-09</th>\n",
       "      <td>406.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-12</th>\n",
       "      <td>406.92</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "MSFT.O         BID\n",
       "Date              \n",
       "2024-08-07  398.26\n",
       "2024-08-06  399.41\n",
       "2024-08-08  402.93\n",
       "2024-08-09  406.02\n",
       "2024-08-12  406.92"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_sorted = df.sort_values(by='BID')\n",
    "display(df_sorted)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5.2) Ranking with 'rank()'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>MSFT.O</th>\n",
       "      <th>BID</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-08-06</th>\n",
       "      <td>399.41</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-07</th>\n",
       "      <td>398.26</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-08</th>\n",
       "      <td>402.93</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-09</th>\n",
       "      <td>406.02</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-08-12</th>\n",
       "      <td>406.92</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "MSFT.O         BID  rank\n",
       "Date                    \n",
       "2024-08-06  399.41   2.0\n",
       "2024-08-07  398.26   1.0\n",
       "2024-08-08  402.93   3.0\n",
       "2024-08-09  406.02   4.0\n",
       "2024-08-12  406.92   5.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df['rank'] = df['BID'].rank()\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6) Advanced Dataframe Manipulations\n",
    "In this section, let's explore more complex dataframe manipulations that can help us reshape and aggregate data in different ways. These techniques are useful when we want to pivot data or change the layout of our dataframe for specific analyzes.\n",
    "\n",
    "6.1 ) Creating pivot tables with 'pivot_table()'. This allows us to summarize data and group it in various ways with ease.\n",
    "Imagine we have the dataframe that contains last 3 years of revenue data of these 3 companies and we want to find out the total revenue of each company. The pivot table can be created like this.\n",
    "- Index='Instrument' specifies that the rows should be grouped by the 'Instrument' column\n",
    "- columns='Date' creates separate columns for each 'Date'\n",
    "- values='Revenue' indicates that the values in the table should be from 'Revenue' column\n",
    "- aggfunc='sum' sums up the revenue for each combination of instrument and date. This can also be changed to other functions like 'mean', 'min', or 'max' based on what is needed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>Instrument</th>\n",
       "      <th>Date</th>\n",
       "      <th>Revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>META.O</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>134902000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>META.O</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>116609000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>META.O</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>117929000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AMZN.O</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>574785000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AMZN.O</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>513983000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AMZN.O</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>469822000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>GOOGL.O</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>307394000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>GOOGL.O</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>282836000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>GOOGL.O</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>257637000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Instrument       Date       Revenue\n",
       "0     META.O 2023-12-31  134902000000\n",
       "1     META.O 2022-12-31  116609000000\n",
       "2     META.O 2021-12-31  117929000000\n",
       "3     AMZN.O 2023-12-31  574785000000\n",
       "4     AMZN.O 2022-12-31  513983000000\n",
       "5     AMZN.O 2021-12-31  469822000000\n",
       "6    GOOGL.O 2023-12-31  307394000000\n",
       "7    GOOGL.O 2022-12-31  282836000000\n",
       "8    GOOGL.O 2021-12-31  257637000000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = ld.get_data(['META.O', 'AMZN.O', 'GOOGL.O'],\n",
    "                 ['TR.Revenue.date', 'TR.Revenue'],\n",
    "                 {'SDate':'0', 'EDate': '-2',\n",
    "                  'Period': 'FY0', 'Frq': 'FY'})\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>Date</th>\n",
       "      <th>2021-12-31</th>\n",
       "      <th>2022-12-31</th>\n",
       "      <th>2023-12-31</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Instrument</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AMZN.O</th>\n",
       "      <td>469822000000</td>\n",
       "      <td>513983000000</td>\n",
       "      <td>574785000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GOOGL.O</th>\n",
       "      <td>257637000000</td>\n",
       "      <td>282836000000</td>\n",
       "      <td>307394000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>META.O</th>\n",
       "      <td>117929000000</td>\n",
       "      <td>116609000000</td>\n",
       "      <td>134902000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Date          2021-12-31    2022-12-31    2023-12-31\n",
       "Instrument                                          \n",
       "AMZN.O      469822000000  513983000000  574785000000\n",
       "GOOGL.O     257637000000  282836000000  307394000000\n",
       "META.O      117929000000  116609000000  134902000000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "pivot = df.pivot_table(index='Instrument',\n",
    "                       columns='Date',\n",
    "                       values='Revenue',\n",
    "                       aggfunc='sum')\n",
    "display(pivot)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "6.2) Reshaping data with 'melt()' to change the format. This is useful to tranform the data from wide format (many columns) to a long format (fewer columns, more rows). This is often required when we need to prepare the data for plotting or more advanced analysis.\n",
    "\n",
    "Let's say we have the dataframe which each column represents a total return cross asset on 1, 3, and 5 years period then we we're transforming it into a long format where each row represents total return cross asset for a specific instrument in a specific number of years period, as below.\n",
    "- id_vars=['Instrument'], this column will remain unchanged, meaning the instrument RIC will stay the same for each row.\n",
    "- value_vars=[''1 Year Total Return Cross Asset, ... , '5 Year Total Return Cross Asset'], are the columns that will be transformed into rows.\n",
    "- var_name='Year' is the column name for the melted variable (inthis case, the year).\n",
    "- value_name='Total Return Cross Asset', is the name of the new column where the values will be sorted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>Instrument</th>\n",
       "      <th>1 Year Total Return Cross Asset</th>\n",
       "      <th>3 Year Total Return Cross Asset</th>\n",
       "      <th>5 Year Total Return Cross Asset</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>META.O</td>\n",
       "      <td>73.026114</td>\n",
       "      <td>36.292669</td>\n",
       "      <td>181.163878</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AMZN.O</td>\n",
       "      <td>27.606429</td>\n",
       "      <td>1.349894</td>\n",
       "      <td>96.944978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MSFT.O</td>\n",
       "      <td>25.504841</td>\n",
       "      <td>39.472125</td>\n",
       "      <td>215.11916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td>18.179222</td>\n",
       "      <td>46.787027</td>\n",
       "      <td>347.655369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GOOGL.O</td>\n",
       "      <td>16.128431</td>\n",
       "      <td>9.600931</td>\n",
       "      <td>169.402471</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Instrument  1 Year Total Return Cross Asset  \\\n",
       "0     META.O                        73.026114   \n",
       "1     AMZN.O                        27.606429   \n",
       "2     MSFT.O                        25.504841   \n",
       "3     AAPL.O                        18.179222   \n",
       "4    GOOGL.O                        16.128431   \n",
       "\n",
       "   3 Year Total Return Cross Asset  5 Year Total Return Cross Asset  \n",
       "0                        36.292669                       181.163878  \n",
       "1                         1.349894                        96.944978  \n",
       "2                        39.472125                        215.11916  \n",
       "3                        46.787027                       347.655369  \n",
       "4                         9.600931                       169.402471  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = ld.get_data(['META.O', 'AMZN.O', 'MSFT.O', 'AAPL.O', 'GOOGL.O'],\n",
    "                 ['TR.TotalReturn1YrCrossAsset', 'TR.TotalReturn3YrCrossAsset', 'TR.TotalReturn5YrCrossAsset'])\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>Instrument</th>\n",
       "      <th>Year</th>\n",
       "      <th>Total Return Cross Asset</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>META.O</td>\n",
       "      <td>1 Year Total Return Cross Asset</td>\n",
       "      <td>73.026114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AMZN.O</td>\n",
       "      <td>1 Year Total Return Cross Asset</td>\n",
       "      <td>27.606429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MSFT.O</td>\n",
       "      <td>1 Year Total Return Cross Asset</td>\n",
       "      <td>25.504841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td>1 Year Total Return Cross Asset</td>\n",
       "      <td>18.179222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GOOGL.O</td>\n",
       "      <td>1 Year Total Return Cross Asset</td>\n",
       "      <td>16.128431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>META.O</td>\n",
       "      <td>5 Year Total Return Cross Asset</td>\n",
       "      <td>181.163878</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>AMZN.O</td>\n",
       "      <td>5 Year Total Return Cross Asset</td>\n",
       "      <td>96.944978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>MSFT.O</td>\n",
       "      <td>5 Year Total Return Cross Asset</td>\n",
       "      <td>215.11916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td>5 Year Total Return Cross Asset</td>\n",
       "      <td>347.655369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>GOOGL.O</td>\n",
       "      <td>5 Year Total Return Cross Asset</td>\n",
       "      <td>169.402471</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Instrument                             Year  Total Return Cross Asset\n",
       "0      META.O  1 Year Total Return Cross Asset                 73.026114\n",
       "1      AMZN.O  1 Year Total Return Cross Asset                 27.606429\n",
       "2      MSFT.O  1 Year Total Return Cross Asset                 25.504841\n",
       "3      AAPL.O  1 Year Total Return Cross Asset                 18.179222\n",
       "4     GOOGL.O  1 Year Total Return Cross Asset                 16.128431\n",
       "..        ...                              ...                       ...\n",
       "10     META.O  5 Year Total Return Cross Asset                181.163878\n",
       "11     AMZN.O  5 Year Total Return Cross Asset                 96.944978\n",
       "12     MSFT.O  5 Year Total Return Cross Asset                 215.11916\n",
       "13     AAPL.O  5 Year Total Return Cross Asset                347.655369\n",
       "14    GOOGL.O  5 Year Total Return Cross Asset                169.402471\n",
       "\n",
       "[15 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "melted_df = pd.melt(df, id_vars=['Instrument'],\n",
    "                    value_vars=['1 Year Total Return Cross Asset', '3 Year Total Return Cross Asset', '5 Year Total Return Cross Asset'],\n",
    "                    var_name='Year', value_name='Total Return Cross Asset')\n",
    "display(melted_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "6.3) Reshaping data with 'stack()' and 'unstack()'\n",
    "- stack() function is used to pivot the columns into rows\n",
    "- unstack() function does the opposite, which pivots the rows into columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>Instrument</th>\n",
       "      <th>1 Year Total Return Cross Asset</th>\n",
       "      <th>3 Year Total Return Cross Asset</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>META.O</td>\n",
       "      <td>73.026114</td>\n",
       "      <td>36.292669</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AMZN.O</td>\n",
       "      <td>27.606429</td>\n",
       "      <td>1.349894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MSFT.O</td>\n",
       "      <td>25.504841</td>\n",
       "      <td>39.472125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAPL.O</td>\n",
       "      <td>18.179222</td>\n",
       "      <td>46.787027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GOOGL.O</td>\n",
       "      <td>16.128431</td>\n",
       "      <td>9.600931</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Instrument  1 Year Total Return Cross Asset  3 Year Total Return Cross Asset\n",
       "0     META.O                        73.026114                        36.292669\n",
       "1     AMZN.O                        27.606429                         1.349894\n",
       "2     MSFT.O                        25.504841                        39.472125\n",
       "3     AAPL.O                        18.179222                        46.787027\n",
       "4    GOOGL.O                        16.128431                         9.600931"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = ld.get_data(['META.O', 'AMZN.O', 'MSFT.O', 'AAPL.O', 'GOOGL.O'],\n",
    "                 ['TR.TotalReturn1YrCrossAsset', 'TR.TotalReturn3YrCrossAsset'])\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Instrument                                 \n",
       "META.O      1 Year Total Return Cross Asset    73.026114\n",
       "            3 Year Total Return Cross Asset    36.292669\n",
       "AMZN.O      1 Year Total Return Cross Asset    27.606429\n",
       "            3 Year Total Return Cross Asset     1.349894\n",
       "MSFT.O      1 Year Total Return Cross Asset    25.504841\n",
       "            3 Year Total Return Cross Asset    39.472125\n",
       "AAPL.O      1 Year Total Return Cross Asset    18.179222\n",
       "            3 Year Total Return Cross Asset    46.787027\n",
       "GOOGL.O     1 Year Total Return Cross Asset    16.128431\n",
       "            3 Year Total Return Cross Asset     9.600931\n",
       "dtype: Float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "stacked = df.set_index('Instrument').stack()\n",
    "display(stacked)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>1 Year Total Return Cross Asset</th>\n",
       "      <th>3 Year Total Return Cross Asset</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Instrument</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>META.O</th>\n",
       "      <td>73.026114</td>\n",
       "      <td>36.292669</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AMZN.O</th>\n",
       "      <td>27.606429</td>\n",
       "      <td>1.349894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MSFT.O</th>\n",
       "      <td>25.504841</td>\n",
       "      <td>39.472125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AAPL.O</th>\n",
       "      <td>18.179222</td>\n",
       "      <td>46.787027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GOOGL.O</th>\n",
       "      <td>16.128431</td>\n",
       "      <td>9.600931</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            1 Year Total Return Cross Asset  3 Year Total Return Cross Asset\n",
       "Instrument                                                                  \n",
       "META.O                            73.026114                        36.292669\n",
       "AMZN.O                            27.606429                         1.349894\n",
       "MSFT.O                            25.504841                        39.472125\n",
       "AAPL.O                            18.179222                        46.787027\n",
       "GOOGL.O                           16.128431                         9.600931"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "unstacked = stacked.unstack()\n",
    "display(unstacked)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Close the Data Library session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "ld.close_session()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conclusion\n",
    "In this beginners' guide to dataframe manipulation with Pandas, we've covered the essential functions that are the backbone of data analysis in Python from loading data and inspecting it, to filtering, grouping, and transforming. Pandas provides a powerful toolkit that simplifies working with complex datasets. By using practical examples and applying these techniques to datasets, you should now have a solid foundation to manipulate dataframes. Whether you're cleaning data, performing exploratory analysis, or preparing data for machine learning models, mastering these Pandas basics will signigicantly enhance your data science capabilities.\n",
    "For further reading and more advanced tutorials, consider exploring the [official Pandas documentation](https://pandas.pydata.org/docs/getting_started/index.html) and using it with the datasets provided by LSEG via [LSEG Data Library for Python](https://developers.lseg.com/en/api-catalog/lseg-data-platform/lseg-data-library-for-python)."
   ]
  }
 ],
 "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}