6_1_2018_Foong_Upwork tenure and pay formatter.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "'NoneType' object is not iterable",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-5-abe4b4dfa102>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m    211\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    212\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 213\u001b[0;31m \u001b[0mmyObject\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mUpworkDataFormatter\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m<ipython-input-5-abe4b4dfa102>\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m     29\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfp_tenure_file_name\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m\"./csv_files/upwork_fp_tenure_2017_12_12.csv\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     30\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 31\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msave_upwork_tenure_to_csv\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'skilltest_tenure'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mskilltest_tenure_file_name\u001b[0m \u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     32\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msave_upwork_tenure_to_csv\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'hr_tenure'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhr_tenure_file_name\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     33\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msave_upwork_tenure_to_csv\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'fp_tenure'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfp_tenure_file_name\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m<ipython-input-5-abe4b4dfa102>\u001b[0m in \u001b[0;36msave_upwork_tenure_to_csv\u001b[0;34m(self, field, filename)\u001b[0m\n\u001b[1;32m     79\u001b[0m                 \u001b[0mwriter\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwriteheader\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     80\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 81\u001b[0;31m                 \u001b[0;32mfor\u001b[0m \u001b[0muser\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mcur\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     82\u001b[0m                     \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     83\u001b[0m                         \u001b[0mfield_value\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcalculate_skilltest_tenure\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0muser\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mTypeError\u001b[0m: 'NoneType' object is not iterable"
     ]
    }
   ],
   "source": [
    "import httplib2\n",
    "import oauth2\n",
    "import urllib3\n",
    "import types\n",
    "import re\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import psycopg2, psycopg2.extras\n",
    "import httplib\n",
    "import base64\n",
    "import json # For Microsoft Face API\n",
    "import urllib as urllib # For Microsoft Face API\n",
    "import time \n",
    "import csv \n",
    "import datetime \n",
    "\n",
    "class UpworkDataFormatter:\n",
    "    \n",
    "    def __init__(self):\n",
    "        # Set the present date for collecting the data\n",
    "        self.current_date = \"12/31/2017\"\n",
    "        self.current_date = datetime.datetime.strptime(self.current_date, \"%m/%d/%Y\")\n",
    "        \n",
    "        # File name\n",
    "        self.actual_rates_file_name = \"./csv_files/upwork_actual_rates_2017_12_12.csv\"\n",
    "        self.skilltest_tenure_file_name = \"./csv_files/upwork_skilltest_tenure_2017_12_12.csv\"\n",
    "        self.hr_tenure_file_name = \"./csv_files/upwork_hr_tenure_2017_12_12.csv\"\n",
    "        self.fp_tenure_file_name = \"./csv_files/upwork_fp_tenure_2017_12_12.csv\"\n",
    "        \n",
    "        self.save_upwork_tenure_to_csv('skilltest_tenure', self.skilltest_tenure_file_name )\n",
    "        self.save_upwork_tenure_to_csv('hr_tenure', self.hr_tenure_file_name)\n",
    "        self.save_upwork_tenure_to_csv('fp_tenure', self.fp_tenure_file_name)\n",
    "        self.save_upwork_tenure_to_csv('actual_rates', self.actual_rates_file_name)\n",
    "    \n",
    "    def connect_to_database(self):\n",
    "        conn = psycopg2.connect(\"dbname=eureka01\")\n",
    "        cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)\n",
    "        psycopg2.extensions.register_adapter(dict, psycopg2.extras.Json)\n",
    "        return cur.execute(\"SELECT detailed_info FROM upwork_unitedstates_allskills_2017_12_12;\")\n",
    "                \n",
    "    def save_upwork_tenure_to_csv(self, field, filename):\n",
    "        cur = self.connect_to_database()\n",
    "        \n",
    "        if field == 'actual_rates':\n",
    "        \n",
    "            with open(filename, 'w') as csvfile:\n",
    "                user_count = 1\n",
    "                fieldnames = ['user_count','all_rates', 'mean_rate', 'median_rate']\n",
    "                writer = csv.DictWriter(csvfile, fieldnames=fieldnames)\n",
    "                writer.writeheader()\n",
    "\n",
    "                for user in cur:\n",
    "                    try: \n",
    "                        hr_tenure = self.calculate_assignment_tenure(user, \"hr\")\n",
    "                        all_rates = hr_tenure[3]\n",
    "                        mean_rate = hr_tenure[4]\n",
    "                        median_rate = hr_tenure[5]\n",
    "\n",
    "                        writer.writerow({'user_count': user_count,'all_rates': all_rates, \n",
    "                                         'mean_rate': mean_rate, 'median_rate': median_rate})\n",
    "\n",
    "                    except KeyboardInterrupt:\n",
    "                        print \"We stoppped\"\n",
    "                        break\n",
    "\n",
    "                    except Error as error:\n",
    "                        print error\n",
    "                        writer.writerow({'user_count': user_count,'all_rates': \"None\", \n",
    "                                         'mean_rate': \"None\", 'median_rate': \"None\"})\n",
    "                    print \"Finished user {0} for {1}\".format(user_count, field)\n",
    "                    user_count += 1\n",
    "        \n",
    "        elif field == 'skilltest_tenure':\n",
    "            with open(filename, 'w') as csvfile:\n",
    "                user_count = 1\n",
    "                fieldnames = ['user_count', field]\n",
    "                writer = csv.DictWriter(csvfile, fieldnames=fieldnames)\n",
    "                writer.writeheader()\n",
    "\n",
    "                for user in cur:\n",
    "                    try: \n",
    "                        field_value = self.calculate_skilltest_tenure(user)\n",
    "\n",
    "                        writer.writerow({'user_count': user_count, field: field_value})\n",
    "\n",
    "                    except KeyboardInterrupt:\n",
    "                        print \"We stoppped\"\n",
    "                        break\n",
    "\n",
    "                    except Error as error:\n",
    "                        print error\n",
    "                        writer.writerow({'user_count': user_count, field: \"None\"})\n",
    "                        \n",
    "                    print \"Finished user {0} for {1}\".format(user_count, field)\n",
    "                    user_count += 1\n",
    "                    \n",
    "        else: #For calculating hr and fp tenure \n",
    "            with open(filename, 'w') as csvfile:\n",
    "                user_count = 1\n",
    "                fieldnames = ['user_count', field]\n",
    "                writer = csv.DictWriter(csvfile, fieldnames=fieldnames)\n",
    "                writer.writeheader()\n",
    "\n",
    "                for user in cur:\n",
    "                    try: \n",
    "                        value = self.calculate_skilltest_tenure(self, field[:2])\n",
    "                        writer.writerow({'user_count': user_count, field: value})\n",
    "\n",
    "                    except KeyboardInterrupt:\n",
    "                        print \"We stoppped\"\n",
    "                        break\n",
    "\n",
    "                    except Error as error:\n",
    "                        print error\n",
    "                        writer.writerow({'user_count': user_count, field: \"None\"})\n",
    "                        print \"Finished user {0} for {1}\".format(user_count, field)\n",
    "                        user_count += 1\n",
    "                \n",
    "    # Calculate the earliest skill test date on Upwork, returns Upwork tenure in days, months and years as list\n",
    "    def calculate_skilltest_tenure(self, user):\n",
    "        \n",
    "        try:\n",
    "            skilltest_list = user[0][\"tsexams\"][\"tsexam\"]\n",
    "        except:\n",
    "            return \"None\"\n",
    "        \n",
    "        all_dates = []\n",
    "        earliest_date = \"\"\n",
    "        \n",
    "        if (type(skilltest_list) is list): \n",
    "            for test in skilltest_list:\n",
    "                date = test['ts_when']\n",
    "                date_datetime = datetime.datetime.strptime(date, \"%m/%d/%Y\")\n",
    "                all_dates.append(date_datetime)\n",
    "            earliest_date = min(all_dates)\n",
    "        \n",
    "        elif (type(skilltest_list) is dict):\n",
    "            date = skilltest_list['ts_when']\n",
    "            date_datetime = datetime.datetime.strptime(date, \"%m/%d/%Y\")\n",
    "            earliest_date = date_datetime\n",
    "        \n",
    "        # Calculate number of months on Upwork\n",
    "        tenure_days = (self.current_date - earliest_date).days\n",
    "        tenure_months = tenure_days/30\n",
    "        tenure_years = tenure_days/365\n",
    "        return [tenure_days, tenure_months, tenure_years]\n",
    "    \n",
    "    # Returns Upwork tenure based on earliest end date for a fixed price or hourly assignment, and average pay for hourly assignments \n",
    "    def calculate_assignment_tenure(self, user, hr_or_fp): # Set last attribute to hr or fp \n",
    "        try: \n",
    "            assignments_list = user[0][\"assignments\"][hr_or_fp][\"job\"]\n",
    "        except:\n",
    "            return \"None\"\n",
    "        \n",
    "        all_end_dates = []\n",
    "        earliest_date = \"\"\n",
    "        \n",
    "        if (type(assignments_list) is list): # If more than one assignment reported\n",
    "            for assignment in assignments_list:\n",
    "                end_date = assignment[\"as_to_full\"]\n",
    "                if (end_date == \"Present\"):\n",
    "                    end_date = self.current_date\n",
    "                    end_datetime = end_date\n",
    "                else:\n",
    "                    end_datetime = datetime.datetime.strptime(end_date, \"%m/%d/%Y\")\n",
    "                \n",
    "                all_end_dates.append(end_datetime)\n",
    "            earliest_date = min(all_end_dates)\n",
    "\n",
    "        elif (type(assignments_list) is dict): # If only one assignment reported\n",
    "            end_date = assignments_list[\"as_to_full\"]\n",
    "            if (end_date == \"Present\"):\n",
    "                end_date = self.current_date\n",
    "                end_datetime = end_date\n",
    "            else:\n",
    "                end_datetime = datetime.datetime.strptime(end_date, \"%m/%d/%Y\")\n",
    "            earliest_date = end_datetime\n",
    "        \n",
    "        # Calculate tenure from earliest work date\n",
    "        tenure_days = (self.current_date - earliest_date).days\n",
    "        tenure_months = tenure_days/30\n",
    "        tenure_years = tenure_days/365\n",
    "        \n",
    "        # Calculate average pay for hourly assignments\n",
    "        if hr_or_fp == \"hr\":\n",
    "            all_rates = []\n",
    "            mean_rate = 0\n",
    "            if (type(assignments_list) is list): # If more than one assignment reported\n",
    "                for assignment in assignments_list:\n",
    "                    rate = assignment[\"as_rate\"]\n",
    "                    rate = float(rate[1:])\n",
    "                    all_rates.append(rate)\n",
    "                \n",
    "                mean_rate = round(np.array(all_rates).mean(),2)\n",
    "                median_rate = round(np.median(np.array(all_rates)), 2)\n",
    "                \n",
    "            elif (type(assignments_list) is dict):\n",
    "                rate = assignments_list[\"as_rate\"]\n",
    "                rate = float(rate[1:])\n",
    "                rate = round(rate, 2)\n",
    "                mean_rate = rate\n",
    "                median_rate = rate\n",
    "                all_rates.append(mean_rate)\n",
    "        elif hr_or_fp == \"fp\":\n",
    "            mean_rate = \"None\"\n",
    "            all_rates = \"None\"\n",
    "            median_rate = \"None\"\n",
    "        \n",
    "        return [tenure_days, tenure_months, tenure_years, all_rates, mean_rate, median_rate]\n",
    "        \n",
    "\n",
    "myObject = UpworkDataFormatter()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}