pandas_example.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas\n",
    "\n",
    "Pandas is a package used for managing data.\n",
    "\n",
    "Pandas main use is that it creates 2 new data types for storing data: series and dataframe.\n",
    "\n",
    "Think of a pandas dataframe like an excel spreadsheet that is storing some data.  One column can have customer name, one column can have product sold name, another column can have price or quantity... Then the rows could be individual sales.\n",
    "\n",
    "A dataframe is made up of several series.  Each column of a dataframe is a series.\n",
    "\n",
    "We can name each column and row of a dataframe.\n",
    "\n",
    "A pandas dataframe is very similar to a data.frame in R.\n",
    "\n",
    "Similar to numpy arrays, a dataframe is a more robust data type for storing data than lists of lists. Dataframes are more flexible than numpy arrays.\n",
    "\n",
    "A numpy array can create a matrix with all entries of the same data type.  In a dataframe each column can have its own datatype.  \n",
    "\n",
    "That's not to say numpy arrays aren't useful.  It is often easiest to convert some subset of a dataframe to a numpy array and then use that to do some math.\n",
    "\n",
    "Pandas also has SQL-like functions for merging, joining, and sorting dataframes.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np  # numpy is not necessary for pandas, but we will use some np code in this example\n",
    "# in general it's good practice to import all pacakages at the beginning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# first let's look at series - think of this as a single column of a spreadsheet\n",
    "# each entry in a series corresponds to an individual row in the spreadsheet\n",
    "# we can create a series by converting a list, or numpy array\n",
    "\n",
    "mylist = [5.4,6.1,1.7,99.8]\n",
    "myarray = np.array(mylist)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "myseries1 = pd.Series(data=mylist)\n",
    "print(myseries1)\n",
    "myseries2 = pd.Series(data=myarray)\n",
    "print(myseries2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we access individual entries the same way as with lists and arrays\n",
    "print(myseries1[2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can add labels to the entries of a series\n",
    "\n",
    "mylabels = ['first','second','third','fourth']\n",
    "myseries3 = pd.Series(data=mylist,index=mylabels)\n",
    "print(myseries3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we need not be explicit about the entries of pd.Series\n",
    "myseries4 = pd.Series(mylist,mylabels)\n",
    "print(myseries4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can also access entries using the index labels\n",
    "print(myseries4['second'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can do math on series \n",
    "myseries5 = pd.Series([5.5,1.1,8.8,1.6],['first','third','fourth','fifth'])\n",
    "print(myseries5)\n",
    "print('')\n",
    "print(myseries5+myseries4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can combine series to create a dataframe using the concat function\n",
    "df1 = pd.concat([myseries4,myseries5],axis=1,sort=False)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can create a new dataframe \n",
    "df2 = pd.DataFrame(np.random.randn(5,5))\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# lets give labels to rows and columns\n",
    "df3 = pd.DataFrame(np.random.randn(5,5),index=['first row','second row','third row','fourth row','fifth row'],\n",
    "                   columns=['first col','second col','third col','fourth col','fifth col'])\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can access individual series in a data frame\n",
    "print(df3['second col'])\n",
    "print('')\n",
    "df3[['third col','first col']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can access rows of a dataframe\n",
    "df3.loc['fourth row']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.iloc[2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.loc[['fourth row','first row'],['second col','third col']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can use logical indexing for dataframes just like for numpy arrays\n",
    "df3>0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df3[df3>0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can add columns to a dataframe\n",
    "df3['sixth col'] = np.random.randn(5,1)\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can remove columns or rows from a dataframe\n",
    "df3.drop('first col',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df4 = df3.drop('first col',axis=1)\n",
    "df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df5 = df3.drop('second row',axis=0)\n",
    "df5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can remove a dataframe's index labels\n",
    "df5.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df5.reset_index(inplace=True)\n",
    "df5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can assign new names to the index\n",
    "df5['new name'] = ['This','is','the','row']\n",
    "df5\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df5.set_index('new name',inplace=True)\n",
    "df5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Combining data frames\n",
    "\n",
    "The ways dataframes are combined in pandas is similar to SQL\n",
    "\n",
    "We will examine 3 methods for combining dataframes\n",
    "\n",
    "1. concat\n",
    "2. join\n",
    "3. merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "df7 = pd.DataFrame({\"customer\":['101','102','103','104'], \n",
    "                    'category': ['cat2','cat2','cat1','cat3'],\n",
    "                    'important': ['yes','no','yes','yes'],\n",
    "                    'sales': [123,52,214,663]},index=[0,1,2,3])\n",
    "\n",
    "df8 = pd.DataFrame({\"customer\":['101','103','104','105'], \n",
    "                    'color': ['yellow','green','green','blue'],\n",
    "                    'distance': [12,9,44,21],\n",
    "                    'sales': [123,214,663,331]},index=[4,5,6,7])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([df7,df8],axis=0,sort=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([df7,df8],axis=0,sort=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([df7,df8],axis=1,sort=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merge and Join\n",
    "\n",
    "Merge combines dataframes using a column's values to identify common entries\n",
    "\n",
    "Join combines dataframes using the index to identify common entries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df7,df8,how='outer',on='customer') # outer merge is union of on"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df7,df8,how='inner',on='customer') # inner merge is intersection of on"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df7,df8,how='right',on='customer') # left merge is just first on, but all columns ... right is second"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df9 = pd.DataFrame({'Q1': [101,102,103],\n",
    "                    'Q2': [201,202,203]},\n",
    "                   index=['I0','I1','I2'])\n",
    "\n",
    "df10 = pd.DataFrame({'Q3': [301,302,303],\n",
    "                    'Q4': [401,402,403]},\n",
    "                   index=['I0','I2','I3'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# join behaves just like merge, \n",
    "# except instead of using the values of one of the columns \n",
    "# to combine data frames, it uses the index labels\n",
    "df9.join(df10,how='right') # outer, inner, left, and right work the same as merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# let's now go over a few more basic functialities of pandas\n",
    "\n",
    "df8['color'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df8['color'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df9.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df8.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df = df8[(df8['customer']!='105') & (df8['color']!='green')]\n",
    "new_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df8['sales'].sum())\n",
    "print(df8['distance'].min())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def profit(s):\n",
    "    return s*0.5 # 50% markup..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df8['sales'].apply(profit)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df8['color'].apply(len)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df11 = df8[['distance','sales']]\n",
    "df11.applymap(profit)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df11"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def col_sum(co):\n",
    "    return sum(co)\n",
    "df11.apply(col_sum)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df11.apply(col_sum)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "del df8['color']\n",
    "df8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df8.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df8.sort_values(by='distance',inplace=True)\n",
    "df8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# if some series has multiple of the same value then we can group all the unique entries together\n",
    "mydict = {'customer': ['Customer 1','Customer 1','Customer2','Customer2','Customer3','Customer3'], \n",
    "          'product1': [1.1,2.1,3.8,4.2,5.5,6.9],\n",
    "          'product2': [8.2,9.1,11.1,5.2,44.66,983]}\n",
    "df6 = pd.DataFrame(mydict,index=['Purchase 1','Purchase 2','Purchase 3','Purchase 4','Purchase 5','Purchase 6'])\n",
    "df6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_data = df6.groupby('customer')\n",
    "print(grouped_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_data.std()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# similar to numpy arrays, we can also save and load dataframes to csv files, and also Excel files\n",
    "\n",
    "df8.to_csv('df8.csv',index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df8 = pd.read_csv('df8.csv',index_col=0)\n",
    "new_df8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df8.to_excel('df8.xlsx',index=False,sheet_name='first sheet')\n",
    "newer_df8 = pd.read_excel('df8.xlsx',sheet_name='first sheet',index_col=1)\n",
    "newer_df8"
   ]
  }
 ],
 "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}