EDA_Task_1_daniel_pavelko.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "id": "initial_id",
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2024-07-11T13:54:35.160039Z",
     "start_time": "2024-07-11T13:54:35.154082Z"
    }
   },
   "source": [
    "# Setup and Import Libraries\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine"
   ],
   "outputs": [],
   "execution_count": 3
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-11T13:54:35.701627Z",
     "start_time": "2024-07-11T13:54:35.665654Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# Database Connection\n",
    "username = username\n",
    "password = password\n",
    "host = \"postgres.host.amazonaws.com\"\n",
    "port = \"5432\"\n",
    "database = \"postgres\"\n",
    "\n",
    "# Create the database URL\n",
    "db_url = f\"postgresql://{username}:{password}@{host}:{port}/{database}\"\n",
    "\n",
    "engine = create_engine(db_url)"
   ],
   "id": "783dd7374339bb18",
   "outputs": [],
   "execution_count": 4
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-11T13:54:36.523674Z",
     "start_time": "2024-07-11T13:54:36.518472Z"
    }
   },
   "cell_type": "code",
   "source": [
    "db_url = 'postgresql://password@postgres.c3ec2o0eqflu.us-east-2.rds.amazonaws.com:5432/postgres'\n",
    "\n",
    "engine = create_engine(db_url)"
   ],
   "id": "2743092b2831cf28",
   "outputs": [],
   "execution_count": 5
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "A SQL query is executed to fetch data from the database. The query groups ads by user and category, counts the number of ads per category, and assigns a rank based on the number of ads within each user group:",
   "id": "f7fff2bfe1a0119d"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-11T13:54:48.157799Z",
     "start_time": "2024-07-11T13:54:37.331405Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# SQL Query Execution\n",
    "sql_query = '''\n",
    "SELECT A.USER_ID,\n",
    "             A.CATEGORY_ID,\n",
    "             A.CATEGORY_NAME,\n",
    "             COUNT(A.CATEGORY_ID)                       AS ADS,\n",
    "             ROW_NUMBER() OVER (PARTITION BY A.USER_ID) AS CATEGORY_RANK\n",
    "      FROM ADS A\n",
    "      GROUP BY A.USER_ID, A.CATEGORY_ID, A.CATEGORY_NAME\n",
    "      ORDER BY 1, 5;\n",
    "'''\n",
    "\n",
    "# Loading Data into DataFrame\n",
    "df = pd.read_sql(sql_query, engine)\n",
    "df.head()"
   ],
   "id": "face9b57c6b74310",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   user_id  category_id      category_name  ads  category_rank\n",
       "0      449           37         electronic    1              1\n",
       "1    10327            1        real estate    1              1\n",
       "2    20342            3          car parts    1              1\n",
       "3    21747          891  fashion and style    3              1\n",
       "4    23066          891  fashion and style    1              1"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>category_id</th>\n",
       "      <th>category_name</th>\n",
       "      <th>ads</th>\n",
       "      <th>category_rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>449</td>\n",
       "      <td>37</td>\n",
       "      <td>electronic</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10327</td>\n",
       "      <td>1</td>\n",
       "      <td>real estate</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20342</td>\n",
       "      <td>3</td>\n",
       "      <td>car parts</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21747</td>\n",
       "      <td>891</td>\n",
       "      <td>fashion and style</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23066</td>\n",
       "      <td>891</td>\n",
       "      <td>fashion and style</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 6
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-11T17:43:18.825585Z",
     "start_time": "2024-07-11T17:43:16.935254Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# Data Transformation and Analysis\n",
    "pivot_table = df.pivot_table(index='user_id', columns='category_name', aggfunc='size', fill_value=0)\n",
    "\n",
    "# Calculate the overlap matrix\n",
    "overlap_df = pivot_table.T.dot(pivot_table)\n",
    "\n",
    "overlap_df.head(30)"
   ],
   "id": "9daa37217815a9d2",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "category_name          animals  business and services  car parts  children  \\\n",
       "category_name                                                                \n",
       "animals                  23865                    383        388      1022   \n",
       "business and services      383                  37285       1000      1178   \n",
       "car parts                  388                   1000      55184      1477   \n",
       "children                  1022                   1178       1477    104731   \n",
       "electronic                1051                   2131       4424      6059   \n",
       "fashion and style         1427                   2000       2101     17466   \n",
       "hobby, rest and sport      799                   1130       2017      5723   \n",
       "home and garden           1181                   2342       3075      5725   \n",
       "jobs                       112                    796        197       336   \n",
       "real estate                154                    367        235       512   \n",
       "transport                  241                    566       1920       457   \n",
       "\n",
       "category_name          electronic  fashion and style  hobby, rest and sport  \\\n",
       "category_name                                                                 \n",
       "animals                      1051               1427                    799   \n",
       "business and services        2131               2000                   1130   \n",
       "car parts                    4424               2101                   2017   \n",
       "children                     6059              17466                   5723   \n",
       "electronic                 146533              10283                   6885   \n",
       "fashion and style           10283             150964                   8518   \n",
       "hobby, rest and sport        6885               8518                  61327   \n",
       "home and garden              8488               8542                   6041   \n",
       "jobs                          637                715                    261   \n",
       "real estate                   689                756                    330   \n",
       "transport                    1245                600                    529   \n",
       "\n",
       "category_name          home and garden   jobs  real estate  transport  \n",
       "category_name                                                          \n",
       "animals                           1181    112          154        241  \n",
       "business and services             2342    796          367        566  \n",
       "car parts                         3075    197          235       1920  \n",
       "children                          5725    336          512        457  \n",
       "electronic                        8488    637          689       1245  \n",
       "fashion and style                 8542    715          756        600  \n",
       "hobby, rest and sport             6041    261          330        529  \n",
       "home and garden                  77636    401          662        804  \n",
       "jobs                               401  17257          174        165  \n",
       "real estate                        662    174        31288        213  \n",
       "transport                          804    165          213      36075  "
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>category_name</th>\n",
       "      <th>animals</th>\n",
       "      <th>business and services</th>\n",
       "      <th>car parts</th>\n",
       "      <th>children</th>\n",
       "      <th>electronic</th>\n",
       "      <th>fashion and style</th>\n",
       "      <th>hobby, rest and sport</th>\n",
       "      <th>home and garden</th>\n",
       "      <th>jobs</th>\n",
       "      <th>real estate</th>\n",
       "      <th>transport</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>animals</th>\n",
       "      <td>23865</td>\n",
       "      <td>383</td>\n",
       "      <td>388</td>\n",
       "      <td>1022</td>\n",
       "      <td>1051</td>\n",
       "      <td>1427</td>\n",
       "      <td>799</td>\n",
       "      <td>1181</td>\n",
       "      <td>112</td>\n",
       "      <td>154</td>\n",
       "      <td>241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>business and services</th>\n",
       "      <td>383</td>\n",
       "      <td>37285</td>\n",
       "      <td>1000</td>\n",
       "      <td>1178</td>\n",
       "      <td>2131</td>\n",
       "      <td>2000</td>\n",
       "      <td>1130</td>\n",
       "      <td>2342</td>\n",
       "      <td>796</td>\n",
       "      <td>367</td>\n",
       "      <td>566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>car parts</th>\n",
       "      <td>388</td>\n",
       "      <td>1000</td>\n",
       "      <td>55184</td>\n",
       "      <td>1477</td>\n",
       "      <td>4424</td>\n",
       "      <td>2101</td>\n",
       "      <td>2017</td>\n",
       "      <td>3075</td>\n",
       "      <td>197</td>\n",
       "      <td>235</td>\n",
       "      <td>1920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>children</th>\n",
       "      <td>1022</td>\n",
       "      <td>1178</td>\n",
       "      <td>1477</td>\n",
       "      <td>104731</td>\n",
       "      <td>6059</td>\n",
       "      <td>17466</td>\n",
       "      <td>5723</td>\n",
       "      <td>5725</td>\n",
       "      <td>336</td>\n",
       "      <td>512</td>\n",
       "      <td>457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>electronic</th>\n",
       "      <td>1051</td>\n",
       "      <td>2131</td>\n",
       "      <td>4424</td>\n",
       "      <td>6059</td>\n",
       "      <td>146533</td>\n",
       "      <td>10283</td>\n",
       "      <td>6885</td>\n",
       "      <td>8488</td>\n",
       "      <td>637</td>\n",
       "      <td>689</td>\n",
       "      <td>1245</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fashion and style</th>\n",
       "      <td>1427</td>\n",
       "      <td>2000</td>\n",
       "      <td>2101</td>\n",
       "      <td>17466</td>\n",
       "      <td>10283</td>\n",
       "      <td>150964</td>\n",
       "      <td>8518</td>\n",
       "      <td>8542</td>\n",
       "      <td>715</td>\n",
       "      <td>756</td>\n",
       "      <td>600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hobby, rest and sport</th>\n",
       "      <td>799</td>\n",
       "      <td>1130</td>\n",
       "      <td>2017</td>\n",
       "      <td>5723</td>\n",
       "      <td>6885</td>\n",
       "      <td>8518</td>\n",
       "      <td>61327</td>\n",
       "      <td>6041</td>\n",
       "      <td>261</td>\n",
       "      <td>330</td>\n",
       "      <td>529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>home and garden</th>\n",
       "      <td>1181</td>\n",
       "      <td>2342</td>\n",
       "      <td>3075</td>\n",
       "      <td>5725</td>\n",
       "      <td>8488</td>\n",
       "      <td>8542</td>\n",
       "      <td>6041</td>\n",
       "      <td>77636</td>\n",
       "      <td>401</td>\n",
       "      <td>662</td>\n",
       "      <td>804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>jobs</th>\n",
       "      <td>112</td>\n",
       "      <td>796</td>\n",
       "      <td>197</td>\n",
       "      <td>336</td>\n",
       "      <td>637</td>\n",
       "      <td>715</td>\n",
       "      <td>261</td>\n",
       "      <td>401</td>\n",
       "      <td>17257</td>\n",
       "      <td>174</td>\n",
       "      <td>165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>real estate</th>\n",
       "      <td>154</td>\n",
       "      <td>367</td>\n",
       "      <td>235</td>\n",
       "      <td>512</td>\n",
       "      <td>689</td>\n",
       "      <td>756</td>\n",
       "      <td>330</td>\n",
       "      <td>662</td>\n",
       "      <td>174</td>\n",
       "      <td>31288</td>\n",
       "      <td>213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>transport</th>\n",
       "      <td>241</td>\n",
       "      <td>566</td>\n",
       "      <td>1920</td>\n",
       "      <td>457</td>\n",
       "      <td>1245</td>\n",
       "      <td>600</td>\n",
       "      <td>529</td>\n",
       "      <td>804</td>\n",
       "      <td>165</td>\n",
       "      <td>213</td>\n",
       "      <td>36075</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 47
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-11T16:05:57.687454Z",
     "start_time": "2024-07-11T16:05:57.611932Z"
    }
   },
   "cell_type": "code",
   "source": [
    "import gspread\n",
    "from oauth2client.service_account import ServiceAccountCredentials\n",
    "from datetime import datetime\n",
    "from googleapiclient.discovery import build"
   ],
   "id": "188361460f94fa62",
   "outputs": [],
   "execution_count": 36
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-11T14:48:03.559415Z",
     "start_time": "2024-07-11T14:48:03.553514Z"
    }
   },
   "cell_type": "code",
   "source": "scope = [\"https://spreadsheets.google.com/feeds\", \"https://www.googleapis.com/auth/drive\"]",
   "id": "bb713c375d87a803",
   "outputs": [],
   "execution_count": 21
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-11T15:26:15.164501Z",
     "start_time": "2024-07-11T15:26:15.128813Z"
    }
   },
   "cell_type": "code",
   "source": "creds =ServiceAccountCredentials.from_json_keyfile_name('/Users/danielpavelko/Downloads/olx-daniel-pavelko-1892afec055d.json', scope)",
   "id": "a25eac9e2eb318e",
   "outputs": [],
   "execution_count": 26
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-11T15:26:49.727206Z",
     "start_time": "2024-07-11T15:26:49.697687Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# Authorize the clientsheet \n",
    "client = gspread.authorize(creds)"
   ],
   "id": "507bbb01197405e5",
   "outputs": [],
   "execution_count": 27
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-11T15:27:24.154382Z",
     "start_time": "2024-07-11T15:27:18.778776Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# Create the spreadsheet\n",
    "spreadsheet_name = 'OLX Pavelko Daniel - XX:XXXX:XX'\n",
    "\n",
    "spreadsheet = client.create(spreadsheet_name)\n",
    "\n",
    "print(\"Spreadsheet '{spreadsheet_name}' created successfully.\")"
   ],
   "id": "5a1bf5aa055794ab",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Spreadsheet 'OLX Pavelko Daniel - 2024-07-11' created successfully.\n"
     ]
    }
   ],
   "execution_count": 28
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-11T15:33:42.429697Z",
     "start_time": "2024-07-11T15:33:39.071231Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# Select the first sheet in the newly created spreadsheet\n",
    "worksheet = spreadsheet.get_worksheet(0)\n",
    "\n",
    "# Rename the first sheet to \"Test task\"\n",
    "worksheet.update_title(\"Test task\")\n",
    "\n",
    "# Convert the DataFrame to a list of lists for updating the Google Sheet\n",
    "data_to_upload = [overlap_df.columns.values.tolist()] + overlap_df.reset_index().values.tolist()\n",
    "\n",
    "# Update the worksheet with the data\n",
    "worksheet.update(data_to_upload)\n",
    "\n",
    "print(f\"Spreadsheet '{spreadsheet_name}' with sheet 'Test task' updated successfully.\")"
   ],
   "id": "78b75a696e68a851",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Spreadsheet 'OLX Pavelko Daniel - 2024-07-11' with sheet 'Test task' updated successfully.\n"
     ]
    }
   ],
   "execution_count": 29
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-11T15:39:39.808720Z",
     "start_time": "2024-07-11T15:39:39.805287Z"
    }
   },
   "cell_type": "code",
   "source": [
    "spreadsheet_url = f\"https://docs.google.com/spreadsheets/d/{spreadsheet.id}/edit\"\n",
    "print(f\"Open the spreadsheet here: {spreadsheet_url}\")"
   ],
   "id": "aaa722a82dba72dd",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Open the spreadsheet here: https://docs.google.com/spreadsheets/d/192Nm8ARcdJq0XhigBN7F6jSb9zl-clm6h9kr4oOvyho/edit\n"
     ]
    }
   ],
   "execution_count": 32
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-11T17:46:17.080246Z",
     "start_time": "2024-07-11T17:46:15.288331Z"
    }
   },
   "cell_type": "code",
   "source": [
    "drive_service = build('drive', 'v3', credentials=creds)\n",
    "\n",
    "permission = {\n",
    "    'type': 'anyone',\n",
    "    'role': 'reader'\n",
    "}\n",
    "drive_service.permissions().create(\n",
    "    fileId=spreadsheet.id,\n",
    "    body=permission,\n",
    "    fields='id'\n",
    ").execute()"
   ],
   "id": "8c8b067b1c61431",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'id': 'anyoneWithLink'}"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 48
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}