Notebook_Exploration.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_excel('FilteredViewTransactionsExpensesAll_CSILakiMIesNet_Eng.xlsx')\n",
    "df.head(2)\n",
    "df['Assigment name '] = df['Assignment / Toimeksianto'].map(lambda x: ' '.join(x.split('-')[1:]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "date_columns = ['Date / Päiväys', 'Entry Date / Kirjauspäivä', 'Invoicing Date / Laskutuspäivä', 'Payment Date / Maksupäivä']\n",
    "\n",
    "df[date_columns] = df[date_columns].astype(str)\n",
    "for date_column in date_columns:\n",
    "    df[date_column] = df[date_column].apply(lambda x: str(x)[:10])\n",
    "\n",
    "new_columns = [column.split('/')[0][:-1] for column in df.columns]\n",
    "\n",
    "df.columns = new_columns\n",
    "\n",
    "bool_columns = ['Internal', 'Non-Billable']\n",
    "\n",
    "for colname in bool_columns:\n",
    "    df[colname] = df[colname].apply(lambda x: 'True' if x else 'False')\n",
    "\n",
    "df['Assignment'] = df['Assignment'].apply(lambda x: x.split()[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "non_use = ['Business Unit', 'Business Area', 'Job Title', 'Manager', 'Account', 'Assignment.agency_gui',\n",
    "           'Assignment.Owner_Gui', 'Assignment.Responsible_Person_Gui','Assignment.Record_Gui', 'User',\n",
    "           'User.Record_Gui', 'Users.Manager_Gui', 'Registered']\n",
    "\n",
    "common_cols =  ['Parent Company', 'Parent Company Type', 'Parent Company Home Country',\n",
    "       'Parent Company Industry', 'Parent Company Responsible', 'Principal',\n",
    "       'Principal Type', 'Principal Home Country', 'Payer',\n",
    "       'Payer Home Country', 'Industry', 'Customer Responsible',\n",
    "       'Seller', 'Cost Center', 'Assignment Type', 'Author', 'Category', \n",
    "        'Employee Role', 'Internal', 'Assigment name', 'Customer Responsible', 'Owner', 'Responsible']\n",
    "\n",
    "df = df[[x for x in df.columns if x not in non_use]]\n",
    "\n",
    "uncommon_cols = [col for col in df.columns if col not in common_cols + ['Assignment', 'Transaction Type']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_common = df[['Assignment'] + common_cols].drop_duplicates(['Assignment']).set_index('Assignment')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "rename_dict = {\n",
    "    'Transaction Type Englis': 'Transaction Type English',\n",
    "    'Transaction status (Ope': 'Transaction status',\n",
    "    'Status (Registere': 'Status',\n",
    "    'Type (Fe': 'Type',\n",
    "}\n",
    "\n",
    "def aggregator(rec):\n",
    "#     print(rec[['Billable Hours', 'Status (Registere', 'Paid', 'Invoiced', 'Margin', 'Invoicing Date', 'Total']])\n",
    "    max_index = np.max(list(rec.index))\n",
    "    result = pd.Series(rec.loc[max_index, :])\n",
    "    result['Billable Hours'] = np.sum(rec['Billable Hours'].dropna())\n",
    "    result['Hours Worked'] = np.sum(rec['Hours Worked'].dropna())\n",
    "    result['Paid'] = np.sum(rec['Paid'].dropna())\n",
    "    result['Invoiced'] = np.max(rec['Invoiced'].dropna())\n",
    "    result['Margin'] = np.max(rec['Margin'].dropna())\n",
    "    result['Total'] = np.max(rec['Total'].dropna())\n",
    "    result_new = result[uncommon_cols]\n",
    "    result_new = result_new.rename(rename_dict)\n",
    "    return result_new.to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "result = df.groupby(['Assignment', 'Transaction'], as_index=True).apply(lambda x: aggregator(x))\n",
    "result = result.reset_index()\n",
    "result = result.rename(columns = {0: 'Transactions'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def transaction_num_adder(rec):\n",
    "#     print(rec)\n",
    "#     new_rec = pd.DataFrame(rec)\n",
    "#     new_rec.loc[:,'transaction_num'] = np.arange(len(new_rec))\n",
    "#     print(new_rec)\n",
    "#     new_rec.T.to_dict()\n",
    "#     print(rec)\n",
    "#     try:\n",
    "        for transaction_num, index in enumerate(rec.index):\n",
    "            rec.loc[index, 'Transactions'].update(\n",
    "                {\n",
    "                    'Transaction Number': transaction_num + 1,\n",
    "                }\n",
    "            )\n",
    "#     except:\n",
    "#         print(rec)\n",
    "#     return new_rec.T.to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Assignment</th>\n",
       "      <th>Transaction</th>\n",
       "      <th>Transactions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>15</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>16</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>17</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>18</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>20080001</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>20080004</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>20080005</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>20080006</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>20080007</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>20080008</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>20080009</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>20080010</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20080011</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>20080012</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>20080014</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>20080015</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>20080016</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>20080017</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>20080018</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>20080019</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>20080020</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>20080021</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>20080022</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>177</th>\n",
       "      <td>20120031</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>20120032</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>20120033</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>20120034</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>20120035</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>20120036</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183</th>\n",
       "      <td>20120037</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>20120038</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>20120039</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>186</th>\n",
       "      <td>20120040</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>20120041</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>20120042</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>20120043</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>20120044</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>20120045</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>20120046</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>20120047</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>20120048</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>20120049</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>20120050</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>20120051</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>20120052</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>20120053</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>7</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>8</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>9</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>207 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Assignment Transaction Transactions\n",
       "0            1        None         None\n",
       "1           10        None         None\n",
       "2           11        None         None\n",
       "3           12        None         None\n",
       "4           13        None         None\n",
       "5           14        None         None\n",
       "6           15        None         None\n",
       "7           16        None         None\n",
       "8           17        None         None\n",
       "9           18        None         None\n",
       "10           2        None         None\n",
       "11    20080001        None         None\n",
       "12    20080004        None         None\n",
       "13    20080005        None         None\n",
       "14    20080006        None         None\n",
       "15    20080007        None         None\n",
       "16    20080008        None         None\n",
       "17    20080009        None         None\n",
       "18    20080010        None         None\n",
       "19    20080011        None         None\n",
       "20    20080012        None         None\n",
       "21    20080014        None         None\n",
       "22    20080015        None         None\n",
       "23    20080016        None         None\n",
       "24    20080017        None         None\n",
       "25    20080018        None         None\n",
       "26    20080019        None         None\n",
       "27    20080020        None         None\n",
       "28    20080021        None         None\n",
       "29    20080022        None         None\n",
       "..         ...         ...          ...\n",
       "177   20120031        None         None\n",
       "178   20120032        None         None\n",
       "179   20120033        None         None\n",
       "180   20120034        None         None\n",
       "181   20120035        None         None\n",
       "182   20120036        None         None\n",
       "183   20120037        None         None\n",
       "184   20120038        None         None\n",
       "185   20120039        None         None\n",
       "186   20120040        None         None\n",
       "187   20120041        None         None\n",
       "188   20120042        None         None\n",
       "189   20120043        None         None\n",
       "190   20120044        None         None\n",
       "191   20120045        None         None\n",
       "192   20120046        None         None\n",
       "193   20120047        None         None\n",
       "194   20120048        None         None\n",
       "195   20120049        None         None\n",
       "196   20120050        None         None\n",
       "197   20120051        None         None\n",
       "198   20120052        None         None\n",
       "199   20120053        None         None\n",
       "200          3        None         None\n",
       "201          4        None         None\n",
       "202          5        None         None\n",
       "203          6        None         None\n",
       "204          7        None         None\n",
       "205          8        None         None\n",
       "206          9        None         None\n",
       "\n",
       "[207 rows x 3 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.groupby(['Assignment'], as_index = False).agg(transaction_num_adder)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# result.loc[result['Assignment'] == '12', 'Transactions'].iloc[2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "result_json = {}\n",
    "def anton_agg(rec):\n",
    "    key = rec['Assignment'].iloc[0]\n",
    "    result = {rec.loc[i,'Transaction']: rec.loc[i,'Transactions'] for i in rec.index}\n",
    "    output = {\n",
    "        key: result,\n",
    "    }\n",
    "    result_json.update(output)\n",
    "\n",
    "after_group_by = result.groupby(['Assignment'], as_index=False).agg(anton_agg)\n",
    "\n",
    "series_uncommon = pd.Series(result_json, name = 'Transactions')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_prefinal = pd.concat([df_common, series_uncommon], axis = 1)\n",
    "df_prefinal = df_prefinal.rename(columns = {0: 'Transactions'})\n",
    "\n",
    "sum_columns = ['Total', 'Margin', 'Paid', 'Invoiced', 'Billable Hours', 'Hours Worked']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_prefinal;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for colname in sum_columns:\n",
    "    df_prefinal[colname + '_sum'] = df_prefinal['Transactions'].map(lambda x: sum([x[j][colname] for j in list(x.keys())]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_prefinal.to_csv('df_prefinal.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "common_dict = df_prefinal.drop(['Transactions'], axis = 1).T.to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "series_common = pd.Series(common_dict, name= 'Info')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_by_min = df.groupby(['Assignment']).agg(\n",
    "    {\n",
    "        'Entry Date': np.min,\n",
    "    }\n",
    ")\n",
    "group_by_min.columns = ['min_date']\n",
    "group_by_min['min_date'] = pd.to_datetime(group_by_min['min_date'])\n",
    "\n",
    "group_by_max = df.groupby(['Assignment']).agg(\n",
    "    {\n",
    "        'Entry Date': np.max,\n",
    "    }\n",
    ")\n",
    "group_by_max.columns = ['max_date']\n",
    "group_by_max['max_date'] = pd.to_datetime(group_by_max['max_date'])\n",
    "\n",
    "\n",
    "df_time = pd.concat([group_by_max, group_by_min], axis = 1)\n",
    "df_time['date_sample'] = df_time.apply(lambda x: str(((x['max_date']- x['min_date']) // 3) * 2 + x['min_date'])[:10], axis=1).astype(str)#[:10]\n",
    "df_time['date_more'] = df_time.apply(lambda x: str(x['max_date'] + pd.to_timedelta(1,unit='D'))[:10], axis=1)\n",
    "df_time['min_date'] = df_time['min_date'].apply(lambda x: str(x)[:10])\n",
    "df_time['max_date'] = df_time['max_date'].apply(lambda x: str(x)[:10])\n",
    "\n",
    "# df_time['Info'] = df.apply(lambda x: x.to_dict(), axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "# series_dates =  df.apply(lambda x: x.to_dict(), axis = 1)\n",
    "# series_dates = series_dates.rename(columns = {0:'Info'})\n",
    "# series_dates.name = 'Info'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "json_4 = pd.concat([series_common, series_uncommon, df_time], axis=1).T.to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [],
   "source": [
    "# json_4['1']['date_sample']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "json.dump(json_4, open('json_4', 'w'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def aggregator_transactions(rec):\n",
    "#     print(rec[['Billable Hours', 'Status (Registere', 'Paid', 'Invoiced', 'Margin', 'Invoicing Date', 'Total']])\n",
    "    max_index = np.max(list(rec.index))\n",
    "    result = pd.Series(rec.loc[max_index, :])\n",
    "    result['Billable Hours'] = np.sum(rec['Billable Hours'].dropna())\n",
    "    result['Hours Worked'] = np.sum(rec['Hours Worked'].dropna())\n",
    "    result['Paid'] = np.sum(rec['Paid'].dropna())\n",
    "    result['Invoiced'] = np.max(rec['Invoiced'].dropna())\n",
    "    result['Margin'] = np.max(rec['Margin'].dropna())\n",
    "    result['Total'] = np.max(rec['Total'].dropna())\n",
    "    return result.to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_transactions = df.groupby(['Assignment', 'Transaction'], as_index=True).apply(lambda x: aggregator_transactions(x))\n",
    "df_transactions = df_transactions.reset_index()\n",
    "df_transactions = df_transactions.rename(columns = {0: 'Transactions'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "content = df_transactions['Transactions'].apply(lambda x: pd.Series(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       170.0\n",
       "1       170.0\n",
       "2         NaN\n",
       "3       170.0\n",
       "4       170.0\n",
       "5       170.0\n",
       "6       170.0\n",
       "7         NaN\n",
       "8       170.0\n",
       "9         NaN\n",
       "10        0.0\n",
       "11      170.0\n",
       "12      170.0\n",
       "13      170.0\n",
       "14        NaN\n",
       "15        NaN\n",
       "16      100.0\n",
       "17      170.0\n",
       "18      200.0\n",
       "19      200.0\n",
       "20      170.0\n",
       "21        NaN\n",
       "22      170.0\n",
       "23        NaN\n",
       "24        NaN\n",
       "25      170.0\n",
       "26      170.0\n",
       "27        NaN\n",
       "28        NaN\n",
       "29      170.0\n",
       "        ...  \n",
       "1130    170.0\n",
       "1131    170.0\n",
       "1132      NaN\n",
       "1133      NaN\n",
       "1134      NaN\n",
       "1135      NaN\n",
       "1136    170.0\n",
       "1137    170.0\n",
       "1138      0.0\n",
       "1139    170.0\n",
       "1140    170.0\n",
       "1141    170.0\n",
       "1142    170.0\n",
       "1143    170.0\n",
       "1144    170.0\n",
       "1145    170.0\n",
       "1146    170.0\n",
       "1147    170.0\n",
       "1148      NaN\n",
       "1149    170.0\n",
       "1150    170.0\n",
       "1151    170.0\n",
       "1152    250.0\n",
       "1153    170.0\n",
       "1154    150.0\n",
       "1155      NaN\n",
       "1156    170.0\n",
       "1157    170.0\n",
       "1158    500.0\n",
       "1159    170.0\n",
       "Name: Hour Price, Length: 1160, dtype: float64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "content['Hour Price']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def checker(rec):\n",
    "    return np.mean(pd.Series(rec).dropna())\n",
    "#     return list(np.unique(rec))\n",
    "#     return len(np.unique(rec))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_lawer_assignment_price = content[['Responsible', 'Hour Price', 'Assignment Type']].groupby(['Responsible', 'Assignment Type']).agg(checker)\n",
    "df_lawer_price = content[['Responsible', 'Hour Price']].groupby(['Responsible']).agg(checker)\n",
    "df_assignment_price = content[['Assignment Type', 'Hour Price']].groupby(['Assignment Type']).agg(checker)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Hour Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Responsible</th>\n",
       "      <th>Assignment Type</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"12\" valign=\"top\">Boris Keilaniemi</th>\n",
       "      <th>Arviointipalvelu - Asuinhuoneistot</th>\n",
       "      <td>170.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arviointipalvelu - Tontit</th>\n",
       "      <td>100.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Edunvalvontapalvelu - Korvausneuvottelut</th>\n",
       "      <td>170.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Edunvalvontapalvelu - Maantiet</th>\n",
       "      <td>170.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hallintoasia</th>\n",
       "      <td>153.478261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kilpailuoikeus</th>\n",
       "      <td>170.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Konsultointia</th>\n",
       "      <td>180.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Perintöoikeus</th>\n",
       "      <td>170.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rahoitusoikeus</th>\n",
       "      <td>137.142857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rikosoikeus</th>\n",
       "      <td>380.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sopimusoikeus</th>\n",
       "      <td>196.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Velkomusasia</th>\n",
       "      <td>210.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Elias Muonio</th>\n",
       "      <th>Hallintoasia</th>\n",
       "      <td>131.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"12\" valign=\"top\">Helen Hakaniemi</th>\n",
       "      <th>Arviointipalvelu - Tontit</th>\n",
       "      <td>250.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hallintoasia</th>\n",
       "      <td>132.758621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kilpailuoikeus</th>\n",
       "      <td>168.175647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Konsultointia</th>\n",
       "      <td>177.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Perheoikeus</th>\n",
       "      <td>231.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Perintöoikeus</th>\n",
       "      <td>147.096774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rahoitusoikeus</th>\n",
       "      <td>151.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rikosoikeus</th>\n",
       "      <td>255.220126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sopimusoikeus</th>\n",
       "      <td>155.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Työoikeus</th>\n",
       "      <td>160.625000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vero-oikeus</th>\n",
       "      <td>170.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yritysoikeus</th>\n",
       "      <td>157.857143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Jari London</th>\n",
       "      <th>Hallintoasia</th>\n",
       "      <td>150.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Konsultointia</th>\n",
       "      <td>172.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Perintöoikeus</th>\n",
       "      <td>185.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Jopi Jalkapuoli</th>\n",
       "      <th>Arviointipalvelu - Tontit</th>\n",
       "      <td>170.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hallintoasia</th>\n",
       "      <td>167.142857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"12\" valign=\"top\">Nelli Niittykumpu</th>\n",
       "      <th>Edunvalvontapalvelu - Maantiet</th>\n",
       "      <td>300.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hallintoasia</th>\n",
       "      <td>185.375000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jatkuva luottopäätös</th>\n",
       "      <td>170.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kilpailuoikeus</th>\n",
       "      <td>168.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Konsultointia</th>\n",
       "      <td>136.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Perheoikeus</th>\n",
       "      <td>166.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rahoitusoikeus</th>\n",
       "      <td>170.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rikosoikeus</th>\n",
       "      <td>176.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sopimusoikeus</th>\n",
       "      <td>190.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Työoikeus</th>\n",
       "      <td>120.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Velkomusasia</th>\n",
       "      <td>132.857143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yritysoikeus</th>\n",
       "      <td>170.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Seppo Sörnäinen</th>\n",
       "      <th>Hallintoasia</th>\n",
       "      <td>182.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Perheoikeus</th>\n",
       "      <td>170.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">Viivi Vuosaari</th>\n",
       "      <th>Arviointipalvelu - Asuinhuoneistot</th>\n",
       "      <td>670.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arviointipalvelu - Tontit</th>\n",
       "      <td>168.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hallintoasia</th>\n",
       "      <td>163.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kilpailuoikeus</th>\n",
       "      <td>159.448182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Konsultointia</th>\n",
       "      <td>176.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Perheoikeus</th>\n",
       "      <td>162.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Perintöoikeus</th>\n",
       "      <td>170.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rahoitusoikeus</th>\n",
       "      <td>172.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rikosoikeus</th>\n",
       "      <td>147.857143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sopimusoikeus</th>\n",
       "      <td>185.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">Yki Kontula</th>\n",
       "      <th>Arviointipalvelu - Asuinhuoneistot</th>\n",
       "      <td>200.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hallintoasia</th>\n",
       "      <td>141.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kilpailuoikeus</th>\n",
       "      <td>168.545455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Perheoikeus</th>\n",
       "      <td>136.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Perintöoikeus</th>\n",
       "      <td>170.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rikosoikeus</th>\n",
       "      <td>182.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>91 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                            Hour Price\n",
       "Responsible       Assignment Type                                     \n",
       "Boris Keilaniemi  Arviointipalvelu - Asuinhuoneistot        170.000000\n",
       "                  Arviointipalvelu - Tontit                 100.000000\n",
       "                  Edunvalvontapalvelu - Korvausneuvottelut  170.000000\n",
       "                  Edunvalvontapalvelu - Maantiet            170.000000\n",
       "                  Hallintoasia                              153.478261\n",
       "                  Kilpailuoikeus                            170.000000\n",
       "                  Konsultointia                             180.000000\n",
       "                  Perintöoikeus                             170.000000\n",
       "                  Rahoitusoikeus                            137.142857\n",
       "                  Rikosoikeus                               380.000000\n",
       "                  Sopimusoikeus                             196.666667\n",
       "                  Velkomusasia                              210.000000\n",
       "Elias Muonio      Hallintoasia                              131.750000\n",
       "Helen Hakaniemi   Arviointipalvelu - Tontit                 250.000000\n",
       "                  Hallintoasia                              132.758621\n",
       "                  Kilpailuoikeus                            168.175647\n",
       "                  Konsultointia                             177.500000\n",
       "                  Perheoikeus                               231.250000\n",
       "                  Perintöoikeus                             147.096774\n",
       "                  Rahoitusoikeus                            151.250000\n",
       "                  Rikosoikeus                               255.220126\n",
       "                  Sopimusoikeus                             155.000000\n",
       "                  Työoikeus                                 160.625000\n",
       "                  Vero-oikeus                               170.000000\n",
       "                  Yritysoikeus                              157.857143\n",
       "Jari London       Hallintoasia                              150.000000\n",
       "                  Konsultointia                             172.000000\n",
       "                  Perintöoikeus                             185.000000\n",
       "Jopi Jalkapuoli   Arviointipalvelu - Tontit                 170.000000\n",
       "                  Hallintoasia                              167.142857\n",
       "...                                                                ...\n",
       "Nelli Niittykumpu Edunvalvontapalvelu - Maantiet            300.000000\n",
       "                  Hallintoasia                              185.375000\n",
       "                  Jatkuva luottopäätös                      170.000000\n",
       "                  Kilpailuoikeus                            168.000000\n",
       "                  Konsultointia                             136.000000\n",
       "                  Perheoikeus                               166.666667\n",
       "                  Rahoitusoikeus                            170.000000\n",
       "                  Rikosoikeus                               176.000000\n",
       "                  Sopimusoikeus                             190.000000\n",
       "                  Työoikeus                                 120.000000\n",
       "                  Velkomusasia                              132.857143\n",
       "                  Yritysoikeus                              170.000000\n",
       "Seppo Sörnäinen   Hallintoasia                              182.500000\n",
       "                  Perheoikeus                               170.000000\n",
       "Viivi Vuosaari    Arviointipalvelu - Asuinhuoneistot        670.000000\n",
       "                  Arviointipalvelu - Tontit                 168.000000\n",
       "                  Hallintoasia                              163.750000\n",
       "                  Kilpailuoikeus                            159.448182\n",
       "                  Konsultointia                             176.000000\n",
       "                  Perheoikeus                               162.000000\n",
       "                  Perintöoikeus                             170.000000\n",
       "                  Rahoitusoikeus                            172.500000\n",
       "                  Rikosoikeus                               147.857143\n",
       "                  Sopimusoikeus                             185.000000\n",
       "Yki Kontula       Arviointipalvelu - Asuinhuoneistot        200.000000\n",
       "                  Hallintoasia                              141.666667\n",
       "                  Kilpailuoikeus                            168.545455\n",
       "                  Perheoikeus                               136.000000\n",
       "                  Perintöoikeus                             170.000000\n",
       "                  Rikosoikeus                               182.000000\n",
       "\n",
       "[91 rows x 1 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_lawer_assignment_price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_lawer_assignment_price.to_csv('df_lawer_assignment_price.csv', index = True)\n",
    "df_lawer_price.to_csv('df_lawer_price.csv', index = True)\n",
    "df_assignment_price.to_csv('df_assignment_price.csv', index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "lawer_counts = content[['Responsible', 'Hour Price', 'Assignment Type']].groupby(['Responsible', 'Assignment Type'], as_index=False).agg(np.count_nonzero)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from plotly.offline import download_plotlyjs, init_notebook_mode, plot, iplot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<script>requirejs.config({paths: { 'plotly': ['https://cdn.plot.ly/plotly-latest.min']},});if(!window.Plotly) {{require(['plotly'],function(plotly) {window.Plotly=plotly;});}}</script>"
      ],
      "text/vnd.plotly.v1+html": [
       "<script>requirejs.config({paths: { 'plotly': ['https://cdn.plot.ly/plotly-latest.min']},});if(!window.Plotly) {{require(['plotly'],function(plotly) {window.Plotly=plotly;});}}</script>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "init_notebook_mode(connected=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import plotly\n",
    "import plotly.graph_objs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "responsible_name = 'Boris Keilaniemi'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "assignment_name = 'Rahoitusoikeus'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Responsible</th>\n",
       "      <th>Assignment Type</th>\n",
       "      <th>Hour Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Boris Keilaniemi</td>\n",
       "      <td>Arviointipalvelu - Asuinhuoneistot</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Boris Keilaniemi</td>\n",
       "      <td>Arviointipalvelu - Tontit</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Boris Keilaniemi</td>\n",
       "      <td>Edunvalvontapalvelu - Korvausneuvottelut</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Boris Keilaniemi</td>\n",
       "      <td>Edunvalvontapalvelu - Maantiet</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Boris Keilaniemi</td>\n",
       "      <td>Hallintoasia</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Boris Keilaniemi</td>\n",
       "      <td>Kilpailuoikeus</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Boris Keilaniemi</td>\n",
       "      <td>Konsultointia</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Boris Keilaniemi</td>\n",
       "      <td>Perintöoikeus</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Boris Keilaniemi</td>\n",
       "      <td>Rahoitusoikeus</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Boris Keilaniemi</td>\n",
       "      <td>Rikosoikeus</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Boris Keilaniemi</td>\n",
       "      <td>Sopimusoikeus</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Boris Keilaniemi</td>\n",
       "      <td>Velkomusasia</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Responsible                           Assignment Type  Hour Price\n",
       "0   Boris Keilaniemi        Arviointipalvelu - Asuinhuoneistot         5.0\n",
       "1   Boris Keilaniemi                 Arviointipalvelu - Tontit         7.0\n",
       "2   Boris Keilaniemi  Edunvalvontapalvelu - Korvausneuvottelut         1.0\n",
       "3   Boris Keilaniemi            Edunvalvontapalvelu - Maantiet         7.0\n",
       "4   Boris Keilaniemi                              Hallintoasia        30.0\n",
       "5   Boris Keilaniemi                            Kilpailuoikeus         7.0\n",
       "6   Boris Keilaniemi                             Konsultointia         3.0\n",
       "7   Boris Keilaniemi                             Perintöoikeus         3.0\n",
       "8   Boris Keilaniemi                            Rahoitusoikeus         6.0\n",
       "9   Boris Keilaniemi                               Rikosoikeus        10.0\n",
       "10  Boris Keilaniemi                             Sopimusoikeus        16.0\n",
       "11  Boris Keilaniemi                              Velkomusasia        14.0"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lawer_counts.loc[lawer_counts['Responsible'] == responsible_name, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "mean_assignment_price = int(df_assignment_price.loc[assignment_name, 'Hour Price'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "lawer_counts.to_csv('lawer_counts.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import plotly.plotly as py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "lawer_counts\n",
    "\n",
    "def get_lawer_assignment_info(responsible_name, assignment_name):\n",
    "    mean_lawer_price = int(df_lawer_price.loc[responsible_name, 'Hour Price'])\n",
    "    mean_assignment_price = int(df_assignment_price.loc[assignment_name, 'Hour Price'])\n",
    "    mean_lawer_assignment_price = int(df_lawer_assignment_price.loc[(responsible_name, assignment_name), 'Hour Price'])\n",
    "    \n",
    "    result = {\n",
    "        'mean_lawer_price': mean_lawer_price,\n",
    "        'mean_assignment_price': mean_assignment_price,\n",
    "        'mean_lawer_assignment_price': mean_lawer_assignment_price,\n",
    "    }\n",
    "    \n",
    "    mask_lawer = lawer_counts['Responsible'] == responsible_name\n",
    "    assignment_types = lawer_counts.loc[mask_lawer, 'Assignment Type'].values\n",
    "    hour_prices = lawer_counts.loc[mask_lawer, 'Hour Price'].values\n",
    "\n",
    "#     filename = f'/Users/smerdov/Junction2017/{responsible_name}.html'\n",
    "#     filename = '/Users/smerdov/Junction2017/Piechart.html'\n",
    "    fig = plotly.offline.plot(\n",
    "        {\n",
    "            \"data\": [\n",
    "                plotly.graph_objs.Pie(labels=assignment_types,values=hour_prices)\n",
    "            ]\n",
    "        },\n",
    "#         filename = filename,\n",
    "        image = 'jpeg',\n",
    "    )\n",
    "    \n",
    "#     py.image.save_as(fig, filename='a-simple-plot.png')\n",
    "    \n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "a = get_lawer_assignment_info(responsible_name, assignment_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 244,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'mean_assignment_price': 172,\n",
       " 'mean_lawer_assignment_price': 137,\n",
       " 'mean_lawer_price': 188}"
      ]
     },
     "execution_count": 244,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 288,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "content.to_csv('content.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Assigment name': ' Osakekauppariita',\n",
       " 'Assignment Type': 'Kilpailuoikeus',\n",
       " 'Author': 'Helen Hakaniemi',\n",
       " 'Billable Hours_sum': 24.0,\n",
       " 'Category': nan,\n",
       " 'Cost Center': nan,\n",
       " 'Customer Responsible': 'Helen Hakaniemi',\n",
       " 'Employee Role': nan,\n",
       " 'Hours Worked_sum': 24.0,\n",
       " 'Industry': 'Muu',\n",
       " 'Internal': 'False',\n",
       " 'Invoiced_sum': 1360.0,\n",
       " 'Margin_sum': nan,\n",
       " 'Owner': 'Helen Hakaniemi',\n",
       " 'Paid_sum': 1360.01,\n",
       " 'Parent Company': nan,\n",
       " 'Parent Company Home Country': nan,\n",
       " 'Parent Company Industry': nan,\n",
       " 'Parent Company Responsible': nan,\n",
       " 'Parent Company Type': nan,\n",
       " 'Payer': nan,\n",
       " 'Payer Home Country': nan,\n",
       " 'Principal': 'AsuntoOy Ankka Linna',\n",
       " 'Principal Home Country': 'Suomi',\n",
       " 'Principal Type': 'Prospect',\n",
       " 'Responsible': 'Boris Keilaniemi',\n",
       " 'Seller': nan,\n",
       " 'Total_sum': 1360.0}"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json_1['1']['Info']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# def check_count(rec):\n",
    "#     return len(np.unique(rec))\n",
    "\n",
    "# agg_names_list = [\n",
    "#     'Principal',\n",
    "#     'Customer Responsible',\n",
    "#     'Owner',\n",
    "#     'Responsible',\n",
    "#     'Seller',\n",
    "#     'Author',\n",
    "#     'Payer',\n",
    "# ]\n",
    "\n",
    "# agg_dict = {name: check_count for name in agg_names_list}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "json.dump(json_1, open('json_1', 'w'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Parent Company \n",
      " n different values is 6 \n",
      "most popular is\n",
      " Kolmio Oy                494\n",
      "Ankallispankki Oy         58\n",
      "Tiinan Muonitus Oy        53\n",
      "Leivolan Leivokset Oy      9\n",
      "Hannu Hanhi Oy             4\n",
      "A yhtiö Oy                 2\n",
      "Name: Parent Company, dtype: int64 \n",
      " number of nans is 4617\n",
      "\n",
      "Parent Company Type \n",
      " n different values is 2 \n",
      "most popular is\n",
      " Asiakas       547\n",
      "Osakeyhtiö     62\n",
      "Name: Parent Company Type, dtype: int64 \n",
      " number of nans is 4628\n",
      "\n",
      "Parent Company Home Country \n",
      " n different values is 1 \n",
      "most popular is\n",
      " Suomi    620\n",
      "Name: Parent Company Home Country, dtype: int64 \n",
      " number of nans is 4617\n",
      "\n",
      "Parent Company Industry \n",
      " n different values is 1 \n",
      "most popular is\n",
      " Pankki    60\n",
      "Name: Parent Company Industry, dtype: int64 \n",
      " number of nans is 5177\n",
      "\n",
      "Parent Company Responsible \n",
      " n different values is 5 \n",
      "most popular is\n",
      " Nelli Niittykumpu     494\n",
      "Helen Hakaniemi        58\n",
      "Viivi Vuosaari         53\n",
      "Lenita Lauttasaari      9\n",
      "Kustaa Kulosaari        6\n",
      "Name: Parent Company Responsible, dtype: int64 \n",
      " number of nans is 4617\n",
      "\n",
      "Principal \n",
      " n different values is 140 \n",
      "most popular is\n",
      " Ahdinko Oy               494\n",
      "Ankallispankki Oy        213\n",
      "Sepon Kauppa Oy          182\n",
      "Suutari Kenkä Oy         166\n",
      "AsuntoOy Ankka Linna     165\n",
      "Onni Oy                  152\n",
      "Riston Rengas Oy         106\n",
      "Sallan Salonki Ky         95\n",
      "Roimaan Kalastusliike     91\n",
      "Ankka Ukko                90\n",
      "Name: Principal, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Principal Type \n",
      " n different values is 5 \n",
      "most popular is\n",
      " Customer        859\n",
      "Prospect        691\n",
      "Other           198\n",
      "Key customer     79\n",
      "Partner           3\n",
      "Name: Principal Type, dtype: int64 \n",
      " number of nans is 3407\n",
      "\n",
      "Principal Home Country \n",
      " n different values is 5 \n",
      "most popular is\n",
      " Suomi                       4634\n",
      "Norja                        582\n",
      "USA                           12\n",
      "Yhdistynyt kuningaskunta       6\n",
      "Ruotsi                         3\n",
      "Name: Principal Home Country, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Payer \n",
      " n different values is 129 \n",
      "most popular is\n",
      " AsuntoOy Ankka Linna    162\n",
      "Ankallispankki Oy       159\n",
      "Suutari Kenkä Oy        131\n",
      "Onni Oy                 121\n",
      "Sepon Kauppa Oy         109\n",
      "Sallan Salonki Ky       104\n",
      "Riston Rengas Oy         93\n",
      "Pohjanmaan Paja Oy       81\n",
      "Miinan Muona Oy          76\n",
      "IT Ihme Oy               72\n",
      "Name: Payer, dtype: int64 \n",
      " number of nans is 1612\n",
      "\n",
      "Payer Home Country \n",
      " n different values is 4 \n",
      "most popular is\n",
      " Suomi                       3487\n",
      "Norja                        119\n",
      "Yhdistynyt kuningaskunta      10\n",
      "u.s.a.                         9\n",
      "Name: Payer Home Country, dtype: int64 \n",
      " number of nans is 1612\n",
      "\n",
      "Industry \n",
      " n different values is 7 \n",
      "most popular is\n",
      " Muu                564\n",
      "Pankki             225\n",
      "Kauppa             173\n",
      "Ohjelmisto          88\n",
      "Asianajo            71\n",
      "Kiinteistötoimi     46\n",
      "Energia              4\n",
      "Name: Industry, dtype: int64 \n",
      " number of nans is 4066\n",
      "\n",
      "Customer Responsible \n",
      " n different values is 13 \n",
      "most popular is\n",
      " Helen Hakaniemi       2003\n",
      "Kustaa Kulosaari       852\n",
      "Jopi Jalkapuoli        677\n",
      "Nelli Niittykumpu      487\n",
      "Viivi Vuosaari         398\n",
      "Boris Keilaniemi       322\n",
      "Yki Kontula            245\n",
      "Lenita Lauttasaari     176\n",
      "Jari London             23\n",
      "Kati Alley              17\n",
      "Name: Customer Responsible, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Business Unit \n",
      " n different values is 1 \n",
      "most popular is\n",
      " Asianajotoimisto Koulutus Oy    5237\n",
      "Name: Business Unit, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Assignment Type \n",
      " n different values is 19 \n",
      "most popular is\n",
      " Kilpailuoikeus    972\n",
      "Hallintoasia      708\n",
      "Rikosoikeus       629\n",
      "Konsultointia     579\n",
      "Perheoikeus       518\n",
      "Perintöoikeus     512\n",
      "Työoikeus         263\n",
      "Sopimusoikeus     254\n",
      "Rahoitusoikeus    211\n",
      "Velkomusasia      191\n",
      "Name: Assignment Type, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Business Area \n",
      " n different values is 0 \n",
      "most popular is\n",
      " Series([], Name: Business Area, dtype: int64) \n",
      " number of nans is 5237\n",
      "\n",
      "Owner \n",
      " n different values is 13 \n",
      "most popular is\n",
      " Helen Hakaniemi       2003\n",
      "Kati Alley             644\n",
      "Boris Keilaniemi       451\n",
      "Nelli Niittykumpu      444\n",
      "Jopi Jalkapuoli        435\n",
      "Viivi Vuosaari         385\n",
      "Kustaa Kulosaari       351\n",
      "Lenita Lauttasaari     213\n",
      "Yki Kontula            207\n",
      "Marika Monroe           36\n",
      "Name: Owner, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Responsible \n",
      " n different values is 15 \n",
      "most popular is\n",
      " Helen Hakaniemi       2013\n",
      "Kati Alley             600\n",
      "Boris Keilaniemi       507\n",
      "Jopi Jalkapuoli        444\n",
      "Viivi Vuosaari         394\n",
      "Nelli Niittykumpu      394\n",
      "Kustaa Kulosaari       286\n",
      "Yki Kontula            256\n",
      "Lenita Lauttasaari     192\n",
      "Seppo Sörnäinen         49\n",
      "Name: Responsible, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Seller \n",
      " n different values is 2 \n",
      "most popular is\n",
      " Boris Keilaniemi    39\n",
      "Kati Alley          21\n",
      "Name: Seller, dtype: int64 \n",
      " number of nans is 5177\n",
      "\n",
      "Cost Center \n",
      " n different values is 8 \n",
      "most popular is\n",
      " Lakipalveluja          1353\n",
      "Palvelu                 468\n",
      "Hallinto                228\n",
      "Arviointipalvelu        123\n",
      "Kehitys                  96\n",
      "Edunvalvontapalvelu      56\n",
      "valkeakoski              52\n",
      "Tre                      43\n",
      "Name: Cost Center, dtype: int64 \n",
      " number of nans is 2818\n",
      "\n",
      "Assignment \n",
      " n different values is 207 \n",
      "most popular is\n",
      " 20120043 - Konsultointia                                               433\n",
      "20080005 - Murto pankkiin                                              110\n",
      "20090025 - Kartelli                                                    106\n",
      "20090052 - Murto Sepon kauppaan                                        103\n",
      "20090022 - Kartelli                                                     92\n",
      "20090004 - Perheriita                                                   91\n",
      "20080011 - Työsopimuksen irtisanominen                                  90\n",
      "20080028 - Hallinnollinen asia                                          86\n",
      "20080007 - Urakkariita Onni Oy / Tilaaja Oy, helsingin käräjäoikeus     86\n",
      "20080064 - Rikosoikeus; riitaisa asia                                   83\n",
      "Name: Assignment, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Internal \n",
      " n different values is 2 \n",
      "most popular is\n",
      " False    5230\n",
      "True        7\n",
      "Name: Internal, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Category \n",
      " n different values is 1 \n",
      "most popular is\n",
      " Laskutettava    120\n",
      "Name: Category, dtype: int64 \n",
      " number of nans is 5117\n",
      "\n",
      "Transaction Type Englis \n",
      " n different values is 38 \n",
      "most popular is\n",
      " Phone meeting                                      805\n",
      "Meeting at the office                              430\n",
      "Mileage allowance                                  400\n",
      "Court appearance                                   398\n",
      "Writing a complaint                                354\n",
      "Negotiation                                        343\n",
      "Visiting the customer                              305\n",
      "Writing a letter                                   227\n",
      "Investigation                                      218\n",
      "Writing a debt collection letter to counterpart    210\n",
      "Name: Transaction Type Englis, dtype: int64 \n",
      " number of nans is 239\n",
      "\n",
      "Transaction Type \n",
      " n different values is 40 \n",
      "most popular is\n",
      " Puhelinneuvottelu                          805\n",
      "Neuvottelu tstossa                         430\n",
      "Kilometrikorvaus                           400\n",
      "Esiintyminen oikeudessa                    398\n",
      "Neuvottelu                                 343\n",
      "Käynti asiakkaan luona                     305\n",
      "Laadittu kirje                             227\n",
      "Tutkittu asiaa                             218\n",
      "Perintäkirjeen laatiminen vastapuolelle    210\n",
      "Haastehakemuksen työstäminen               200\n",
      "Name: Transaction Type, dtype: int64 \n",
      " number of nans is 239\n",
      "\n",
      "Skill \n",
      " n different values is 3 \n",
      "most popular is\n",
      " juridiikka               66\n",
      "kiinteistön arviointi     9\n",
      "maan arviointi            6\n",
      "Name: Skill, dtype: int64 \n",
      " number of nans is 5156\n",
      "\n",
      "Employee Role \n",
      " n different values is 2 \n",
      "most popular is\n",
      " Osakas          387\n",
      "Asiantuntija    232\n",
      "Name: Employee Role, dtype: int64 \n",
      " number of nans is 4618\n",
      "\n",
      "Job Title \n",
      " n different values is 0 \n",
      "most popular is\n",
      " Series([], Name: Job Title, dtype: int64) \n",
      " number of nans is 5237\n",
      "\n",
      "Manager \n",
      " n different values is 0 \n",
      "most popular is\n",
      " Series([], Name: Manager, dtype: int64) \n",
      " number of nans is 5237\n",
      "\n",
      "Author \n",
      " n different values is 16 \n",
      "most popular is\n",
      " Helen Hakaniemi       1963\n",
      "Nelli Niittykumpu      589\n",
      "Jopi Jalkapuoli        535\n",
      "Viivi Vuosaari         463\n",
      "Kustaa Kulosaari       389\n",
      "Boris Keilaniemi       387\n",
      "Yki Kontula            253\n",
      "Kati Alley             232\n",
      "Lenita Lauttasaari     197\n",
      "Elias Muonio            83\n",
      "Name: Author, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Date \n",
      " n different values is 277 \n",
      "most popular is\n",
      " 2010-01-21    435\n",
      "2009-03-12    236\n",
      "2009-12-21    194\n",
      "2010-01-28    192\n",
      "2009-01-28    166\n",
      "2010-09-01    160\n",
      "2010-02-19    132\n",
      "2010-02-10    126\n",
      "2009-01-07    118\n",
      "2010-06-02    115\n",
      "Name: Date, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Total \n",
      " n different values is 1419 \n",
      "most popular is\n",
      " 0.0      430\n",
      "170.0    272\n",
      "340.0    240\n",
      "850.0    133\n",
      "255.0    127\n",
      "510.0    120\n",
      "85.0     109\n",
      "42.5      71\n",
      "400.0     58\n",
      "50.0      56\n",
      "Name: Total, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Billable Hours \n",
      " n different values is 343 \n",
      "most popular is\n",
      "  0.00    647\n",
      " 1.00    425\n",
      " 2.00    418\n",
      " 3.00    264\n",
      " 5.00    228\n",
      " 0.50    165\n",
      " 0.25    165\n",
      " 1.50    145\n",
      "-2.00     93\n",
      " 2.50     89\n",
      "Name: Billable Hours, dtype: int64 \n",
      " number of nans is 1032\n",
      "\n",
      "Hours Worked \n",
      " n different values is 336 \n",
      "most popular is\n",
      "  0.00    1105\n",
      " 2.00     347\n",
      " 1.00     295\n",
      " 3.00     281\n",
      " 5.00     171\n",
      " 1.50     141\n",
      " 0.50     129\n",
      " 0.25     123\n",
      " 4.00      89\n",
      "-2.00      79\n",
      "Name: Hours Worked, dtype: int64 \n",
      " number of nans is 1032\n",
      "\n",
      "Hour Price \n",
      " n different values is 40 \n",
      "most popular is\n",
      " 170.0    2547\n",
      "200.0     447\n",
      "0.0       148\n",
      "300.0     144\n",
      "150.0     115\n",
      "120.0     111\n",
      "250.0      98\n",
      "100.0      84\n",
      "130.0      67\n",
      "180.0      65\n",
      "Name: Hour Price, dtype: int64 \n",
      " number of nans is 1032\n",
      "\n",
      "Margin \n",
      " n different values is 1323 \n",
      "most popular is\n",
      "  0.0      236\n",
      " 140.0    197\n",
      " 310.0    123\n",
      " 170.0     97\n",
      "-30.0      86\n",
      " 280.0     82\n",
      " 820.0     78\n",
      " 55.0      59\n",
      " 420.0     59\n",
      " 480.0     56\n",
      "Name: Margin, dtype: int64 \n",
      " number of nans is 1032\n",
      "\n",
      "Account \n",
      " n different values is 0 \n",
      "most popular is\n",
      " Series([], Name: Account, dtype: int64) \n",
      " number of nans is 5237\n",
      "\n",
      "Tax Rate \n",
      " n different values is 7 \n",
      "most popular is\n",
      " Alv 22 %    2651\n",
      "Veroton      590\n",
      "Alv 23 %     352\n",
      "Alv 24 %     129\n",
      "Alv 0 %       94\n",
      "Alv 9 %       12\n",
      "Alv 8 %       11\n",
      "Name: Tax Rate, dtype: int64 \n",
      " number of nans is 1398\n",
      "\n",
      "Cost Center (Business Unit) \n",
      " n different values is 8 \n",
      "most popular is\n",
      " Lakipalveluja          1255\n",
      "Palvelu                 344\n",
      "Hallinto                178\n",
      "Arviointipalvelu         97\n",
      "Kehitys                  75\n",
      "Edunvalvontapalvelu      59\n",
      "valkeakoski              50\n",
      "Tre                      43\n",
      "Name: Cost Center (Business Unit), dtype: int64 \n",
      " number of nans is 3136\n",
      "\n",
      "Transaction \n",
      " n different values is 374 \n",
      "most popular is\n",
      " Puhelinneuvottelu               457\n",
      "Neuvottelu                      274\n",
      "Käynti asiakkaan luona          255\n",
      "Esiintyminen oikeudessa         244\n",
      "Neuvottelu tstossa              233\n",
      "Kilometrikorvaus                230\n",
      "Laadittu kirje                  181\n",
      "Haastehakemuksen työstäminen    168\n",
      "Kaupparekisteriote              150\n",
      "Toimeksiantoon perehtyminen     130\n",
      "Name: Transaction, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Transaction status (Ope \n",
      " n different values is 8 \n",
      "most popular is\n",
      " Maksettu        1631\n",
      "Avoin           1038\n",
      "Laskutettu       927\n",
      "Hyvitetty        855\n",
      "Peruttu          526\n",
      "Tulostettu       230\n",
      "Ei laskuteta      16\n",
      "Lähetetty         14\n",
      "Name: Transaction status (Ope, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Entry Date \n",
      " n different values is 246 \n",
      "most popular is\n",
      " 2010-01-21    320\n",
      "2009-01-28    216\n",
      "2008-10-01    194\n",
      "2010-01-28    174\n",
      "2010-02-19    169\n",
      "2010-06-02    132\n",
      "2010-04-07    124\n",
      "2009-12-21    116\n",
      "2010-02-10    108\n",
      "2009-02-12    105\n",
      "Name: Entry Date, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Invoicing Date \n",
      " n different values is 119 \n",
      "most popular is\n",
      " 2010-01-21    376\n",
      "2009-03-12    250\n",
      "2009-01-07    229\n",
      "2009-01-28    202\n",
      "2009-12-21    198\n",
      "2010-02-19    157\n",
      "2010-01-28    122\n",
      "2010-02-10    118\n",
      "2010-09-01    111\n",
      "2010-04-07    108\n",
      "Name: Invoicing Date, dtype: int64 \n",
      " number of nans is 685\n",
      "\n",
      "Payment Date \n",
      " n different values is 68 \n",
      "most popular is\n",
      " 2010-01-21    299\n",
      "2009-03-12    160\n",
      "2010-09-01    134\n",
      "2009-12-21    112\n",
      "2010-01-27    108\n",
      "2009-10-08    101\n",
      "2009-11-05     67\n",
      "2013-05-31     60\n",
      "2012-02-16     54\n",
      "2009-03-11     50\n",
      "Name: Payment Date, dtype: int64 \n",
      " number of nans is 3056\n",
      "\n",
      "Registered \n",
      " n different values is 268 \n",
      "most popular is\n",
      " 170.0    149\n",
      "0.0      130\n",
      "340.0    121\n",
      "850.0     80\n",
      "510.0     57\n",
      "85.0      55\n",
      "255.0     53\n",
      "600.0     38\n",
      "42.5      36\n",
      "400.0     33\n",
      "Name: Registered, dtype: int64 \n",
      " number of nans is 3625\n",
      "\n",
      "Invoiced \n",
      " n different values is 736 \n",
      "most popular is\n",
      "  0.0      143\n",
      " 170.0    106\n",
      " 340.0    101\n",
      " 255.0     66\n",
      " 510.0     53\n",
      " 85.0      45\n",
      " 850.0     44\n",
      "-340.0     36\n",
      "-170.0     35\n",
      " 45.0      35\n",
      "Name: Invoiced, dtype: int64 \n",
      " number of nans is 2902\n",
      "\n",
      "Paid \n",
      " n different values is 788 \n",
      "most popular is\n",
      " 0.00      157\n",
      "340.00     18\n",
      "170.00     17\n",
      "42.50      11\n",
      "510.00     10\n",
      "850.00      9\n",
      "85.00       9\n",
      "255.00      8\n",
      "46.02       8\n",
      "109.33      8\n",
      "Name: Paid, dtype: int64 \n",
      " number of nans is 3947\n",
      "\n",
      "Status (Registere \n",
      " n different values is 3 \n",
      "most popular is\n",
      " Laskutettu    2335\n",
      "Kirjattu      1612\n",
      "Maksettu      1290\n",
      "Name: Status (Registere, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Type (Fe \n",
      " n different values is 2 \n",
      "most popular is\n",
      " Palkkio    4205\n",
      "Kulu       1032\n",
      "Name: Type (Fe, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "User \n",
      " n different values is 16 \n",
      "most popular is\n",
      " 20db0c13-c601-4076-b161-7ac3af250c2d    1963\n",
      "f9cfb529-29b3-49aa-b748-932dfc07aba1     589\n",
      "f26b564c-f150-406b-8c72-e1d518514358     535\n",
      "8e4513e1-3e63-4c8a-ab28-a6692f85982f     463\n",
      "aa7aefd0-dbad-4217-a275-5d029ee655ef     389\n",
      "deb049ee-93d0-49df-8aea-1045c63a90f2     387\n",
      "554d8ec0-1514-4ac0-bf55-79bbbc075633     253\n",
      "08b2ac4f-28dc-4358-9c6c-0cb7fb389557     232\n",
      "d5fb8490-d3e9-44d6-8cbc-e8630f1e307f     197\n",
      "3017453a-7a7e-491e-b8f8-97a2060205d5      83\n",
      "Name: User, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Invoice Number \n",
      " n different values is 595 \n",
      "most popular is\n",
      " 20090144    72\n",
      "20080110    49\n",
      "20080044    45\n",
      "20090001    45\n",
      "20090199    42\n",
      "20090011    36\n",
      "20090204    36\n",
      "20090004    35\n",
      "20090129    30\n",
      "20090159    30\n",
      "Name: Invoice Number, dtype: int64 \n",
      " number of nans is 1612\n",
      "\n",
      "Non-Billable \n",
      " n different values is 2 \n",
      "most popular is\n",
      " False    5221\n",
      "True       16\n",
      "Name: Non-Billable, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Assignment.agency_gui \n",
      " n different values is 1 \n",
      "most popular is\n",
      " faa6df72-06be-4eaf-999e-022ef49754eb    5237\n",
      "Name: Assignment.agency_gui, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Assignment.Owner_Gui \n",
      " n different values is 13 \n",
      "most popular is\n",
      " 20db0c13-c601-4076-b161-7ac3af250c2d    2003\n",
      "08b2ac4f-28dc-4358-9c6c-0cb7fb389557     644\n",
      "deb049ee-93d0-49df-8aea-1045c63a90f2     451\n",
      "f9cfb529-29b3-49aa-b748-932dfc07aba1     444\n",
      "f26b564c-f150-406b-8c72-e1d518514358     435\n",
      "8e4513e1-3e63-4c8a-ab28-a6692f85982f     385\n",
      "aa7aefd0-dbad-4217-a275-5d029ee655ef     351\n",
      "d5fb8490-d3e9-44d6-8cbc-e8630f1e307f     213\n",
      "554d8ec0-1514-4ac0-bf55-79bbbc075633     207\n",
      "8a01f271-ba8c-4df2-a094-a18f04d72b7f      36\n",
      "Name: Assignment.Owner_Gui, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Assignment.Responsible_Person_Gui \n",
      " n different values is 15 \n",
      "most popular is\n",
      " 20db0c13-c601-4076-b161-7ac3af250c2d    2013\n",
      "08b2ac4f-28dc-4358-9c6c-0cb7fb389557     600\n",
      "deb049ee-93d0-49df-8aea-1045c63a90f2     507\n",
      "f26b564c-f150-406b-8c72-e1d518514358     444\n",
      "8e4513e1-3e63-4c8a-ab28-a6692f85982f     394\n",
      "f9cfb529-29b3-49aa-b748-932dfc07aba1     394\n",
      "aa7aefd0-dbad-4217-a275-5d029ee655ef     286\n",
      "554d8ec0-1514-4ac0-bf55-79bbbc075633     256\n",
      "d5fb8490-d3e9-44d6-8cbc-e8630f1e307f     192\n",
      "d42a7787-f11c-48ab-9749-506147b6c4f3      49\n",
      "Name: Assignment.Responsible_Person_Gui, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Assignment.Record_Gui \n",
      " n different values is 207 \n",
      "most popular is\n",
      " cbebe986-833e-4b9a-bbc9-597dae7f75d1    433\n",
      "4b5fe04a-cab6-4f77-96a5-af43cff1b41f    110\n",
      "e8be9ab2-07c7-458b-a512-8da0d8f7440c    106\n",
      "6c37c04b-225a-43a5-9ea6-4fc6812cbe05    103\n",
      "14af6ead-0cda-4052-acb2-41fad4719df8     92\n",
      "a553da1a-a17a-451d-8d99-efd3538aba65     91\n",
      "a6ef1723-e84f-4ce0-90b4-10485b757399     90\n",
      "1ff53ed0-65f3-46e8-bd68-0f452810b95b     86\n",
      "69b90290-9857-429f-87c8-0af1645487ee     86\n",
      "e7e09fb9-377c-4ce1-812c-6f91865ecb4e     83\n",
      "Name: Assignment.Record_Gui, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "User.Record_Gui \n",
      " n different values is 16 \n",
      "most popular is\n",
      " 20db0c13-c601-4076-b161-7ac3af250c2d    1963\n",
      "f9cfb529-29b3-49aa-b748-932dfc07aba1     589\n",
      "f26b564c-f150-406b-8c72-e1d518514358     535\n",
      "8e4513e1-3e63-4c8a-ab28-a6692f85982f     463\n",
      "aa7aefd0-dbad-4217-a275-5d029ee655ef     389\n",
      "deb049ee-93d0-49df-8aea-1045c63a90f2     387\n",
      "554d8ec0-1514-4ac0-bf55-79bbbc075633     253\n",
      "08b2ac4f-28dc-4358-9c6c-0cb7fb389557     232\n",
      "d5fb8490-d3e9-44d6-8cbc-e8630f1e307f     197\n",
      "3017453a-7a7e-491e-b8f8-97a2060205d5      83\n",
      "Name: User.Record_Gui, dtype: int64 \n",
      " number of nans is 0\n",
      "\n",
      "Users.Manager_Gui \n",
      " n different values is 0 \n",
      "most popular is\n",
      " Series([], Name: Users.Manager_Gui, dtype: int64) \n",
      " number of nans is 5237\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for colname in df.columns:\n",
    "    # print(df['Principal / Toimeksiantaja'].value_counts()()\n",
    "    value_counts = df[colname].value_counts()\n",
    "    print(colname, '\\n', 'n different values is', len(value_counts), '\\nmost popular is\\n', value_counts.head(10), '\\n number of nans is', df[colname].isnull().sum())\n",
    "    print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}