30_min_of_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 20 minutes of pandas\n",
    "This workbook starts with the copy of the code of \"10 minutes of pandas\"\n",
    "The most complete jupyter HELP file on pandas of mine.\n",
    "There is more or less a logic line but not always.\n",
    "The jupyter notebook tries to put together the most important things but as starter.\n",
    "It does not go deep of kind of complicated queries since I dont think I need that."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# importing the neccesary libraries.\n",
    "\n",
    "# importing numpy for arrays.\n",
    "# note: since pandas is built on top of numpy I think that if pandas is imported \n",
    "# there is no need to import numpy. \n",
    "import numpy as np\n",
    "\n",
    "# pandas dataframe library:\n",
    "import pandas as pd\n",
    "\n",
    "# in order to visualize data matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "# the follwoing is a special nomenclature for using matplotlib within a jupyter notebook\n",
    "%matplotlib inline\n",
    "\n",
    "# in order to generated ramdomly generated numpy arrays\n",
    "import random"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a NUMPY array"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "onp numpy array is:\n",
      "[ 1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20]\n",
      "onp numpy array reshaped is:\n",
      "[[ 1  2  3  4  5]\n",
      " [ 6  7  8  9 10]\n",
      " [11 12 13 14 15]\n",
      " [16 17 18 19 20]]\n"
     ]
    }
   ],
   "source": [
    "# create a one dimensional array with numpy with values from 1 to 21 with step 1\n",
    "onp=np.arange(1,21,1)\n",
    "print('onp numpy array is:')\n",
    "print(onp)\n",
    "onp=onp.reshape(4,5)\n",
    "# notice how the dimensions have to fit. 4x5=20 which is the number of elements.\n",
    "print('onp numpy array reshaped is:')\n",
    "print(onp)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a series with pandas\n",
    "my explanation:\n",
    "A series can be seen as a one dimensional pandas dataframe.\n",
    "It is like a column or a row of a dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    1.0\n",
      "1    3.0\n",
      "2    5.0\n",
      "3    NaN\n",
      "4    6.0\n",
      "5    8.0\n",
      "dtype: float64\n",
      "<class 'pandas.core.series.Series'>\n"
     ]
    }
   ],
   "source": [
    "s=pd.Series([1,3,5,np.nan,6,8])\n",
    "# note how nan is an element of numpy. \n",
    "print(s)\n",
    "print(type(s))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Time series are very important for certain kind of data processing\n",
    "# At least there is so much about time series everywhere.\n",
    "# time series is for me at the moment not so important.\n",
    "# I dont see any importance for \"time evolution\" in data (so far)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a time series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DatetimeIndex(['2013-01-01', '2013-01-02', '2013-01-03', '2013-01-04',\n",
      "               '2013-01-05', '2013-01-06'],\n",
      "              dtype='datetime64[ns]', freq='D')\n"
     ]
    }
   ],
   "source": [
    "dates=pd.date_range('20130101',periods=6)\n",
    "print(dates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   A         B         C         D\n",
      "2013-01-01  0.032454  0.882226  0.446675  0.351854\n",
      "2013-01-02  0.741414  0.901705  0.415631  0.003549\n",
      "2013-01-03  0.179602  0.478852  0.568454  0.413010\n",
      "2013-01-04  0.783716  0.234898  0.989542  0.640495\n",
      "2013-01-05  0.836919  0.009969  0.692610  0.408315\n",
      "2013-01-06  0.170976  0.776922  0.982307  0.884943\n"
     ]
    }
   ],
   "source": [
    "df=pd.DataFrame(np.random.rand(6,4),index=dates,columns=list('ABCD'))\n",
    "print(df)\n",
    "# Note again how the dimensions have to fit.\n",
    "# The dataframe is created passing the data of a two dimensional numpy array created ramdomly.\n",
    "# Such array is 6x4, so 6 dates are neccesary as indexes and 4 column names.\n",
    "# see how deleting on letter of the string ABCD would spit an error."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a dataframe passing a dicctionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   A          B    C  D      E    F\n",
      "0  1 2013-01-02  1.0  3   test  foo\n",
      "1  1 2013-01-02  1.0  3  train  foo\n",
      "2  1 2013-01-02  1.0  3   test  foo\n",
      "3  1 2013-01-02  1.0  3  train  foo\n"
     ]
    }
   ],
   "source": [
    "df2=pd.DataFrame({'A':1,\n",
    "                  'B':pd.Timestamp('20130102'),\n",
    "                 'C':pd.Series(1,index=list(range(4)),dtype='float32'),\n",
    "                 'D':np.array([3]*4,dtype='int32'),\n",
    "                'E': pd.Categorical([\"test\",\"train\",\"test\",\"train\"]),\n",
    "                'F': 'foo'})\n",
    "print(df2)\n",
    "# note how every column is of a different type.\n",
    "# again dimensions have to fit. All series are of the same dimension.\n",
    "# 'F' is a string, so it is get all the same along the column\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## dataframe Methods\n",
    "### dtypes, head(), tail(), index(), columns(), values()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A             int64\n",
      "B    datetime64[ns]\n",
      "C           float32\n",
      "D             int32\n",
      "E          category\n",
      "F            object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(df2.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   A         B         C         D\n",
      "2013-01-01  0.032454  0.882226  0.446675  0.351854\n",
      "2013-01-02  0.741414  0.901705  0.415631  0.003549\n"
     ]
    }
   ],
   "source": [
    "# first two lines\n",
    "# note: using df, not df2\n",
    "print(df.head(2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   A         B         C         D\n",
      "2013-01-04  0.783716  0.234898  0.989542  0.640495\n",
      "2013-01-05  0.836919  0.009969  0.692610  0.408315\n",
      "2013-01-06  0.170976  0.776922  0.982307  0.884943\n"
     ]
    }
   ],
   "source": [
    "# last three lines\n",
    "print(df.tail(3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DatetimeIndex(['2013-01-01', '2013-01-02', '2013-01-03', '2013-01-04',\n",
      "               '2013-01-05', '2013-01-06'],\n",
      "              dtype='datetime64[ns]', freq='D')\n",
      "Index(['A', 'B', 'C', 'D'], dtype='object')\n",
      "\n",
      "index values\n",
      "['2013-01-01T00:00:00.000000000' '2013-01-02T00:00:00.000000000'\n",
      " '2013-01-03T00:00:00.000000000' '2013-01-04T00:00:00.000000000'\n",
      " '2013-01-05T00:00:00.000000000' '2013-01-06T00:00:00.000000000']\n",
      "\n",
      "type of index:\n",
      "<class 'pandas.core.indexes.datetimes.DatetimeIndex'>\n",
      "type of index.values:\n",
      "<class 'numpy.ndarray'>\n"
     ]
    }
   ],
   "source": [
    "# the index and the columns\n",
    "print(df.index)\n",
    "print(df.columns)\n",
    "# be aware that df.index ITS NOT A LIST, BUT AN OBJECT.\n",
    "# THE LIST IS ACCESED AGAIN WITH .VALUES\n",
    "print()\n",
    "print('index values')\n",
    "print(df.index.values)\n",
    "print()\n",
    "print('type of index:')\n",
    "print(type(df.index))\n",
    "print('type of index.values:')\n",
    "print(type(df.index.values))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[0.03245381 0.88222628 0.44667515 0.35185446]\n",
      " [0.741414   0.90170488 0.41563109 0.00354915]\n",
      " [0.17960189 0.47885186 0.56845389 0.41301016]\n",
      " [0.78371589 0.23489807 0.98954234 0.64049516]\n",
      " [0.83691853 0.00996885 0.69261005 0.40831515]\n",
      " [0.17097609 0.77692211 0.98230653 0.88494305]]\n",
      "\n",
      "the type variable of df.values is:\n",
      "<class 'numpy.ndarray'>\n",
      "\n",
      "np vlaues array size is: 24\n",
      "np vlaues array shape is: (6, 4)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# it is possible to access JUST the values\n",
    "print(df.values)\n",
    "print()\n",
    "print('the type variable of df.values is:')\n",
    "print(type(df.values))\n",
    "# the values are a two dimensional numpy array.\n",
    "NP_values_Array=df.values\n",
    "print()\n",
    "print('np vlaues array size is:',NP_values_Array.size)\n",
    "print('np vlaues array shape is:',NP_values_Array.shape)\n",
    "print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Operations with the values of a dataframe\n",
    "### A dataframe can be operated as a matrix using his values (as seen above)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[0.03245381 0.88222628 0.44667515 0.35185446]\n",
      " [0.741414   0.90170488 0.41563109 0.00354915]\n",
      " [0.17960189 0.47885186 0.56845389 0.41301016]\n",
      " [0.78371589 0.23489807 0.98954234 0.64049516]\n",
      " [0.83691853 0.00996885 0.69261005 0.40831515]\n",
      " [0.17097609 0.77692211 0.98230653 0.88494305]]\n"
     ]
    }
   ],
   "source": [
    "oNumPy=df.values\n",
    "print(oNumPy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'numpy.ndarray'>\n",
      "<class 'numpy.ndarray'>\n"
     ]
    }
   ],
   "source": [
    "# both are of the same type of data:\n",
    "print(type(df.values))\n",
    "print(type(oNumPy))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(6, 4)\n"
     ]
    }
   ],
   "source": [
    "# lets investigate how can we operate witht he values.\n",
    "# multiplying vestors\n",
    "# (m x n) x (n x k) = m x k\n",
    "print(df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "oa shape\n",
      "(2, 4)\n",
      "oa Transposed\n",
      "[[1 5]\n",
      " [2 6]\n",
      " [3 7]\n",
      " [4 8]]\n",
      "\n",
      "df Transposed\n",
      "[[0.03245381 0.741414   0.17960189 0.78371589 0.83691853 0.17097609]\n",
      " [0.88222628 0.90170488 0.47885186 0.23489807 0.00996885 0.77692211]\n",
      " [0.44667515 0.41563109 0.56845389 0.98954234 0.69261005 0.98230653]\n",
      " [0.35185446 0.00354915 0.41301016 0.64049516 0.40831515 0.88494305]]\n"
     ]
    }
   ],
   "source": [
    "# building a matrix (n x k) , lets say k=2\n",
    "oa=np.array(([1,2,3,4],[5,6,7,8]))\n",
    "print('oa shape')\n",
    "print(oa.shape)\n",
    "oa=oa.T\n",
    "print('oa Transposed')\n",
    "print(oa)\n",
    "print()\n",
    "print('df Transposed')\n",
    "print(df.values.T)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(6, 4) x (4, 2)\n",
      "\n",
      "it does not work\n"
     ]
    }
   ],
   "source": [
    "matrix1=df.values\n",
    "matrix2=oa\n",
    "print('{0} x {1}'.format(matrix1.shape,matrix2.shape))\n",
    "print()\n",
    "#print(matrix1.dtypes)\n",
    "#print(matrix2.dtypes)\n",
    "try:\n",
    "    print(matrix1 * matrix2)\n",
    "except:\n",
    "    print('it does not work')\n",
    "# TO DO: dot multiplication.\n",
    "# print(dot(oNumPy*oa))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Sorting values\n",
    "Sorting the columns (alphabetically)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   D         C         B         A\n",
      "2013-01-01  0.351854  0.446675  0.882226  0.032454\n",
      "2013-01-02  0.003549  0.415631  0.901705  0.741414\n",
      "2013-01-03  0.413010  0.568454  0.478852  0.179602\n",
      "2013-01-04  0.640495  0.989542  0.234898  0.783716\n",
      "2013-01-05  0.408315  0.692610  0.009969  0.836919\n",
      "2013-01-06  0.884943  0.982307  0.776922  0.170976\n"
     ]
    }
   ],
   "source": [
    "print(df.sort_index(axis=1, ascending=False))\n",
    "# in a dataframe axis=0 is rows\n",
    "# axis=1 is columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   A         B         C         D\n",
      "2013-01-06  0.170976  0.776922  0.982307  0.884943\n",
      "2013-01-05  0.836919  0.009969  0.692610  0.408315\n",
      "2013-01-04  0.783716  0.234898  0.989542  0.640495\n",
      "2013-01-03  0.179602  0.478852  0.568454  0.413010\n",
      "2013-01-02  0.741414  0.901705  0.415631  0.003549\n",
      "2013-01-01  0.032454  0.882226  0.446675  0.351854\n"
     ]
    }
   ],
   "source": [
    "# ??? = why is it neccesary to specify axis=1 (0: index, 1:columns) if the function is called\n",
    "# already sort_index\n",
    "# works also for the columns?\n",
    "print(df.sort_index(axis=0, ascending=False))\n",
    "# yes it works also for columns, see how the columns are not listed in ascending order."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sorting accrding to one Column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   A         B         C         D\n",
      "2013-01-02  0.741414  0.901705  0.415631  0.003549\n",
      "2013-01-01  0.032454  0.882226  0.446675  0.351854\n",
      "2013-01-06  0.170976  0.776922  0.982307  0.884943\n",
      "2013-01-03  0.179602  0.478852  0.568454  0.413010\n",
      "2013-01-04  0.783716  0.234898  0.989542  0.640495\n",
      "2013-01-05  0.836919  0.009969  0.692610  0.408315\n"
     ]
    }
   ],
   "source": [
    "print(df.sort_values(by='B', ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Selection\n",
    "Select a column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2013-01-01    0.032454\n",
      "2013-01-02    0.741414\n",
      "2013-01-03    0.179602\n",
      "2013-01-04    0.783716\n",
      "2013-01-05    0.836919\n",
      "2013-01-06    0.170976\n",
      "Freq: D, Name: A, dtype: float64\n",
      "\n",
      "when we select a column with this method we get:\n",
      "<class 'pandas.core.series.Series'>\n"
     ]
    }
   ],
   "source": [
    "print(df['A'])\n",
    "print()\n",
    "print('when we select a column with this method we get:')\n",
    "print(type(df['A']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   A         B         C         D\n",
      "2013-01-01  0.032454  0.882226  0.446675  0.351854\n",
      "2013-01-02  0.741414  0.901705  0.415631  0.003549\n",
      "2013-01-03  0.179602  0.478852  0.568454  0.413010\n"
     ]
    }
   ],
   "source": [
    "print(df[0:3])\n",
    "# curious to see that when specifying a number range 0 to 3 pandas interprets that \n",
    "# we are talking about indexes in rows.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   A         B         C         D\n",
      "2013-01-01  0.032454  0.882226  0.446675  0.351854\n",
      "2013-01-02  0.741414  0.901705  0.415631  0.003549\n"
     ]
    }
   ],
   "source": [
    "print(df['20130101':'20130102'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df['A':'C'] does not work\n",
      "df['A','C'] does not work\n",
      "df.columns['A':'C'] does not work\n"
     ]
    }
   ],
   "source": [
    "# its strange that it is exactly the same layout for columns and for rows\n",
    "# things that DO NOT work. \n",
    "# print(df.columns['A','B'])\n",
    "# print(df['A','B'])\n",
    "# curious enough df['A','B'] does not work but df['A'] works\n",
    "try:\n",
    "    print(df['A':'C'])\n",
    "except:\n",
    "    print(\"df['A':'C']\", 'does not work')\n",
    "    \n",
    "try:\n",
    "    print(df['A','C'])\n",
    "except:\n",
    "    print(\"df['A','C']\", 'does not work')\n",
    "\n",
    "try:\n",
    "    print(df.columns['A':'C'])\n",
    "except:\n",
    "    print(\"df.columns['A':'C']\", 'does not work')\n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2013-01-01    0.032454\n",
      "2013-01-02    0.741414\n",
      "2013-01-03    0.179602\n",
      "2013-01-04    0.783716\n",
      "2013-01-05    0.836919\n",
      "2013-01-06    0.170976\n",
      "Freq: D, Name: A, dtype: float64\n",
      "\n",
      "<class 'pandas.core.series.Series'>\n",
      "\n",
      "the values\n",
      "[0.03245381 0.741414   0.17960189 0.78371589 0.83691853 0.17097609]\n",
      "<class 'numpy.ndarray'>\n",
      "0.7414139964920232\n",
      "\n",
      "type of data myArray[1]\n",
      "<class 'numpy.float64'>\n"
     ]
    }
   ],
   "source": [
    "# Alternative nomenclature\n",
    "print(df.A)\n",
    "print()\n",
    "print(type(df.A))\n",
    "print()\n",
    "print('the values')\n",
    "print(df.A.values)\n",
    "print(type(df.A.values))\n",
    "\n",
    "myArray=df.A.values\n",
    "print(myArray[1])\n",
    "print()\n",
    "print('type of data myArray[1]')\n",
    "print(type(myArray[1]))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Without using loc iloc: \n",
    "What does work?\n",
    "df['A','B'] NO\n",
    "df['A':'B'] NO\n",
    "df['A'] YES = the column A\n",
    "df.A = YES equivalent to df['A'] \n",
    "df[0:3] YES = the Rows from 0 to 3\n",
    "df['20130101':'20130102'] YES the rows from index first to index second\n",
    "It looks like when putting into [] and between : the dataframe refers to rows.\n",
    "With one only single value to a Column\n",
    "The way not to get confused might be to use always df.A notation for columns since anyway it is not possible to access several columns with this notation & use the : separated values (integers or strings) to refer to rows.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# LOC\n",
    "LOC= LOCATE. locate values based of\n",
    "a combination of indexes entry (rows)\n",
    "a combination of columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DatetimeIndex(['2013-01-01', '2013-01-02', '2013-01-03', '2013-01-04',\n",
      "               '2013-01-05', '2013-01-06'],\n",
      "              dtype='datetime64[ns]', freq='D')\n"
     ]
    }
   ],
   "source": [
    "# The index of the dataframe is a list \"dates\"\n",
    "print(dates)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2013-01-01 00:00:00\n"
     ]
    }
   ],
   "source": [
    "print(dates[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2013-01-01 00:00:00\n"
     ]
    }
   ],
   "source": [
    "print(df.index[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n"
     ]
    }
   ],
   "source": [
    "print(df.index[0]==dates[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A    0.032454\n",
      "B    0.882226\n",
      "C    0.446675\n",
      "D    0.351854\n",
      "Name: 2013-01-01 00:00:00, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(df.loc[dates[0]])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Index and columns names are lists, therefore the values can be accessed as such: as unique values or as ranges separated by ':'\n",
    "Slicing is King here. Like in any list.\n",
    "It is possible to access single values or ranges, or lists of values.\n",
    "The obvious way here to access ONE SINGLE POINT is refer to ONE ROW AND ONE COLUMN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.7414139964920232\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# get the value corresponding the date 20130102 and column A\n",
    "print(df.loc['20130102','A'])\n",
    "print()\n",
    "# Be aware!!!! with LOC you specify position with names.\n",
    "# index name , column name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   A         B         C\n",
      "2013-01-02  0.741414  0.901705  0.415631\n",
      "2013-01-03  0.179602  0.478852  0.568454\n",
      "2013-01-04  0.783716  0.234898  0.989542\n"
     ]
    }
   ],
   "source": [
    "print(df.loc['20130102':'20130104','A':'C'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using ranges with LOC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   A         C\n",
      "2013-01-02  0.741414  0.415631\n",
      "2013-01-03  0.179602  0.568454\n",
      "2013-01-04  0.783716  0.989542\n"
     ]
    }
   ],
   "source": [
    "print(df.loc['20130102':'20130104',['A','C']])\n",
    "# this does not work:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             A         B         C         D\n",
      "row1  0.890044  0.335384  0.329297  0.119863\n",
      "row2  0.776955  0.366543  0.970135  0.258837\n",
      "row3  0.714904  0.464015  0.524845  0.683201\n",
      "row4  0.943850  0.593634  0.879107  0.640165\n",
      "row5  0.460524  0.534652  0.169395  0.726782\n",
      "row6  0.185527  0.539885  0.700319  0.167296\n"
     ]
    }
   ],
   "source": [
    "# I create a new dataframe having strings as indexes to avoid working with dates.\n",
    "df3=pd.DataFrame(np.random.rand(6,4),index=['row1','row2','row3','row4','row5','row6'],columns=list('ABCD'))\n",
    "print(df3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             A         B         C\n",
      "row1  0.890044  0.335384  0.329297\n",
      "row2  0.776955  0.366543  0.970135\n",
      "row3  0.714904  0.464015  0.524845\n"
     ]
    }
   ],
   "source": [
    "# now there are no dates in the index\n",
    "print(df3.loc['row1':'row3', 'A':'C'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             A         B         C\n",
      "row1  0.890044  0.335384  0.329297\n",
      "row3  0.714904  0.464015  0.524845\n"
     ]
    }
   ],
   "source": [
    "# accessing using a list of values.\n",
    "print(df3.loc[['row1','row3'], 'A':'C'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             A         C\n",
      "row1  0.890044  0.329297\n",
      "row2  0.776955  0.970135\n",
      "row3  0.714904  0.524845\n"
     ]
    }
   ],
   "source": [
    "print(df3.loc['row1':'row3', ['A','C']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.890043987796399\n"
     ]
    }
   ],
   "source": [
    "print(df3.loc['row1','A'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ILOC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas provides a suite of methods in order to get purely integer based indexing. The semantics follow closely Python and NumPy slicing. These are 0-based indexing. When slicing, the start bounds is included, while the upper bound is excluded. Trying to use a non-integer, even a valid label will raise an IndexError.\n",
    "\n",
    "The .iloc attribute is the primary access method. The following are valid inputs:\n",
    "\n",
    "An integer e.g. 5.\n",
    "A list or array of integers [4, 3, 0].\n",
    "A slice object with ints 1:7.\n",
    "A boolean array.\n",
    "A callable, see Selection By Callable.\n",
    "\n",
    "ILOC is therefore the I-NTEGER LOC-ATOR"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A    0.943850\n",
      "B    0.593634\n",
      "C    0.879107\n",
      "D    0.640165\n",
      "Name: row4, dtype: float64\n",
      "row4\n"
     ]
    }
   ],
   "source": [
    "# if we include only one index it refers to a ROW\n",
    "print(df3.iloc[3])\n",
    "print(df3.iloc[3].name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "row4\n",
      "<class 'str'>\n"
     ]
    }
   ],
   "source": [
    "# what about the index name of the series 3\n",
    "print(df3.index[3])\n",
    "print(type(df3.index[3]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "we wan to append this list [12, 34, 45, 56]\n"
     ]
    }
   ],
   "source": [
    "# how to create a series to add to the pandas data frame.\n",
    "# with append (which is very slow)\n",
    "# df.append\n",
    "# it is only possible to append a series, not a list.\n",
    "a=12\n",
    "b=34\n",
    "c=45\n",
    "d=56\n",
    "# how to append those values.\n",
    "mylist=[a,b,c,d]\n",
    "print('we wan to append this list {0}'.format(mylist))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             A         B         C         D\n",
      "row1  0.890044  0.335384  0.329297  0.119863\n",
      "row2  0.776955  0.366543  0.970135  0.258837\n",
      "row3  0.714904  0.464015  0.524845  0.683201\n",
      "row4  0.943850  0.593634  0.879107  0.640165\n",
      "row5  0.460524  0.534652  0.169395  0.726782\n",
      "row6  0.185527  0.539885  0.700319  0.167296\n"
     ]
    }
   ],
   "source": [
    "print(df3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                 A             B             C             D\n",
      "row1      0.890044      0.335384      0.329297      0.119863\n",
      "row2      0.776955      0.366543      0.970135      0.258837\n",
      "row3      0.714904      0.464015      0.524845      0.683201\n",
      "row4      0.943850      0.593634      0.879107      0.640165\n",
      "row5  11111.000000  22222.000000  33333.000000  44444.000000\n",
      "row6      0.185527      0.539885      0.700319      0.167296\n",
      "\n",
      "modify one value\n",
      "                 A         B             C             D\n",
      "row1      0.890044  0.335384      0.329297      0.119863\n",
      "row2      0.776955  0.366543      0.970135      0.258837\n",
      "row3      0.714904  0.464015      0.524845      0.683201\n",
      "row4      0.943850  0.593634      0.879107      0.640165\n",
      "row5  11111.000000     22222  33333.000000  44444.000000\n",
      "row6      0.185527    XXXXXX      0.700319      0.167296\n"
     ]
    }
   ],
   "source": [
    "# modify an entire row\n",
    "df3.iloc[4]=[11111,22222,33333,44444]\n",
    "print(df3)\n",
    "print()\n",
    "print('modify one value')\n",
    "df3.iloc[5,1]='XXXXXX'\n",
    "print(df3)\n",
    "# Note that it is BASE 0!!!!!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "it does not work\n"
     ]
    }
   ],
   "source": [
    "# There are 6 rows. from 0 to 5\n",
    "# what happends if I try to modify a index that does not exist\n",
    "# case being inmediatly after the last one: row 7\n",
    "try:\n",
    "    df3.iloc[6,1]='NEW'\n",
    "except:\n",
    "    print('it does not work')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                 A         B             C             D\n",
      "row1      0.890044  0.335384      0.329297      0.119863\n",
      "row2      0.776955  0.366543      0.970135      0.258837\n",
      "row3      0.714904  0.464015      0.524845      0.683201\n",
      "row4      0.943850  0.593634      0.879107      0.640165\n",
      "row5  11111.000000     22222  33333.000000  44444.000000\n",
      "row6      0.185527    XXXXXX      0.700319      0.167296\n",
      "jose      7.000000         8      9.000000      0.000000\n",
      "\n",
      "                  A         B             C             D\n",
      "row1       0.890044  0.335384      0.329297      0.119863\n",
      "row2       0.776955  0.366543      0.970135      0.258837\n",
      "row3       0.714904  0.464015      0.524845      0.683201\n",
      "row4       0.943850  0.593634      0.879107      0.640165\n",
      "row5   11111.000000     22222         33333  44444.000000\n",
      "row6       0.185527    XXXXXX      0.700319      0.167296\n",
      "jose       7.000000         8             9      0.000000\n",
      "maria           NaN       NaN  single value           NaN\n",
      "\n",
      "pandas adds NaN to the values nokt specified.\n"
     ]
    }
   ],
   "source": [
    "# nevertheless with loc looks to work out\n",
    "# There is no row called 'jose' but:\n",
    "df3.loc['jose']=[7,8,9,0]\n",
    "print(df3)\n",
    "print()\n",
    "# even adding a single value.\n",
    "df3.loc['maria','C']='single value'\n",
    "print(df3)\n",
    "print()\n",
    "print('pandas adds NaN to the values nokt specified.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this is the advantage of having named indexes.\n",
    "# you can always add in with a new index name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We have to convert the list into a pandas series.\n",
    "# It is not possible to use append with a list\n",
    "# -------- TO DO ---------------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adding rows 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "values size: 3\n",
      "                  A         B             C             D\n",
      "row1       0.890044  0.335384      0.329297      0.119863\n",
      "row2       0.776955  0.366543      0.970135      0.258837\n",
      "row3       0.714904  0.464015      0.524845      0.683201\n",
      "row4       0.943850  0.593634      0.879107      0.640165\n",
      "row5   11111.000000     22222         33333  44444.000000\n",
      "row6       0.185527    XXXXXX      0.700319      0.167296\n",
      "jose       7.000000         8             9      0.000000\n",
      "maria           NaN       NaN  single value           NaN\n"
     ]
    }
   ],
   "source": [
    "indexlist=['one']\n",
    "columnList=list('ABC')\n",
    "values=np.array([1,2,3])\n",
    "# take care, the values array is a 3x1 size array. \n",
    "# rows has to be 1x3 so we have to reshape it\n",
    "print('values size:',values.size)\n",
    "values=values.reshape(1,3)\n",
    "df4=pd.DataFrame(values,index=indexlist,columns=columnList)\n",
    "print(df3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "type: <class 'pandas.core.series.Series'>\n",
      "values: [5 6 7]\n",
      "name: None\n"
     ]
    }
   ],
   "source": [
    "# what happend when adding \"two\" [5,6,7] and how to do it\n",
    "# a) first create a series:\n",
    "listtwo=[5,6,7]\n",
    "oseries=pd.Series(listtwo)\n",
    "print('type:',type(oseries))\n",
    "print('values:',oseries.values)\n",
    "print('name:',oseries.name)\n",
    "oseries.name=\"two\"\n",
    "# I would like to add this series to the end of the dataframe\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     A  B  C\n",
      "one  1  2  3\n",
      "two  5  6  7\n",
      "\n",
      "     A  B  C\n",
      "one  1  2  3\n",
      "two  5  6  7\n",
      "\n",
      "     A  B  C\n",
      "one  1  2  3\n",
      "two  5  6  7\n",
      "\n",
      "     A  B  C\n",
      "one  1  2  3\n",
      "two  5  6  7\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# the following is equivalent, thats why the df printed is the same three times\n",
    "df4.loc[oseries.name]=oseries.values\n",
    "print(df4)\n",
    "print()\n",
    "# this does work:\n",
    "df4.loc[oseries.name]=listtwo\n",
    "print(df4)\n",
    "print()\n",
    "df4.loc['two']=[5,6,7]\n",
    "print(df4)\n",
    "print()\n",
    "df4.append(oseries, ignore_index=True)\n",
    "print(df4)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "       A    B    C    0\n",
      "one  1.0  2.0  3.0  NaN\n",
      "two  5.0  6.0  7.0  NaN\n",
      "0    NaN  NaN  NaN  8.0\n",
      "1    NaN  NaN  NaN  9.0\n",
      "2    NaN  NaN  NaN  0.0\n"
     ]
    }
   ],
   "source": [
    "# what if I add a simple list\n",
    "newlist=[8,9,0]\n",
    "df4=df4.append(newlist)\n",
    "print()\n",
    "print(df4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add a dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "my dict {'A': 4, 'B': 5, 'C': 6}\n",
      "\n",
      "myseries:\n",
      "A    4\n",
      "B    5\n",
      "C    6\n",
      "Name: NEW, dtype: int64\n",
      "       A    B    C    0\n",
      "one  1.0  2.0  3.0  NaN\n",
      "two  5.0  6.0  7.0  NaN\n",
      "0    NaN  NaN  NaN  8.0\n",
      "1    NaN  NaN  NaN  9.0\n",
      "2    NaN  NaN  NaN  0.0\n",
      "NEW  4.0  5.0  6.0  NaN\n",
      "see how the row name NEW is added\n",
      "if you run this cell several times more rows named NEW are added\n",
      "if you dont want that use LOC or ILOC\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/jose/code/env/first/lib/python3.6/site-packages/ipykernel_launcher.py:7: RuntimeWarning: '<' not supported between instances of 'int' and 'str', sort order is undefined for incomparable objects\n",
      "  import sys\n"
     ]
    }
   ],
   "source": [
    "## from stack overflow\n",
    "print('my dict',dict(zip(df4.columns, [4, 5, 6])))\n",
    "print()\n",
    "s = pd.Series(dict(zip(df4.columns, [4, 5, 6]))).rename('NEW')\n",
    "print('myseries:')\n",
    "print(s)\n",
    "df4=df4.append(s)\n",
    "print(df4)\n",
    "print\n",
    "print('see how the row name {0} is added'.format(s.name))\n",
    "print('if you run this cell several times more rows named {0} are added'.format(s.name))\n",
    "print('if you dont want that use LOC or ILOC')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "my dict {'A': 4, 'B': 5, 'C': 6}\n",
      "my dict modified: {'A': 4, 'B': 5, 'C': 6, 'H': 'YES NEW COLUMN H Added'}\n",
      "\n",
      "myseries:\n",
      "A                         4\n",
      "B                         5\n",
      "C                         6\n",
      "H    YES NEW COLUMN H Added\n",
      "Name: NEW2, dtype: object\n",
      "        A    B    C    0                       H\n",
      "one   1.0  2.0  3.0  NaN                     NaN\n",
      "two   5.0  6.0  7.0  NaN                     NaN\n",
      "0     NaN  NaN  NaN  8.0                     NaN\n",
      "1     NaN  NaN  NaN  9.0                     NaN\n",
      "2     NaN  NaN  NaN  0.0                     NaN\n",
      "NEW   4.0  5.0  6.0  NaN                     NaN\n",
      "NEW2  4.0  5.0  6.0  NaN  YES NEW COLUMN H Added\n",
      "see how the row name NEW2 is added\n",
      "if you run this cell several times more rows named NEW2 are added\n",
      "if you dont want that use LOC or ILOC\n"
     ]
    }
   ],
   "source": [
    "# what if the dicctionary has one value not being a column name.\n",
    "my_dict=dict(zip(df4.columns, [4, 5, 6]))\n",
    "print('my dict',my_dict)\n",
    "my_dict[\"H\"]=\"YES NEW COLUMN H Added\"\n",
    "print('my dict modified:',my_dict)\n",
    "print()\n",
    "s = pd.Series(my_dict).rename('NEW2')\n",
    "print('myseries:')\n",
    "print(s)\n",
    "df4=df4.append(s)\n",
    "print(df4)\n",
    "print\n",
    "print('see how the row name {0} is added'.format(s.name))\n",
    "print('if you run this cell several times more rows named {0} are added'.format(s.name))\n",
    "print('if you dont want that use LOC or ILOC')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adding rows 2\n",
    "df5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   A  B  C\n",
      "0  1  2  3\n"
     ]
    }
   ],
   "source": [
    "# take care, the values array is a 3x1 size array. \n",
    "# rows has to be 1x3 so we have to reshape it\n",
    "df5=pd.DataFrame(np.array([1,2,3]).reshape(1,3),columns=list('ABC'))\n",
    "print(df5)\n",
    "# here the dataframe created does not have index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     A    B    C    0\n",
      "0  1.0  2.0  3.0  NaN\n",
      "0  NaN  NaN  NaN  5.0\n",
      "1  NaN  NaN  NaN  6.0\n",
      "2  NaN  NaN  NaN  7.0\n"
     ]
    }
   ],
   "source": [
    "# adding a simple list [5,6,7]\n",
    "# NOT TO DO: PASSING LISTS TO APPEND LOC AND ILOC. NOOOO\n",
    "# IN THE FUTURE WILL NOT WORK.\n",
    "newlist=[5,6,7]\n",
    "\n",
    "df6=df5.append(newlist)\n",
    "print(df6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mydf5 before:\n",
      "   A  B  C\n",
      "0  1  2  3\n",
      "\n",
      "mydict to add  {'A': 4, 'B': 5, 'C': 6}\n",
      "\n",
      "A    4\n",
      "B    5\n",
      "C    6\n",
      "dtype: int64\n",
      "myseries None\n",
      "\n",
      "adding series s\n",
      "\n",
      "mydf5:\n",
      "   A  B  C\n",
      "0  1  2  3\n",
      "1  4  5  6\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print('mydf5 before:')\n",
    "print(df5)\n",
    "my_dict=dict(zip(df.columns, [4, 5, 6]))\n",
    "print()\n",
    "print('mydict to add ',my_dict)\n",
    "print()\n",
    "\n",
    "s = pd.Series(my_dict)\n",
    "print('myseries',print(s))\n",
    "print()\n",
    "print('adding series s')\n",
    "print()\n",
    "\n",
    "# series witout name, otherwise it would be:\n",
    "# s = pd.Series(dict(zip(df.columns, [4, 5, 6])).rename('two'))\n",
    "df5 = df5.append(s, ignore_index=True)\n",
    "print('mydf5:')\n",
    "print(df5)\n",
    "print()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          A       B       C\n",
      "0         1       2       3\n",
      "1         4       5       6\n",
      "2  whatever  sdfsdf  sdfsdf\n",
      "Sname\n",
      "\n",
      "mydf5:\n",
      "              A       B       C\n",
      "0             1       2       3\n",
      "1             4       5       6\n",
      "2      whatever  sdfsdf  sdfsdf\n",
      "Sname         4       5       6\n",
      "\n"
     ]
    }
   ],
   "source": [
    "hh='whatever'\n",
    "my_dict2=dict(zip(df.columns, [hh,'sdfsdf','sdfsdf']))\n",
    "s2=pd.Series(my_dict2)\n",
    "df5 = df5.append(s2, ignore_index=True)\n",
    "# If series does not have a name I have to forget ignore the index name.\n",
    "print(df5)\n",
    "\n",
    "s.name=\"Sname\"\n",
    "print(s.name)\n",
    "# If we say ignore_index=False then the name of the series will be the index\n",
    "df5 = df5.append(s, ignore_index=False)\n",
    "print()\n",
    "print('mydf5:')\n",
    "print(df5)\n",
    "print()\n",
    "\n",
    "# this is the way to go if I just want to add stuff no matter the index.\n",
    "# for instance when later on the dataframe is further processed (for duplicate values etc)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## reducing duplicates: agregating data with groupby dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "before adding column D\n",
      "   A  B  C\n",
      "J  a  b  c\n",
      "K  a  b  f\n",
      "L  b  h  i\n",
      "M  b  l  c\n",
      "N  t  z  l\n",
      "\n",
      "   A  B  C  D\n",
      "J  a  b  c  1\n",
      "K  a  b  f  2\n",
      "L  b  h  i  3\n",
      "M  b  l  c  4\n",
      "N  t  z  l  5\n"
     ]
    }
   ],
   "source": [
    "df6=pd.DataFrame(np.array(list('abcabfbhiblctzl')).reshape(5,3),columns=list('ABC'),index=list('JKLMN'))\n",
    "print('before adding column D')\n",
    "print(df6)\n",
    "print()\n",
    "df6['D']=[1,2,3,4,5]\n",
    "print(df6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   A  B     C\n",
      "0  a  b  c, f\n",
      "1  b  h  i, c\n",
      "2  t  z     l\n"
     ]
    }
   ],
   "source": [
    "# above it is possible to see that rows 0 and 1 have duplicates values\n",
    "# how to get a row:\n",
    "# a b c,f\n",
    "df6= df6.groupby('A').agg({'B':'first', \n",
    "                             'C': ', '.join, \n",
    "                             }).reset_index()\n",
    "# explanation: indicating the column where I want to add.\n",
    "# indicate what to do with the other columns when adding.\n",
    "# B: I take the first because they are the same. I could do any other operation\n",
    "# Since I dont indicate anything about D it is not taking\n",
    "print(df6)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   A  B     C  D\n",
      "0  a  b  c, f  1\n",
      "1  b  h  i, c  2\n",
      "2  t  z     l  3\n"
     ]
    }
   ],
   "source": [
    "# lets assume I want to sum up the values of column D\n",
    "df7=df6\n",
    "df7['D']=[1,2,3]\n",
    "df7= df7.groupby('A').agg({'B':'first', \n",
    "                             'C': ', '.join, \n",
    "                           'D': (lambda x : x.sum())\n",
    "                             }).reset_index()\n",
    "# mio: I guess when 'B' and 'D' ... are used it is taking as a list\n",
    "print(df7)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## real case"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2, 4)\n",
      "          A         B         C         D\n",
      "0  value aa  value bb  value cc  value dd\n",
      "1  value aa  value bb  value cc  value dd\n",
      "2         4         5         6         7\n"
     ]
    }
   ],
   "source": [
    "columnsList=['A','B','C','D']\n",
    "df8=pd.DataFrame(columns=columnsList)\n",
    "L=['value aa','value bb','value cc','value dd']\n",
    "s = pd.Series(dict(zip(df8.columns, L)))\n",
    "# remember to assign again df8, \n",
    "#the next line does not work:\n",
    "df8.append(s,ignore_index=True)\n",
    "df8=df8.append(s,ignore_index=True)\n",
    "df8=df8.append(s,ignore_index=True)\n",
    "# I EXPECT HERE A 2X4 DATAFRAME. \n",
    "print(df8.shape)\n",
    "#>>> (0,4)\n",
    "# adding a row at the end of the matrix\n",
    "df8.loc[df8.index.max() + 1, :] = [4, 5, 6,7]\n",
    "print(df8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "# when including ranges and list of values the situation is similar to LOC\n",
    "# See my question in stack overflow about LOC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://stackoverflow.com/questions/53903889/logic-behind-pandas-loc-selecting-values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   A         B\n",
      "2013-01-04  0.783716  0.234898\n",
      "2013-01-05  0.836919  0.009969\n"
     ]
    }
   ],
   "source": [
    "print(df.iloc[3:5,0:2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   A         B\n",
      "2013-01-04  0.783716  0.234898\n",
      "2013-01-06  0.170976  0.776922\n"
     ]
    }
   ],
   "source": [
    "print(df.iloc[[3,5],0:2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   A         D\n",
      "2013-01-04  0.783716  0.640495\n",
      "2013-01-06  0.170976  0.884943\n"
     ]
    }
   ],
   "source": [
    "print(df.iloc[[3,5],[0,3]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   A         D\n",
      "2013-01-01  0.032454  0.351854\n",
      "2013-01-02  0.741414  0.003549\n",
      "2013-01-03  0.179602  0.413010\n",
      "2013-01-04  0.783716  0.640495\n",
      "2013-01-05  0.836919  0.408315\n",
      "2013-01-06  0.170976  0.884943\n"
     ]
    }
   ],
   "source": [
    "print(df.iloc[:,[0,3]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.00996884553278976\n"
     ]
    }
   ],
   "source": [
    "print(df.iloc[4,1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time of iloc: 2.514461040496826\n",
      "time of iat 2.409627914428711\n"
     ]
    }
   ],
   "source": [
    "import time\n",
    "#I am going to count the running time of the program\n",
    "start_time=time.time()\n",
    "for i in range(100000):\n",
    "    a=df.iloc[1,1]\n",
    "elapsed_time=time.time()-start_time\n",
    "print('time of iloc:',elapsed_time)\n",
    "start_time=time.time()\n",
    "for i in range(100000):\n",
    "    a=df.iat[1,1]\n",
    "elapsed_time=time.time()-start_time\n",
    "print('time of iat',elapsed_time)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# number of elements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "size of the dataframe is (6, 4)\n",
      "the number of rows is 6\n",
      "the number of columns is 4\n"
     ]
    }
   ],
   "source": [
    "# number of rows of a dataframe\n",
    "print('size of the dataframe is {0}'.format(df.shape))\n",
    "print('the number of rows is {0}'.format(df.shape[0]))\n",
    "print('the number of columns is {0}'.format(df.shape[1]))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# selecting data conditionally"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['app', 'SUM']\n",
      "['app', 'SUM', 'A', 'B']\n",
      "['A', 'B']\n",
      "it works: columnslist.extend(list('AB')) \n",
      "['app', 'SUM', 'A', 'B', 'A', 'B']\n",
      "this is the split text\n",
      "['this', 'is', 'a', 'text', 'to', 'be', 'splitted']\n",
      "this does not work:\n",
      "columnslist.extend(mytext.split())\n"
     ]
    }
   ],
   "source": [
    "# Starting with a dataframe similar to reality.\n",
    "indexlist=['concept1','concept2','concpet3','concept4','concept5']\n",
    "# this is the list to be extended:\n",
    "columnslist=['app','SUM']\n",
    "print(columnslist)\n",
    "\n",
    "# it is going to be extended with the values A and B\n",
    "columnslist.extend(['A','B'])\n",
    "print(columnslist)\n",
    "\n",
    "# It is possible to make a list out of a string in this way\n",
    "print(list('AB'))\n",
    "\n",
    "# I realise that list['AB']= ['A','B']\n",
    "#but this would not work:\n",
    "try:\n",
    "    columnslist.extend(list('AB'))\n",
    "    print(\"it works: columnslist.extend(list('AB')) \")\n",
    "    print(columnslist)\n",
    "    \n",
    "except:\n",
    "    print(\"columnslist.extend(list['AB']) does not work\")\n",
    "\n",
    "# what about dividing a text into words\n",
    "mytext=\"this is a text to be splitted\"\n",
    "oTextList=mytext.split()\n",
    "print('this is the split text')\n",
    "print(oTextList)\n",
    "try:\n",
    "    columnslist.extend(mytext.split())\n",
    "    print(columnlist)\n",
    "except:\n",
    "    print(\"this does not work:\")\n",
    "    print(\"columnslist.extend(mytext.split())\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           app  SUM  doc0  doc1  doc2  doc3  doc4  doc5  doc6\n",
      "concept0     1    0     0     1     1     0     1     1     0\n",
      "concept1     0    0     0     0     0     0     0     1     0\n",
      "concept2     0    0     1     0     1     0     1     1     1\n",
      "concept3     0    0     1     0     0     0     1     0     1\n",
      "concept4     1    0     1     1     1     1     0     0     0\n",
      "concept5     1    0     0     0     0     0     0     1     1\n",
      "concept6     1    0     0     1     0     1     0     0     1\n",
      "concept7     1    0     1     0     1     1     0     0     1\n",
      "concept8     1    0     0     0     0     1     1     0     1\n",
      "concept9     1    0     0     0     0     1     1     0     1\n",
      "concept10    1    0     1     0     1     0     0     0     1\n",
      "concept11    0    0     1     1     1     1     0     0     1\n",
      "concept12    1    0     1     1     1     1     0     0     0\n",
      "concept13    0    0     0     1     0     1     0     1     1\n",
      "concept14    0    0     0     1     0     1     0     0     0\n",
      "concept15    1    0     1     0     1     0     0     0     1\n",
      "concept16    1    0     0     1     1     0     1     1     1\n",
      "concept17    0    0     1     1     0     0     1     1     1\n",
      "concept18    1    0     1     1     0     1     0     1     0\n",
      "concept19    0    0     1     0     0     1     0     1     0\n",
      "add         12    0    12    11     9    12     8    10    13\n"
     ]
    }
   ],
   "source": [
    "indexlist=[]\n",
    "for i in range(20):\n",
    "    indexlist.append('concept' + str(i))\n",
    "\n",
    "indexlist.append('add')\n",
    "\n",
    "columnslist=['app','SUM']\n",
    "for i in range(7):\n",
    "    columnslist.append('doc' + str(i))\n",
    "    \n",
    "rows = len(indexlist)\n",
    "cols = len(columnslist)\n",
    "a = np.matrix(np.random.randint(0,2, size=(rows, cols)))\n",
    "matrixdf=pd.DataFrame(a,index=indexlist,columns=columnslist)\n",
    "matrixdf.loc['add']= matrixdf.sum()\n",
    "matrixdf.SUM=0\n",
    "print(matrixdf) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['app' 'SUM' 'doc0' 'doc1' 'doc2' 'doc3' 'doc4' 'doc5' 'doc6']\n",
      "['app', 'SUM', 'doc0', 'doc1', 'doc2', 'doc3', 'doc4', 'doc5', 'doc6']\n",
      "<class 'list'>\n",
      "['doc0', 'doc1', 'doc2', 'doc3', 'doc4', 'doc5', 'doc6']\n",
      "           app  SUM  doc0  doc1  doc2  doc3  doc4  doc5  doc6\n",
      "concept0     1    4     0     1     1     0     1     1     0\n",
      "concept1     0    1     0     0     0     0     0     1     0\n",
      "concept2     0    5     1     0     1     0     1     1     1\n",
      "concept3     0    3     1     0     0     0     1     0     1\n",
      "concept4     1    4     1     1     1     1     0     0     0\n",
      "concept5     1    2     0     0     0     0     0     1     1\n",
      "concept6     1    3     0     1     0     1     0     0     1\n",
      "concept7     1    4     1     0     1     1     0     0     1\n",
      "concept8     1    3     0     0     0     1     1     0     1\n",
      "concept9     1    3     0     0     0     1     1     0     1\n",
      "concept10    1    3     1     0     1     0     0     0     1\n",
      "concept11    0    5     1     1     1     1     0     0     1\n",
      "concept12    1    4     1     1     1     1     0     0     0\n",
      "concept13    0    4     0     1     0     1     0     1     1\n",
      "concept14    0    2     0     1     0     1     0     0     0\n",
      "concept15    1    3     1     0     1     0     0     0     1\n",
      "concept16    1    5     0     1     1     0     1     1     1\n",
      "concept17    0    5     1     1     0     0     1     1     1\n",
      "concept18    1    4     1     1     0     1     0     1     0\n",
      "concept19    0    3     1     0     0     1     0     1     0\n",
      "add          0    0    12    11     9    12     8    10    13\n"
     ]
    }
   ],
   "source": [
    "# how to sum the other columns \n",
    "print(matrixdf.columns.values)\n",
    "col_list= list(matrixdf.columns.values)\n",
    "print(col_list)\n",
    "print(type(col_list))\n",
    "col_list.remove('app')\n",
    "col_list.remove('SUM')\n",
    "print(col_list)\n",
    "matrixdf['SUM'] = matrixdf[col_list].sum(axis=1)\n",
    "matrixdf.loc['add','app']=0\n",
    "matrixdf.loc['add','SUM']=0\n",
    "#matrixdf.app()\n",
    "print(matrixdf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           app  SUM  doc0  doc1  doc2  doc3  doc4  doc5  doc6\n",
      "concept0     1    4     0     1     1     0     1     1     0\n",
      "concept4     1    4     1     1     1     1     0     0     0\n",
      "concept5     1    2     0     0     0     0     0     1     1\n",
      "concept6     1    3     0     1     0     1     0     0     1\n",
      "concept7     1    4     1     0     1     1     0     0     1\n",
      "concept8     1    3     0     0     0     1     1     0     1\n",
      "concept9     1    3     0     0     0     1     1     0     1\n",
      "concept10    1    3     1     0     1     0     0     0     1\n",
      "concept12    1    4     1     1     1     1     0     0     0\n",
      "concept15    1    3     1     0     1     0     0     0     1\n",
      "concept16    1    5     0     1     1     0     1     1     1\n",
      "concept18    1    4     1     1     0     1     0     1     0\n",
      "add         12   42     6     6     7     7     4     4     8\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/jose/code/env/first/lib/python3.6/site-packages/ipykernel_launcher.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  after removing the cwd from sys.path.\n"
     ]
    }
   ],
   "source": [
    "# Filter out ot the matrix the subframe of the concepts of the application\n",
    "# Using the filtering >0 in this way I can use RCS by Madou\n",
    "ApplicationMatrix=matrixdf[matrixdf.app > 0]\n",
    "ApplicationMatrix.loc['add']= ApplicationMatrix.sum()\n",
    "print(ApplicationMatrix)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           SUM  app  doc6  doc2  doc3  doc0  doc1  doc4  doc5\n",
      "concept0     4    1     0     1     0     0     1     1     1\n",
      "concept4     4    1     0     1     1     1     1     0     0\n",
      "concept5     2    1     1     0     0     0     0     0     1\n",
      "concept6     3    1     1     0     1     0     1     0     0\n",
      "concept7     4    1     1     1     1     1     0     0     0\n",
      "concept8     3    1     1     0     1     0     0     1     0\n",
      "concept9     3    1     1     0     1     0     0     1     0\n",
      "concept10    3    1     1     1     0     1     0     0     0\n",
      "concept12    4    1     0     1     1     1     1     0     0\n",
      "concept15    3    1     1     1     0     1     0     0     0\n",
      "concept16    5    1     1     1     0     0     1     1     1\n",
      "concept18    4    1     0     0     1     1     1     0     1\n",
      "add         42   12     8     7     7     6     6     4     4\n"
     ]
    }
   ],
   "source": [
    "# Sort the columns of the matrix according to the last row\n",
    "# the last row \"add\" is the number of concepts that appear in the document.\n",
    "ApplicationMatrix = ApplicationMatrix.sort_values(ApplicationMatrix.last_valid_index(), axis=1, ascending=False)\n",
    "print(ApplicationMatrix)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10\n"
     ]
    }
   ],
   "source": [
    "# lets try to get the docs that have at least 80% of the concepts\n",
    "# this value might be change by user\n",
    "Similarity=80\n",
    "#How many concepts are there?\n",
    "nrConcepts=len(ApplicationMatrix.index)\n",
    "nrDocs=round(80/100*len(ApplicationMatrix.index))\n",
    "print(nrDocs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# sorting the values of a dataframe sort"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            coverage   name  reports  year\n",
      "Cochice            2  Jason        1  2012\n",
      "Pima               2  Molly        2  2012\n",
      "Santa Cruz         3   Tina        1  2013\n",
      "Maricopa           3   Jake        2  2014\n",
      "Yuma               3    Amy        3  2014\n"
     ]
    }
   ],
   "source": [
    "data = {'name': ['Jason', 'Molly', 'Tina', 'Jake', 'Amy'], \n",
    "        'year': [2012, 2012, 2013, 2014, 2014], \n",
    "        'reports': [1, 2, 1, 2, 3],\n",
    "        'coverage': [2, 2, 3, 3, 3]}\n",
    "df = pd.DataFrame(data, index = ['Cochice', 'Pima', 'Santa Cruz', 'Maricopa', 'Yuma'])\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "df.sort_values(by='reports', ascending=0, inplace=True)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            coverage   name  reports  year\n",
      "Yuma               3    Amy        3  2014\n",
      "Maricopa           3   Jake        2  2014\n",
      "Santa Cruz         3   Tina        1  2013\n",
      "Pima               2  Molly        2  2012\n",
      "Cochice            2  Jason        1  2012\n"
     ]
    }
   ],
   "source": [
    "# it is possible to sort on more than one column.\n",
    "# first one column then the other one.\n",
    "print(df.sort_values(by=['coverage', 'reports'], ascending=False))\n",
    "# see how first the column coverage is ordered and then reports"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# A gentle introduction to Pandas (by medium.com)\n",
    "\n",
    "https://medium.com/@harrypotter0/an-introduction-to-data-analysis-with-pandas-27ecbce2853"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}