Pandas bootcamp.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas bootcamp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://pandasbootcamp.herokuapp.com/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing bootcamp1.tsv\n"
     ]
    }
   ],
   "source": [
    "%%file bootcamp1.tsv\n",
    "date\tnum\tnum2\tstr\tstr2\n",
    "2014-02-01\t663\t431.396160\tboy\tred\n",
    "2014-02-09\t643\t422.855973\tgirl\tred\n",
    "2014-02-18\t158\t48.205905\tboy\tblue\n",
    "2014-03-19\t263\t242.969867\tboy\tyellow\n",
    "2014-03-26\t876\t60.587023\tboy\tpurple\n",
    "2014-03-29\t944\t769.996271\tboy\tgreen\n",
    "2014-04-21\t75\t835.692630\tgirl\tred\n",
    "2014-05-18\t79\t99.810889\tboy\tpurple\n",
    "2014-06-12\t842\t544.683391\tboy\tpurple\n",
    "2014-07-09\t3\t868.341070\tboy\tyellow\n",
    "2014-08-06\t521\t42.628203\tgirl\tred\n",
    "2014-09-23\t247\t110.183157\tboy\tred\n",
    "2014-09-29\t701\t939.721699\tboy\tyellow\n",
    "2014-10-15\t835\t711.579429\tgirl\tblue\n",
    "2014-10-21\t640\t633.604265\tboy\tyellow\n",
    "2014-11-19\t727\t237.534784\tgirl\tred\n",
    "2015-01-21\t715\t169.189147\tboy\tgreen\n",
    "2015-02-19\t508\t662.156362\tboy\tred\n",
    "2015-02-24\t298\t859.427413\tgirl\tyellow\n",
    "2015-04-10\t684\t659.694331\tgirl\tgreen\n",
    "2015-06-11\t905\t117.203320\tgirl\tblue\n",
    "2015-06-14\t977\t576.147780\tgirl\tblue\n",
    "2015-07-14\t538\t739.206595\tboy\tyellow\n",
    "2015-07-22\t68\t879.446180\tgirl\tblue\n",
    "2015-07-27\t437\t162.084731\tboy\tgreen\n",
    "2015-07-29\t419\t449.273961\tboy\tred\n",
    "2015-07-30\t500\t589.384323\tboy\tgreen\n",
    "2015-08-14\t753\t283.https://pandasbootcamp.herokuapp.com/460551\tgirl\tyellow\n",
    "2015-08-25\t292\t652.074501\tboy\tgreen\n",
    "2015-09-05\t209\t63.404787\tboy\tpurple\n",
    "2015-10-02\t298\t400.493463\tgirl\tgreen\n",
    "2015-10-05\t399\t299.972968\tboy\tgreen\n",
    "2015-10-05\t571\t58.570900\tgirl\tpurple\n",
    "2015-10-12\t655\t610.143245\tboy\tblue\n",
    "2015-11-16\t638\t430.080939\tgirl\tgreen\n",
    "2015-11-22\t179\t237.256851\tgirl\tblue\n",
    "2015-12-10\t353\t541.081251\tgirl\tred\n",
    "2015-12-12\t684\t46.488439\tboy\tred\n",
    "2016-04-09\t831\t55.519112\tgirl\tyellow\n",
    "2016-04-09\t951\t687.240484\tgirl\tyellow\n",
    "2016-04-30\t289\t754.413019\tgirl\tred\n",
    "2016-05-12\t32\t986.133777\tgirl\tpurple\n",
    "2016-05-23\t63\t344.635461\tgirl\tpurple\n",
    "2016-06-06\t712\t634.034360\tboy\tpurple\n",
    "2016-06-24\t800\t216.198437\tboy\tgreen\n",
    "2016-07-11\t403\t87.872617\tboy\tred\n",
    "2016-09-08\t21\t494.308010\tboy\tyellow\n",
    "2016-09-14\t565\t79.563044\tboy\tgreen\n",
    "2016-09-21\t428\t202.313860\tgirl\tgreen\n",
    "2016-09-22\t460\t565.415256\tboy\tgreen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "231.60384000000005"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('bootcamp1.tsv', delimiter='\\t', parse_dates=True)\n",
    "earliest = df[df['date'] == df['date'].min()]\n",
    "result, = (earliest['num'] - earliest['num2']).values\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing bootcamp2.tsv\n"
     ]
    }
   ],
   "source": [
    "%%file bootcamp2.tsv\n",
    "Pandas Bootcamp\n",
    "\n",
    "date\tnum\tnum2\tstr\tstr2\n",
    "2014-03-19\t989\t931.433333\tgirl\tyellow\n",
    "2014-04-01\t299\t457.610045\tgirl\tblue\n",
    "2014-05-15\t109\t485.188116\tgirl\tgreen\n",
    "2014-05-18\t740\t205.706471\tboy\tyellow\n",
    "2014-05-19\t259\t22.160529\tgirl\tyellow\n",
    "2014-05-31\t482\t914.970070\tgirl\tyellow\n",
    "2014-06-24\t424\t345.931005\tboy\tred\n",
    "2014-07-03\t897\t710.997391\tboy\tyellow\n",
    "2014-07-12\t365\t918.453764\tboy\tred\n",
    "2014-07-24\t156\t135.273739\tboy\tyellow\n",
    "2014-07-31\t16\t908.126265\tgirl\tyellow\n",
    "2014-10-06\t975\t91.660146\tgirl\tpurple\n",
    "2014-10-29\t36\t729.769868\tboy\tred\n",
    "2014-11-04\t449\t390.806579\tgirl\tred\n",
    "2014-11-30\t517\t555.375993\tboy\tpurple\n",
    "2014-12-13\t804\t319.616147\tgirl\tgreen\n",
    "2015-01-05\t174\t629.065720\tboy\tblue\n",
    "2015-02-19\t167\t840.140143\tboy\tpurple\n",
    "2015-03-02\t721\t211.583033\tboy\tgreen\n",
    "2015-03-03\t554\t48.738002\tgirl\tblue\n",
    "2015-03-06\t339\t959.654915\tboy\tblue\n",
    "2015-03-12\t970\t875.130925\tboy\tgreen\n",
    "2015-04-04\t497\t776.021489\tboy\tpurple\n",
    "2015-04-10\t181\t180.206955\tgirl\tgreen\n",
    "2015-04-30\t348\t999.251139\tboy\tred\n",
    "2015-05-07\t570\t954.350178\tboy\tgreen\n",
    "2015-05-11\t677\t180.727701\tgirl\tgreen\n",
    "2015-05-21\t750\t671.088331\tboy\tblue\n",
    "2015-07-29\t400\t174.821474\tboy\tpurple\n",
    "2015-11-01\t132\t882.789025\tboy\tgreen\n",
    "2016-01-09\t63\t141.145834\tboy\tred\n",
    "2016-01-21\t952\t195.574950\tboy\tblue\n",
    "2016-01-22\t319\t833.798559\tgirl\tyellow\n",
    "2016-02-03\t322\t533.223166\tgirl\tgreen\n",
    "2016-02-25\t286\t190.804209\tboy\tblue\n",
    "2016-03-07\t951\t780.581858\tboy\tblue\n",
    "2016-03-14\t774\t659.288279\tgirl\tyellow\n",
    "2016-04-11\t143\t440.066966\tboy\tred\n",
    "2016-04-14\t916\t843.169470\tgirl\tpurple\n",
    "2016-04-16\t483\t857.519467\tgirl\tblue\n",
    "2016-05-06\t547\t854.941835\tboy\tpurple\n",
    "2016-06-01\t132\t962.545298\tboy\tgreen\n",
    "2016-06-05\t772\t903.660721\tgirl\tblue\n",
    "2016-06-27\t866\t662.843370\tgirl\tyellow\n",
    "2016-07-03\t397\t771.970807\tboy\tgreen\n",
    "2016-08-02\t191\t292.201484\tgirl\tred\n",
    "2016-08-22\t160\t62.561427\tboy\tgreen\n",
    "2016-08-25\t865\t212.907342\tboy\tgreen\n",
    "2016-09-02\t680\t768.457790\tgirl\tpurple\n",
    "2016-09-24\t1\t479.408738\tgirl\tgreen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('bootcamp2.tsv', delimiter='\\t', skiprows=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "559.06640122"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['num2'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overwriting bootcamp3.tsv\n"
     ]
    }
   ],
   "source": [
    "%%file bootcamp3.tsv\n",
    "Pandas Bootcamp\n",
    "\n",
    "date\tnum\tnum2\tstr\tstr2\n",
    "2014-01-11\t425\t316.980166\tgirl\tyellow\n",
    "2014-01-14\t380\t753.987245\tboy\tred\n",
    "2014-03-03\t504\t683.604602\tgirl\tgreen\n",
    "2014-03-19\t92\t54.103423\tgirl\tyellow\n",
    "2014-03-22\t652\t458.135051\tboy\tred\n",
    "2014-05-04\t78\t754.538073\tboy\tblue\n",
    "2014-05-18\t360\t238.759123\tgirl\tblue\n",
    "2014-06-10\t517\t830.540505\tboy\tpurple\n",
    "2014-07-19\t822\t826.162928\tgirl\tpurple\n",
    "2014-08-17\t712\t361.156380\tboy\tpurple\n",
    "2014-08-29\t155\t524.199506\tboy\tred\n",
    "2014-09-17\t232\t374.757023\tgirl\tpurple\n",
    "2014-09-20\t502\t841.795939\tboy\tblue\n",
    "2014-10-01\t898\t616.012001\tboy\tgreen\n",
    "2014-10-13\t465\t700.375645\tboy\tyellow\n",
    "2014-10-16\t88\t842.862302\tgirl\tpurple\n",
    "2014-12-30\t707\t560.122707\tboy\tgreen\n",
    "2015-01-08\t240\t585.791341\tboy\tpurple\n",
    "2015-01-13\t947\t188.948914\tboy\tgreen\n",
    "2015-01-30\t795\t721.538255\tboy\tred\n",
    "2015-02-27\t55\t449.128106\tboy\tgreen\n",
    "2015-03-04\t504\t156.417901\tboy\tgreen\n",
    "2015-04-24\t739\t723.670925\tboy\tblue\n",
    "2015-06-06\t290\t413.591839\tboy\tpurple\n",
    "2015-06-13\t836\t463.234865\tboy\tyellow\n",
    "2015-06-25\t289\t720.606904\tboy\tred\n",
    "2015-06-26\t105\t561.145244\tboy\tred\n",
    "2015-07-01\t192\t690.666830\tboy\tpurple\n",
    "2015-07-02\t384\t96.705835\tgirl\tblue\n",
    "2015-09-12\t668\t748.279469\tboy\tgreen\n",
    "2015-10-05\t699\t100.475509\tboy\tyellow\n",
    "2015-10-20\t777\t342.218190\tgirl\tpurple\n",
    "2015-11-03\t20\t736.263230\tgirl\tpurple\n",
    "2015-11-10\t342\t716.622462\tgirl\tred\n",
    "2015-12-07\t835\t52.116061\tboy\tblue\n",
    "2015-12-23\t479\t710.700008\tboy\tyellow\n",
    "2016-01-02\t38\t188.787582\tboy\tgreen\n",
    "2016-01-31\t765\t924.163054\tboy\tyellow\n",
    "2016-02-20\t304\t606.213815\tboy\tblue\n",
    "2016-02-26\t69\t416.569350\tgirl\tpurple\n",
    "2016-03-07\t757\t128.622282\tboy\tyellow\n",
    "2016-05-01\t454\t245.508640\tgirl\tyellow\n",
    "2016-05-03\t229\t279.101912\tboy\tgreen\n",
    "2016-06-17\t246\t911.940265\tgirl\tpurple\n",
    "2016-07-04\t791\t685.360372\tboy\tblue\n",
    "2016-08-01\t289\t216.389045\tboy\tgreen\n",
    "2016-08-12\t458\t386.966497\tgirl\tpurple\n",
    "2016-08-15\t916\t543.228738\tboy\tblue\n",
    "2016-08-23\t824\t267.902308\tgirl\tred\n",
    "2016-09-20\t380\t501.423946\tgirl\tpurple"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('bootcamp3.tsv', delimiter='\\t', skiprows=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num</th>\n",
       "      <th>num2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>str</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>boy</th>\n",
       "      <td>16828</td>\n",
       "      <td>17259.042023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>girl</th>\n",
       "      <td>6477</td>\n",
       "      <td>7959.350290</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        num          num2\n",
       "str                      \n",
       "boy   16828  17259.042023\n",
       "girl   6477   7959.350290"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sums = df.groupby('str').sum()\n",
    "sums"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16828"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sums.loc['boy', 'num']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16828"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['str'] == 'boy']['num'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overwriting bootcamp4.tsv\n"
     ]
    }
   ],
   "source": [
    "%%file bootcamp4.tsv\n",
    "Pandas Bootcamp\n",
    "\n",
    "date\tnum\tnum2\tstr\tstr2\n",
    "2014-01-02\t97\t569.572320\tboy\tpurple\n",
    "2014-01-19\t460\t974.935766\tgirl\tyellow\n",
    "2014-01-21\t989\t436.263500\tgirl\tyellow\n",
    "2014-02-16\t439\t953.438446\tgirl\tblue\n",
    "2014-04-04\t112\t486.978815\tgirl\tyellow\n",
    "2014-05-01\t739\t482.150378\tgirl\tblue\n",
    "2014-05-14\t617\t627.349695\tgirl\tgreen\n",
    "2014-06-08\t258\t748.490738\tboy\tyellow\n",
    "2014-06-14\t627\t679.897534\tgirl\tyellow\n",
    "2014-07-10\t708\t578.400840\tgirl\tgreen\n",
    "2014-08-30\t649\t335.045781\tgirl\tyellow\n",
    "2014-08-30\t528\t996.830337\tboy\tgreen\n",
    "2014-09-04\t161\t867.246292\tboy\tgreen\n",
    "2014-09-29\t164\t661.445575\tboy\tyellow\n",
    "2014-10-28\t230\t422.476554\tgirl\tpurple\n",
    "2014-10-28\t318\t982.028435\tboy\tyellow\n",
    "2014-11-01\t377\t924.494862\tgirl\tpurple\n",
    "2014-11-30\t549\t653.950097\tgirl\tblue\n",
    "2014-12-01\t26\t778.754410\tboy\tpurple\n",
    "2014-12-14\t90\t942.687870\tboy\tblue\n",
    "2014-12-28\t614\t572.836111\tboy\tblue\n",
    "2015-01-11\t988\t789.138854\tgirl\tred\n",
    "2015-04-27\t614\t321.318443\tgirl\tyellow\n",
    "2015-05-16\t929\t669.390434\tgirl\tgreen\n",
    "2015-05-29\t449\t472.638162\tgirl\tyellow\n",
    "2015-07-08\t915\t391.840372\tboy\tyellow\n",
    "2015-07-16\t988\t450.994443\tgirl\tred\n",
    "2015-07-30\t99\t787.530102\tboy\tblue\n",
    "2015-08-04\t964\t188.273812\tgirl\tyellow\n",
    "2015-08-29\t587\t317.978754\tgirl\tgreen\n",
    "2015-09-09\t838\t70.636845\tgirl\tgreen\n",
    "2015-10-21\t167\t640.797087\tboy\tgreen\n",
    "2015-12-14\t527\t908.070246\tboy\tblue\n",
    "2015-12-19\t562\t64.103513\tboy\tpurple\n",
    "2015-12-24\t985\t189.088120\tboy\tred\n",
    "2016-01-06\t100\t388.447901\tgirl\tgreen\n",
    "2016-01-17\t254\t518.057217\tboy\tgreen\n",
    "2016-01-28\t518\t267.122756\tgirl\tgreen\n",
    "2016-03-04\t704\t742.864890\tgirl\tyellow\n",
    "2016-04-04\t827\t741.301791\tboy\tyellow\n",
    "2016-04-05\t799\t195.608335\tgirl\tred\n",
    "2016-05-10\t4\t340.484478\tboy\tyellow\n",
    "2016-05-29\t728\t147.594808\tboy\tgreen\n",
    "2016-06-10\t628\t359.328103\tgirl\tpurple\n",
    "2016-07-04\t357\t529.696059\tboy\tgreen\n",
    "2016-08-18\t464\t601.406913\tgirl\tblue\n",
    "2016-08-27\t683\t881.393584\tboy\tyellow\n",
    "2016-08-30\t105\t905.976737\tgirl\tpurple\n",
    "2016-09-08\t317\t777.645155\tboy\tblue\n",
    "2016-09-25\t433\t414.769892\tboy\tyellow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('bootcamp4.tsv', delimiter='\\t', skiprows=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df['num'] > df['num2']).astype(int).sum()"
   ]
  },
  {
   "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}