tutorials/python/Ipython files/Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Object Creation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating Series: 1D ndarray "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    1\n",
      "1    5\n",
      "2    2\n",
      "3    3\n",
      "4    6\n",
      "5    4\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(pd.Series( [1, 5, 2, 3, 6, 4] ) )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Checking a value in Series index and values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n",
      "a    False\n",
      "b    False\n",
      "c     True\n",
      "d    False\n",
      "e    False\n",
      "dtype: bool\n"
     ]
    }
   ],
   "source": [
    "s = pd.Series(range(5), index=list('abcde'))\n",
    "print('b' in s) # checks index values\n",
    "print(s.isin([2])) # checks in values "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a    0\n",
      "b    1\n",
      "c    2\n",
      "d    3\n",
      "e    4\n",
      "dtype: int32\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "a    0.0\n",
       "f    NaN\n",
       "g    NaN\n",
       "dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(s)\n",
    "s = s.reindex(list('afg'))\n",
    "s # due to NaN, whole series dtype changed to float. To escape from such instances, make the series object dtype initially."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a    0\n",
      "b    1\n",
      "c    2\n",
      "d    3\n",
      "e    4\n",
      "dtype: object\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "a      0\n",
       "f    NaN\n",
       "g    NaN\n",
       "dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s_obj = pd.Series(range(5), index=list('abcde'), dtype=np.object)\n",
    "print(s_obj)\n",
    "s_obj = s_obj.reindex(list('afg'))\n",
    "s_obj # No change in the dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1. Using list of dictionary\n",
    "lst = [{\"C1\": 1, \"C2\": 2},\n",
    "      {\"C1\": 5, \"C2\": 10, \"C3\": 20}]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    C1  C2    C3\n",
      "R1   1   2   NaN\n",
      "R2   5  10  20.0\n"
     ]
    }
   ],
   "source": [
    "# Observe NaN       \n",
    "print(pd.DataFrame(lst, index = [\"R1\", \"R2\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   C1 C2\n",
      "R1  1  2\n",
      "R2  3  4\n"
     ]
    }
   ],
   "source": [
    "# 2. Using list (Commonly used)\n",
    "lst = {\"C1\": [\"1\", \"3\"],\n",
    "      \"C2\": [\"2\",\"4\"]}\n",
    "print( pd.DataFrame(lst, index = [\"R1\", \"R2\"]) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          a         b\n",
      "p  0.563066  0.494843\n",
      "q -0.051123  1.303799\n"
     ]
    }
   ],
   "source": [
    "# Defining Column name within df function\n",
    "print(pd.DataFrame(np.random.randn(2,2),\n",
    "                  index = list('pq'),\n",
    "                   columns = list('ab')))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      A     B   C       D\n",
      "0  10.0  text   2  (3+9j)\n",
      "1  20.0  text  60  (3+9j)\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame({'A': [10., 20.],\n",
    "                  'B': \"text\",\n",
    "                  'C': [2,60],\n",
    "                  'D': 3+9j})\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A       float64\n",
      "B        object\n",
      "C         int64\n",
      "D    complex128\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "# Observe final dtype as OBJECT to preserve every\n",
    "# element's data type information.\n",
    "print(df.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2 entries, 0 to 1\n",
      "Data columns (total 4 columns):\n",
      "A    2 non-null float64\n",
      "B    2 non-null object\n",
      "C    2 non-null int64\n",
      "D    2 non-null complex128\n",
      "dtypes: complex128(1), float64(1), int64(1), object(1)\n",
      "memory usage: 160.0+ bytes\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "print(df.info()) # memory usage doesn't include object dtypes columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2 entries, 0 to 1\n",
      "Data columns (total 4 columns):\n",
      "A    2 non-null float64\n",
      "B    2 non-null object\n",
      "C    2 non-null int64\n",
      "D    2 non-null complex128\n",
      "dtypes: complex128(1), float64(1), int64(1), object(1)\n",
      "memory usage: 286.0 bytes\n"
     ]
    }
   ],
   "source": [
    "df.info(memory_usage='deep') # with deep even object dtypes are included"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Viewing Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          0         1\n",
      "0  2.076125 -0.371193\n",
      "1 -0.620316 -0.521640\n",
      "2 -0.555493 -0.146482\n",
      "3 -0.614715  1.007617\n",
      "4  0.219642 -0.311603\n"
     ]
    }
   ],
   "source": [
    "print(pd.DataFrame(np.random.randn(20,2)).head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           0         1\n",
      "15  0.938823 -1.065879\n",
      "16  1.284274  0.952037\n",
      "17  1.633314 -0.249550\n",
      "18 -0.881654 -1.234461\n",
      "19  1.162749  1.542099\n"
     ]
    }
   ],
   "source": [
    "print(pd.DataFrame(np.random.randn(20,2)).tail(5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'A': [10., 20.],\n",
    "                  'B': \"text\",\n",
    "                  'C': [2,60],\n",
    "                  'D': 3+9j}, index = list('PQ'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['P', 'Q'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print((df.index))   #index of rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['A', 'B', 'C', 'D'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print(df.columns)   # index of columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[10.0 'text' 2 (3+9j)]\n",
      " [20.0 'text' 60 (3+9j)]]\n"
     ]
    }
   ],
   "source": [
    "print(df.values)    # display values of df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DataFrame Attributes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   A  B  C\n",
      "P  1  2  3\n",
      "Q  4  5  6\n",
      "R  7  8  9\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame({'A': [1,4,7],\n",
    "                   'B': [2,5,8],\n",
    "                   'C': [3,6,9]},\n",
    "                   index = list('PQR'))\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   P  Q  R\n",
      "A  1  4  7\n",
      "B  2  5  8\n",
      "C  3  6  9\n"
     ]
    }
   ],
   "source": [
    "# Transpose\n",
    "print(df.T)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3, 3) 9 2\n"
     ]
    }
   ],
   "source": [
    "# shape, size and dimensions\n",
    "print(df.shape, df.size, df.ndim)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DataFrame Methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame({'A': [1,4,7],\n",
    "                   'B': [2,5,8],\n",
    "                   'C': [3,6,9]},\n",
    "                   index = list('PQR'))\n",
    "df_temp = df1\n",
    "df2 = pd.DataFrame({'A': [-1,4,7],\n",
    "                   'B': [2,-5,8],\n",
    "                   'C': [3,6,-9]},\n",
    "                   index = list('PQR'))\n",
    "\n",
    "df3 = pd.DataFrame({'A': [1,-4,-7],\n",
    "                   'B': [-2,5,-8],\n",
    "                   'C': [-3,-6,9]},\n",
    "                   index = list('STU'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Addition | Subtraction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    A   B   C\n",
      "P   0   4   6\n",
      "Q   8   0  12\n",
      "R  14  16   0\n"
     ]
    }
   ],
   "source": [
    "print(df1.add(df2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    A   B   C\n",
      "P NaN NaN NaN\n",
      "Q NaN NaN NaN\n",
      "R NaN NaN NaN\n",
      "S NaN NaN NaN\n",
      "T NaN NaN NaN\n",
      "U NaN NaN NaN\n"
     ]
    }
   ],
   "source": [
    "print(df1.sub(df3))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Appending 2 dfs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   A  B  C\n",
      "P  1  2  3\n",
      "Q  4  5  6\n",
      "R  7  8  9\n",
      "P -1  2  3\n",
      "Q  4 -5  6\n",
      "R  7  8 -9\n"
     ]
    }
   ],
   "source": [
    "print(df_temp.append(df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Applying a function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   A  B  C\n",
      "S  1  2  3\n",
      "T  4  5  6\n",
      "U  7  8  9\n"
     ]
    }
   ],
   "source": [
    "print(df3.apply(np.abs, axis = 1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>S</th>\n",
       "      <td>1</td>\n",
       "      <td>-2</td>\n",
       "      <td>-3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>T</th>\n",
       "      <td>-4</td>\n",
       "      <td>5</td>\n",
       "      <td>-6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>U</th>\n",
       "      <td>-7</td>\n",
       "      <td>-8</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A  B  C\n",
       "S  1 -2 -3\n",
       "T -4  5 -6\n",
       "U -7 -8  9"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "A   -10\n",
       "B    -5\n",
       "C     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.apply(np.sum, axis = 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Converting frame to its numpy array representation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'numpy.ndarray'>\n"
     ]
    }
   ],
   "source": [
    "print(type(df3.as_matrix()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Computing non-nan values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>P</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Q</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R</th>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A  B  C\n",
       "P  1  2  3\n",
       "Q  4  5  6\n",
       "R  7  8  9"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A    3\n",
      "B    3\n",
      "C    3\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(df1.count())  #by default -> col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "P    3\n",
      "Q    3\n",
      "R    3\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(df1.count(axis = 1))  # row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A    0\n",
      "B    0\n",
      "C    0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(df1.sub(df3).count())   # nan in all col"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Handling NULL values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    A   B\n",
      "C NaN NaN\n",
      "D NaN NaN\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame(np.nan,columns = list('AB'),\n",
    "                 index = list('CD'))\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      A     B\n",
      "C  True  True\n",
      "D  True  True\n"
     ]
    }
   ],
   "source": [
    "print(df.isnull())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filling NaNs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     A    B    C\n",
      "P  5.0  5.0  5.0\n",
      "Q  5.0  5.0  5.0\n",
      "R  5.0  5.0  5.0\n",
      "S  5.0  5.0  5.0\n",
      "T  5.0  5.0  5.0\n",
      "U  5.0  5.0  5.0\n"
     ]
    }
   ],
   "source": [
    "print(df1.sub(df3).fillna(5))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Drop NaNs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     A    B   C  D\n",
      "0  NaN  2.0 NaN  0\n",
      "1  3.0  4.0 NaN  1\n",
      "2  NaN  NaN NaN  5\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame([[np.nan, 2, np.nan, 0], \n",
    "                  [3, 4, np.nan, 1],\n",
    "                  [np.nan, np.nan, np.nan, 5]],\n",
    "                  columns=list('ABCD'))\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     A    B  D\n",
      "0  NaN  2.0  0\n",
      "1  3.0  4.0  1\n",
      "2  NaN  NaN  5\n"
     ]
    }
   ],
   "source": [
    "print(df.dropna(axis = 1, how = 'all'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   D\n",
      "0  0\n",
      "1  1\n",
      "2  5\n"
     ]
    }
   ],
   "source": [
    "print(df.dropna(axis = 1, how='any'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Handling Duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    False\n",
      "1     True\n",
      "2    False\n",
      "3     True\n",
      "dtype: bool\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame([[1, 2, 3], \n",
    "                  [1, 2, 3], \n",
    "                  [4, 5, 6],\n",
    "                  [1, 2, 3]], columns =list('ABC'))\n",
    "# representing which rows are duplicated\n",
    "print(df.duplicated())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Computing PRESENCE of VALUES in a df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    A      B       C\n",
      "0  hi      2  (3+0j)\n",
      "1   1     20  (3+3j)\n",
      "2   4  world  (6+0j)\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame([[\"hi\", 2, 3], \n",
    "                  [1, 20, 3+3j], \n",
    "                  [4, \"world\", 6]], \n",
    "                   columns =list('ABC'))\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       A      B      C\n",
      "0   True  False  False\n",
      "1  False   True  False\n",
      "2  False  False   True\n"
     ]
    }
   ],
   "source": [
    "print(df.isin( [20, 6+0j, 'hi'] ))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Selection"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selection by Label"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    D   E\n",
      "A  15  12\n",
      "B  33  54\n",
      "C  10  32\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame([[15, 12],\n",
    "                  [33, 54],\n",
    "                  [10, 32]], \n",
    "                  index = list('ABC'),\n",
    "                  columns = list('DE'))\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    D   E\n",
      "A  15  12\n",
      "C  10  32\n"
     ]
    }
   ],
   "source": [
    "print(df.loc[['A','C'],:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A    12\n",
      "B    54\n",
      "C    32\n",
      "Name: E, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(df.loc[:,'E'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "33\n"
     ]
    }
   ],
   "source": [
    "print(df.loc['B', 'D'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selection by Position"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    0   1\n",
      "0  15  12\n",
      "1  33  54\n",
      "2  10  32\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame([[15, 12],\n",
    "                  [33, 54],\n",
    "                  [10, 32]])\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    0   1\n",
      "0  15  12\n",
      "2  10  32\n"
     ]
    }
   ],
   "source": [
    "print(df.iloc[[0,2],:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    0\n",
      "0  15\n",
      "1  33\n",
      "2  10\n"
     ]
    }
   ],
   "source": [
    "print(df.iloc[:,0:1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    0   1\n",
      "0  15  12\n",
      "1  33  54\n",
      "2  10  32\n"
     ]
    }
   ],
   "source": [
    "print(df.iloc[:,:]) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## RegEx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       col1  col2\n",
      "one      15    12\n",
      "two      33    54\n",
      "three    10    32\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame([[15, 12],\n",
    "                  [33, 54],\n",
    "                  [10, 32]], \n",
    "                  index = ['one','two','three'],\n",
    "                  columns = ['col1', 'col2'])\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       col1  col2\n",
      "one      15    12\n",
      "three    10    32\n"
     ]
    }
   ],
   "source": [
    "print(df.filter(regex = 'e$', axis = 0))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       col1  col2\n",
      "one      15    12\n",
      "two      33    54\n",
      "three    10    32\n"
     ]
    }
   ],
   "source": [
    "print(df.filter(regex = '^c', axis = 1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Boolean indexing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>33</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    0   1\n",
       "0  15  10\n",
       "1  33  54\n",
       "2  10  32"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame([[15, 12],\n",
    "                  [33, 54],\n",
    "                  [10, 32]])\n",
    "\n",
    "df[df < 15] = 10\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    A   B\n",
      "0  15  12\n",
      "1  33  54\n",
      "2  10  32\n"
     ]
    }
   ],
   "source": [
    "df1 = pd.DataFrame([[15, 12],\n",
    "                  [33, 54],\n",
    "                  [10, 32]],\n",
    "                  columns = list('AB')) \n",
    "df2 = pd.DataFrame([[15, 12, -3],\n",
    "                  [33, 54, 21],\n",
    "                  [10, 32, 22]],\n",
    "                  columns = list('ABC'))\n",
    "df3 = pd.DataFrame([[10, 1, 3],\n",
    "                  [33, -54, 2],\n",
    "                  [10, 0.32, 2]],\n",
    "                  columns = list('ABC'))\n",
    "\n",
    "print(df1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    -3\n",
      "1    21\n",
      "2    22\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(df1.eval('B - A'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15</td>\n",
       "      <td>12</td>\n",
       "      <td>-3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>33</td>\n",
       "      <td>54</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>32</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B   C\n",
       "0  15  12  -3\n",
       "1  33  54  21\n",
       "2  10  32  22"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.eval('C = B - A', inplace=False)   # < C = > is optional    |  inplace=True doesn't return output, rather changes orig."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>33</td>\n",
       "      <td>54</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>32</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B   C\n",
       "0  15  12  15\n",
       "1  33  54  34\n",
       "2  10  32  12"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loc_var = np.arange(3)\n",
    "df1.eval('C = A + @loc_var', inplace=False)   # @ allows to use external variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    A   B\n",
      "0  15  12\n",
      "1  33  54\n",
      "2  10  32\n"
     ]
    }
   ],
   "source": [
    "print(df1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Comparison Operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "False\n"
     ]
    }
   ],
   "source": [
    "print(df1.equals(df2))  # On broader scale"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       A      B      C\n",
      "0  False  False  False\n",
      "1   True  False  False\n",
      "2   True  False  False\n"
     ]
    }
   ],
   "source": [
    "print(df2.eq(df3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       A      B      C\n",
      "0  False  False   True\n",
      "1   True  False  False\n",
      "2   True  False  False\n"
     ]
    }
   ],
   "source": [
    "print(df2.le(df3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       A     B     C\n",
      "0   True  True  True\n",
      "1  False  True  True\n",
      "2  False  True  True\n"
     ]
    }
   ],
   "source": [
    "print(df2.ne(df3))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging dfs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Concatenating at bottom"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    A      B   C\n",
      "0  15  12.00  -3\n",
      "1  33  54.00  21\n",
      "2  10  32.00  22\n",
      "0  10   1.00   3\n",
      "1  33 -54.00   2\n",
      "2  10   0.32   2\n"
     ]
    }
   ],
   "source": [
    "print(pd.concat( [df2,df3], axis = 0 ))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Concatenating at side"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    A   B   C   A      B  C\n",
      "0  15  12  -3  10   1.00  3\n",
      "1  33  54  21  33 -54.00  2\n",
      "2  10  32  22  10   0.32  2\n"
     ]
    }
   ],
   "source": [
    "print(pd.concat( [df2,df3], axis = 1 ))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reshaping & Pivot tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pivot tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    PID   CType USD PND\n",
      "0  PID0    Gold  1$  2£\n",
      "1  PID0  Bronze  2$  4£\n",
      "2  PID1    Gold  3$  6£\n",
      "3  PID1  Silver  4$  8£\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame([\n",
    "           ['PID0', 'Gold', '1$', '2£'],\n",
    "           ['PID0', 'Bronze', '2$', '4£'],\n",
    "           ['PID1', 'Gold', '3$', '6£'],\n",
    "           ['PID1', 'Silver', '4$', '8£'],\n",
    "           ], columns = ['PID', 'CType', \n",
    "               'USD', 'PND'])\n",
    "                 \n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         USD                PND            \n",
      "CType Bronze Gold Silver Bronze Gold Silver\n",
      "PID                                        \n",
      "PID0      2$   1$   None     4£   2£   None\n",
      "PID1    None   3$     4$   None   6£     8£\n"
     ]
    }
   ],
   "source": [
    "print(df.pivot(index = 'PID', columns = 'CType'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CType Bronze Gold Silver\n",
      "PID                     \n",
      "PID0      2$   1$   None\n",
      "PID1    None   3$     4$\n"
     ]
    }
   ],
   "source": [
    "print(df.pivot(index = 'PID', columns = 'CType',\n",
    "              values = 'USD')) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame([\n",
    "           ['PID0', 'Gold', '1$'],\n",
    "           ['PID0', 'Bronze', '2$'],\n",
    "           ['PID0', 'Gold', '3$'],     #Duplicate\n",
    "           ['PID1', 'Silver', '4$'],\n",
    "           ], columns = ['PID', 'CType', \n",
    "               'USD'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    PID   CType USD\n",
      "0  PID0    Gold  1$\n",
      "1  PID0  Bronze  2$\n",
      "2  PID0    Gold  3$\n",
      "3  PID1  Silver  4$\n"
     ]
    }
   ],
   "source": [
    "# Ambiguity in saving value to PID0_Gold \n",
    "# on values 1$ and 3$\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         USD             \n",
      "CType Bronze  Gold Silver\n",
      "PID                      \n",
      "PID0      2$    3$   None\n",
      "PID1    None  None     4$\n"
     ]
    }
   ],
   "source": [
    "print(df.pivot_table(index = 'PID', \n",
    "                    columns = 'CType',\n",
    "                    aggfunc = np.max))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reshaping "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "               C1        C2\n",
      "1st 2nd                    \n",
      "IND R1   0.609643  0.767526\n",
      "    R2   0.212994  0.589823\n",
      "US  R1  -0.745894 -0.024052\n",
      "    R2  -0.435979 -0.980595\n"
     ]
    }
   ],
   "source": [
    "multi_ind = pd.MultiIndex.from_tuples(\n",
    "                                     [('IND','R1'),\n",
    "                                      ('IND','R2'),\n",
    "                                      ('US','R1'),\n",
    "                                      ('US','R2')],\n",
    "                                      names = [\n",
    "                                               '1st',\n",
    "                                               '2nd'])\n",
    "df = pd.DataFrame(np.random.randn(4,2),\n",
    "                 index = multi_ind,\n",
    "                 columns = ['C1', 'C2'])\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1st  2nd    \n",
      "IND  R1   C1    0.609643\n",
      "          C2    0.767526\n",
      "     R2   C1    0.212994\n",
      "          C2    0.589823\n",
      "US   R1   C1   -0.745894\n",
      "          C2   -0.024052\n",
      "     R2   C1   -0.435979\n",
      "          C2   -0.980595\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(df.stack())   # increase in length"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           C1                  C2          \n",
      "2nd        R1        R2        R1        R2\n",
      "1st                                        \n",
      "IND  0.609643  0.212994  0.767526  0.589823\n",
      "US  -0.745894 -0.435979 -0.024052 -0.980595\n"
     ]
    }
   ],
   "source": [
    "print(df.unstack()) # increase in width"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Grouping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   X  Y\n",
      "0  B  1\n",
      "1  B  2\n",
      "2  A  3\n",
      "3  A  4\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame([[\"B\", 1],\n",
    "                  [\"B\", 2],\n",
    "                  [\"A\", 3],\n",
    "                  [\"A\", 4]], columns = list('XY'))\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Y\n",
      "X   \n",
      "A  7\n",
      "B  3\n"
     ]
    }
   ],
   "source": [
    "print(df.groupby(['X']).sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Y\n",
      "X   \n",
      "B  3\n",
      "A  7\n"
     ]
    }
   ],
   "source": [
    "print(df.groupby(['X'], sort = False).sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   A  B         C         D\n",
      "0  p  r -0.035902 -1.610307\n",
      "1  p  r  0.817545  1.032971\n",
      "2  p  s  0.874735 -0.811189\n",
      "3  p  s -0.480971 -1.050927\n",
      "4  q  r  0.724361  1.857076\n",
      "5  q  r -0.093597 -0.479472\n",
      "6  q  s -0.199158 -0.220293\n",
      "7  q  s -0.082610 -0.028525\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame({'A' : list('ppppqqqq'),\n",
    "                  'B' : list('rrssrrss'),\n",
    "                   'C': np.random.randn(8),\n",
    "                   'D': np.random.randn(8)})\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            C         D\n",
      "       C_mean  D_median\n",
      "A B                    \n",
      "p r  0.390822 -0.288668\n",
      "  s  0.196882 -0.931058\n",
      "q r  0.315382  0.688802\n",
      "  s -0.140884 -0.124409\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Anaconda\\lib\\site-packages\\pandas\\core\\groupby.py:3961: FutureWarning: using a dict with renaming is deprecated and will be removed in a future version\n",
      "  return super(DataFrameGroupBy, self).aggregate(arg, *args, **kwargs)\n"
     ]
    }
   ],
   "source": [
    "print(df.groupby(['A', 'B']).agg(\n",
    "                {'C': {'C_mean': 'mean'},\n",
    "                 'D': {'D_median': 'median'}\n",
    "                  }))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import and Export"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame([[11, 202],\n",
    "                  [33, 44]],\n",
    "                  index = list('AB'),\n",
    "                   columns = list('CD'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Writing to excel file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel('pd_df.xlsx', sheet_name = 'Sheet1')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading from excel file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    C    D\n",
      "A  11  202\n",
      "B  33   44\n"
     ]
    }
   ],
   "source": [
    "print(pd.read_excel('pd_df.xlsx', 'Sheet1'))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}