Clockify_Pandas_Example.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "70107fe8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Note: This script will fail to run until the consultant_df is filled with valid entries.\n",
    "\n",
    "\n",
    "import requests as reqs, pandas as pd\n",
    "from datetime import datetime\n",
    "from dateutil.relativedelta import relativedelta\n",
    "\n",
    "def print_summary_report(consultant_name = 'Ryan', fromToList =[]):\n",
    "    '''\n",
    "    Prints a CLI summary report for each consultant (last 30 days)\n",
    "    '''\n",
    "\n",
    "    def consultant_dataframe():\n",
    "        '''\n",
    "        Setting up pandas DF to store consultant identifiers\n",
    "        '''\n",
    "        consultant_df = pd.DataFrame( # creating dataframe to hold employee info\n",
    "            data= {\n",
    "                'API Key': [\n",
    "                '#########################'\n",
    "                ],\n",
    "                'ID': [\n",
    "                '#########################'\n",
    "                ]\n",
    "             },\n",
    "            index= [\n",
    "                'Ryan',\n",
    "    ]\n",
    "        )\n",
    "        return consultant_df\n",
    "\n",
    "    consultant_df = consultant_dataframe() # Initialize the consultant DF\n",
    "    #date_range = handle_dates()\n",
    "        \n",
    "    API, ID  = consultant_df.loc[consultant_name,'API Key'] , consultant_df.loc[consultant_name,'ID']\n",
    "    \n",
    "    \n",
    "    r = reqs.post( # creating the request object\n",
    "        url= f'######## PATH ############', #static URL for the summary endpoint\n",
    "        headers = { # headers for summary request\n",
    "            \"content-type\": \"application/json\", \n",
    "            \"X-Api-Key\":API\n",
    "        }, \n",
    "        json = { # JSON payload to specify additional parameters\n",
    "            \"dateRangeStart\": str(fromToList[0]),\n",
    "            \"dateRangeEnd\": str(fromToList[1]),\n",
    "            \"summaryFilter\": {\"groups\": [\"PROJECT\"]},\n",
    "            \"sortOrder\": \"DESCENDING\"\n",
    "        }\n",
    "    )\n",
    "    \n",
    "    json2dict = dict(r.json())\n",
    "    df2 = pd.DataFrame(json2dict['groupOne'])\n",
    "    df2['duration'] /=3600\n",
    "    df2['Consultant_Name'] = consultant_name\n",
    "    df2.drop(['amounts','amount','name','nameLowerCase','color'], axis=1, inplace=True)\n",
    "    \n",
    "    \n",
    "    rates_df = pd.read_excel(r'C:\\Users\\mryan\\Desktop\\Rates.xlsx')\n",
    "    #rates_df.dropna(inplace=True)\n",
    "    rates_df.set_index('User', inplace=True)\n",
    "    \n",
    "    #df2.join(rates_df, on='Consultant_Name', how='left')\n",
    "    df3 = df2.merge(rates_df, how = 'left', left_on=['Consultant_Name', 'clientName'], right_on = ['User','Client'])\n",
    "    #df3.dropna(inplace=True)\n",
    "    df3.drop(['_id','clientName','Pay_Rate','To','cc'], axis=1, inplace=True)\n",
    "    df3.dropna(inplace=True)\n",
    "    for i in df3:\n",
    "        df3['Bill_Total'] = df3['duration'] * df3['Bill_Rate']\n",
    "    \n",
    "    #print('\\n' + consultant_name)\n",
    "    #display(df3)\n",
    "    \n",
    "    return df3\n",
    "    \n",
    "    \n",
    "    #print('\\n' + consultant_name)\n",
    "    #print(df2.loc[0:,['clientName', 'duration']].groupby('clientName').sum())\n",
    "    #print('--------------------------------------------------------------------------------')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2dce26b9",
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'Ryan Moore'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "File \u001b[1;32mc:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexes\\base.py:3621\u001b[0m, in \u001b[0;36mIndex.get_loc\u001b[1;34m(self, key, method, tolerance)\u001b[0m\n\u001b[0;32m   3620\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[1;32m-> 3621\u001b[0m     \u001b[39mreturn\u001b[39;00m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_engine\u001b[39m.\u001b[39;49mget_loc(casted_key)\n\u001b[0;32m   3622\u001b[0m \u001b[39mexcept\u001b[39;00m \u001b[39mKeyError\u001b[39;00m \u001b[39mas\u001b[39;00m err:\n",
      "File \u001b[1;32mc:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\_libs\\index.pyx:136\u001b[0m, in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "File \u001b[1;32mc:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\_libs\\index.pyx:163\u001b[0m, in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "File \u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi:5198\u001b[0m, in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "File \u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi:5206\u001b[0m, in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: 'Ryan Moore'",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32mc:\\Users\\mryan\\Documents\\Py_Files\\Clockify_Pandas.ipynb Cell 2\u001b[0m in \u001b[0;36m<cell line: 38>\u001b[1;34m()\u001b[0m\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=32'>33</a>\u001b[0m     \u001b[39m# dfList = [print_summary_report(i, handle_dates(fYear,fMonth,fDay, addMonths)) for i in names]\u001b[39;00m\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=33'>34</a>\u001b[0m     \u001b[39m# return dfList\u001b[39;00m\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=35'>36</a>\u001b[0m     \u001b[39mreturn\u001b[39;00m dfList\n\u001b[1;32m---> <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=37'>38</a>\u001b[0m totals_df \u001b[39m=\u001b[39m pd\u001b[39m.\u001b[39mconcat(report_printer(\u001b[39m2022\u001b[39;49m,\u001b[39m7\u001b[39;49m,\u001b[39m1\u001b[39;49m,\u001b[39m1\u001b[39;49m))\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=38'>39</a>\u001b[0m totals_df\u001b[39m.\u001b[39mgroupby([\u001b[39m'\u001b[39m\u001b[39mClient\u001b[39m\u001b[39m'\u001b[39m])\u001b[39m.\u001b[39msum()\n",
      "\u001b[1;32mc:\\Users\\mryan\\Documents\\Py_Files\\Clockify_Pandas.ipynb Cell 2\u001b[0m in \u001b[0;36mreport_printer\u001b[1;34m(fYear, fMonth, fDay, addMonths, names)\u001b[0m\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=27'>28</a>\u001b[0m dfList \u001b[39m=\u001b[39m []\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=28'>29</a>\u001b[0m \u001b[39mfor\u001b[39;00m i \u001b[39min\u001b[39;00m names:\n\u001b[1;32m---> <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=29'>30</a>\u001b[0m     report_df_temp \u001b[39m=\u001b[39m print_summary_report(i, handle_dates(fYear,fMonth,fDay, addMonths))\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=30'>31</a>\u001b[0m     dfList\u001b[39m.\u001b[39mappend(report_df_temp)\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=32'>33</a>\u001b[0m \u001b[39m# dfList = [print_summary_report(i, handle_dates(fYear,fMonth,fDay, addMonths)) for i in names]\u001b[39;00m\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=33'>34</a>\u001b[0m \u001b[39m# return dfList\u001b[39;00m\n",
      "\u001b[1;32mc:\\Users\\mryan\\Documents\\Py_Files\\Clockify_Pandas.ipynb Cell 2\u001b[0m in \u001b[0;36mprint_summary_report\u001b[1;34m(consultant_name, fromToList)\u001b[0m\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=28'>29</a>\u001b[0m consultant_df \u001b[39m=\u001b[39m consultant_dataframe() \u001b[39m# Initialize the consultant DF\u001b[39;00m\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=29'>30</a>\u001b[0m \u001b[39m#date_range = handle_dates()\u001b[39;00m\n\u001b[1;32m---> <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=31'>32</a>\u001b[0m API, ID  \u001b[39m=\u001b[39m consultant_df\u001b[39m.\u001b[39;49mloc[consultant_name,\u001b[39m'\u001b[39;49m\u001b[39mAPI Key\u001b[39;49m\u001b[39m'\u001b[39;49m] , consultant_df\u001b[39m.\u001b[39mloc[consultant_name,\u001b[39m'\u001b[39m\u001b[39mID\u001b[39m\u001b[39m'\u001b[39m]\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=34'>35</a>\u001b[0m r \u001b[39m=\u001b[39m reqs\u001b[39m.\u001b[39mpost( \u001b[39m# creating the request object\u001b[39;00m\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=35'>36</a>\u001b[0m     url\u001b[39m=\u001b[39m \u001b[39mf\u001b[39m\u001b[39m'\u001b[39m\u001b[39m######## PATH ############\u001b[39m\u001b[39m'\u001b[39m, \u001b[39m#static URL for the summary endpoint\u001b[39;00m\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=36'>37</a>\u001b[0m     headers \u001b[39m=\u001b[39m { \u001b[39m# headers for summary request\u001b[39;00m\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=45'>46</a>\u001b[0m     }\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=46'>47</a>\u001b[0m )\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/mryan/Documents/Py_Files/Clockify_Pandas.ipynb#W1sZmlsZQ%3D%3D?line=48'>49</a>\u001b[0m json2dict \u001b[39m=\u001b[39m \u001b[39mdict\u001b[39m(r\u001b[39m.\u001b[39mjson())\n",
      "File \u001b[1;32mc:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexing.py:960\u001b[0m, in \u001b[0;36m_LocationIndexer.__getitem__\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m    958\u001b[0m     key \u001b[39m=\u001b[39m \u001b[39mtuple\u001b[39m(com\u001b[39m.\u001b[39mapply_if_callable(x, \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mobj) \u001b[39mfor\u001b[39;00m x \u001b[39min\u001b[39;00m key)\n\u001b[0;32m    959\u001b[0m     \u001b[39mif\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_is_scalar_access(key):\n\u001b[1;32m--> 960\u001b[0m         \u001b[39mreturn\u001b[39;00m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mobj\u001b[39m.\u001b[39;49m_get_value(\u001b[39m*\u001b[39;49mkey, takeable\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_takeable)\n\u001b[0;32m    961\u001b[0m     \u001b[39mreturn\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_getitem_tuple(key)\n\u001b[0;32m    962\u001b[0m \u001b[39melse\u001b[39;00m:\n\u001b[0;32m    963\u001b[0m     \u001b[39m# we by definition only have the 0th axis\u001b[39;00m\n",
      "File \u001b[1;32mc:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py:3622\u001b[0m, in \u001b[0;36mDataFrame._get_value\u001b[1;34m(self, index, col, takeable)\u001b[0m\n\u001b[0;32m   3616\u001b[0m engine \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mindex\u001b[39m.\u001b[39m_engine\n\u001b[0;32m   3618\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39mnot\u001b[39;00m \u001b[39misinstance\u001b[39m(\u001b[39mself\u001b[39m\u001b[39m.\u001b[39mindex, MultiIndex):\n\u001b[0;32m   3619\u001b[0m     \u001b[39m# CategoricalIndex: Trying to use the engine fastpath may give incorrect\u001b[39;00m\n\u001b[0;32m   3620\u001b[0m     \u001b[39m#  results if our categories are integers that dont match our codes\u001b[39;00m\n\u001b[0;32m   3621\u001b[0m     \u001b[39m# IntervalIndex: IntervalTree has no get_loc\u001b[39;00m\n\u001b[1;32m-> 3622\u001b[0m     row \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mindex\u001b[39m.\u001b[39;49mget_loc(index)\n\u001b[0;32m   3623\u001b[0m     \u001b[39mreturn\u001b[39;00m series\u001b[39m.\u001b[39m_values[row]\n\u001b[0;32m   3625\u001b[0m \u001b[39m# For MultiIndex going through engine effectively restricts us to\u001b[39;00m\n\u001b[0;32m   3626\u001b[0m \u001b[39m#  same-length tuples; see test_get_set_value_no_partial_indexing\u001b[39;00m\n",
      "File \u001b[1;32mc:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexes\\base.py:3623\u001b[0m, in \u001b[0;36mIndex.get_loc\u001b[1;34m(self, key, method, tolerance)\u001b[0m\n\u001b[0;32m   3621\u001b[0m     \u001b[39mreturn\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_engine\u001b[39m.\u001b[39mget_loc(casted_key)\n\u001b[0;32m   3622\u001b[0m \u001b[39mexcept\u001b[39;00m \u001b[39mKeyError\u001b[39;00m \u001b[39mas\u001b[39;00m err:\n\u001b[1;32m-> 3623\u001b[0m     \u001b[39mraise\u001b[39;00m \u001b[39mKeyError\u001b[39;00m(key) \u001b[39mfrom\u001b[39;00m \u001b[39merr\u001b[39;00m\n\u001b[0;32m   3624\u001b[0m \u001b[39mexcept\u001b[39;00m \u001b[39mTypeError\u001b[39;00m:\n\u001b[0;32m   3625\u001b[0m     \u001b[39m# If we have a listlike key, _check_indexing_error will raise\u001b[39;00m\n\u001b[0;32m   3626\u001b[0m     \u001b[39m#  InvalidIndexError. Otherwise we fall through and re-raise\u001b[39;00m\n\u001b[0;32m   3627\u001b[0m     \u001b[39m#  the TypeError.\u001b[39;00m\n\u001b[0;32m   3628\u001b[0m     \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_check_indexing_error(key)\n",
      "\u001b[1;31mKeyError\u001b[0m: 'Ryan Moore'"
     ]
    }
   ],
   "source": [
    "def handle_dates(fromYear= 2022, fromMonth= 1, fromDay= 1, months2Add = 1):\n",
    "    '''\n",
    "    Expanding the date selection capabilities for clockify POST req.\n",
    "    '''\n",
    "    fromdate = datetime(fromYear,fromMonth,fromDay)\n",
    "    rd = relativedelta(months=months2Add)\n",
    "    \n",
    "    return [fromdate.strftime(\"%Y-%m-%dT%H:%M:%S.%f\"), (fromdate+rd).strftime(\"%Y-%m-%dT%H:%M:%S.%f\")]\n",
    "\n",
    "def report_printer(\n",
    "        fYear= 2022,\n",
    "        fMonth= 1,\n",
    "        fDay= 1, \n",
    "        addMonths = 1,\n",
    "        names = [\n",
    "            'Ryan Moore',\n",
    "            ] ):\n",
    "    \n",
    "    fromdate = datetime(fYear,fMonth,fDay)\n",
    "    \n",
    "    rd = relativedelta(months=addMonths)\n",
    "    \n",
    "    \n",
    "    #print('--------------------------------------------------------------------------------')\n",
    "    #print('WFI Consulting Hours Summary' +f\"\\n From: {str(fromdate)}\" + f\"\\n To: {str(fromdate+rd)}\")\n",
    "    #print('--------------------------------------------------------------------------------')\n",
    "    \n",
    "    dfList = []\n",
    "    for i in names:\n",
    "        report_df_temp = print_summary_report(i, handle_dates(fYear,fMonth,fDay, addMonths))\n",
    "        dfList.append(report_df_temp)\n",
    "    \n",
    "    # dfList = [print_summary_report(i, handle_dates(fYear,fMonth,fDay, addMonths)) for i in names]\n",
    "    # return dfList\n",
    "\n",
    "    return dfList\n",
    "    \n",
    "totals_df = pd.concat(report_printer(2022,7,1,1))\n",
    "totals_df.groupby(['Client']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc18da56",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.indexing._LocIndexer at 0x14a325974f0>"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rates_df = pd.read_excel(r'C:\\Users\\mryan\\Desktop\\Rates.xlsx')\n",
    "rates_df.dropna(inplace=True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  },
  "vscode": {
   "interpreter": {
    "hash": "ad2bdc8ecc057115af97d19610ffacc2b4e99fae6737bb82f5d7fb13d2f2c186"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}