relacion_csvs.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "auctions_dtypes = {'auction_type_id': np.float16, 'country': 'category', 'platform': 'category',\n",
    "                   'ref_type_id': 'category', 'source_id': 'category', 'device_id': 'category'}\n",
    "\n",
    "auctions = pd.read_csv('auctions.csv', dtype = auctions_dtypes)\n",
    "\n",
    "auctions.drop('auction_type_id', axis = 1, inplace = True)  #todas Nan\n",
    "\n",
    "auctions[\"date\"] =  pd.to_datetime(auctions[\"date\"], errors = \"coerce\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "clicks_dtypes = {'advertiser': np.int8, 'action_id': np.float16, 'source_id': np.int8, 'country_code': 'category',\n",
    "                 'latitude': np.float16, 'longitude': np.float16, 'carrier_id': np.float16, 'brand': np.float16,\n",
    "                 'touchX': np.float16, 'touchY': np.float16, 'ref_hash': 'category'}\n",
    "\n",
    "clicks = pd.read_csv('clicks.csv', dtype = clicks_dtypes)\n",
    "\n",
    "clicks.drop('action_id', axis = 1, inplace = True) #todos NaN\n",
    "clicks.drop('wifi_connection', axis = 1, inplace = True) #todos False\n",
    "\n",
    "clicks[\"created\"] =  pd.to_datetime(clicks[\"created\"], errors = \"coerce\") #parseo de fechas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>advertiser_id</th>\n",
       "      <th>source_id</th>\n",
       "      <th>created</th>\n",
       "      <th>country_code</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>carrier_id</th>\n",
       "      <th>trans_id</th>\n",
       "      <th>os_minor</th>\n",
       "      <th>agent_device</th>\n",
       "      <th>os_major</th>\n",
       "      <th>specs_brand</th>\n",
       "      <th>brand</th>\n",
       "      <th>timeToClick</th>\n",
       "      <th>touchX</th>\n",
       "      <th>touchY</th>\n",
       "      <th>ref_type</th>\n",
       "      <th>ref_hash</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-03-06 22:42:12.755</td>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>1.206055</td>\n",
       "      <td>1.070312</td>\n",
       "      <td>1.0</td>\n",
       "      <td>iGgClCM9exiHF4K31g94XmvHEBSLKIY</td>\n",
       "      <td>6.768137e+18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.072849e+18</td>\n",
       "      <td>2733035977666442898</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.563</td>\n",
       "      <td>0.904785</td>\n",
       "      <td>0.078003</td>\n",
       "      <td>1891515180541284343</td>\n",
       "      <td>1904083516767779093</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   advertiser_id  source_id                 created         country_code  \\\n",
       "0              2          4 2019-03-06 22:42:12.755  6333597102633388268   \n",
       "\n",
       "   latitude  longitude  carrier_id                         trans_id  \\\n",
       "0  1.206055   1.070312         1.0  iGgClCM9exiHF4K31g94XmvHEBSLKIY   \n",
       "\n",
       "       os_minor  agent_device      os_major          specs_brand  brand  \\\n",
       "0  6.768137e+18           NaN  3.072849e+18  2733035977666442898    NaN   \n",
       "\n",
       "   timeToClick    touchX    touchY             ref_type             ref_hash  \n",
       "0        1.563  0.904785  0.078003  1891515180541284343  1904083516767779093  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clicks.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    13354597\n",
       "1     4016739\n",
       "5     1466494\n",
       "2      582083\n",
       "6      151406\n",
       "Name: source_id, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "auctions['source_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>advertiser_id</th>\n",
       "      <th>source_id</th>\n",
       "      <th>created</th>\n",
       "      <th>country_code</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>carrier_id</th>\n",
       "      <th>trans_id</th>\n",
       "      <th>os_minor</th>\n",
       "      <th>agent_device</th>\n",
       "      <th>os_major</th>\n",
       "      <th>specs_brand</th>\n",
       "      <th>brand</th>\n",
       "      <th>timeToClick</th>\n",
       "      <th>touchX</th>\n",
       "      <th>touchY</th>\n",
       "      <th>ref_type</th>\n",
       "      <th>ref_hash</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2019-03-07 23:29:13.617</td>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>1.206055</td>\n",
       "      <td>1.070312</td>\n",
       "      <td>6.0</td>\n",
       "      <td>RcMoGv9oLfpzkpmUKwyj3GeF6RUalWE</td>\n",
       "      <td>5.975656e+18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.072849e+18</td>\n",
       "      <td>392184377613098015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.927</td>\n",
       "      <td>0.917969</td>\n",
       "      <td>0.068970</td>\n",
       "      <td>1891515180541284343</td>\n",
       "      <td>4063522808706112974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2019-03-07 23:59:09.969</td>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>1.206055</td>\n",
       "      <td>1.070312</td>\n",
       "      <td>6.0</td>\n",
       "      <td>_n6W8pe5-DyzcWjAyx_1Ir2MrGofV8o</td>\n",
       "      <td>5.975656e+18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.072849e+18</td>\n",
       "      <td>392184377613098015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>281.442</td>\n",
       "      <td>0.872070</td>\n",
       "      <td>0.109985</td>\n",
       "      <td>1891515180541284343</td>\n",
       "      <td>4063522808706112974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>643</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2019-03-08 00:04:11.194</td>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>1.206055</td>\n",
       "      <td>1.070312</td>\n",
       "      <td>6.0</td>\n",
       "      <td>zv6mw0gkpo22AL3deRc0VvTkeubn-0k</td>\n",
       "      <td>5.975656e+18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.072849e+18</td>\n",
       "      <td>392184377613098015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>299.161</td>\n",
       "      <td>0.883789</td>\n",
       "      <td>0.406006</td>\n",
       "      <td>1891515180541284343</td>\n",
       "      <td>4063522808706112974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1991</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2019-03-08 10:24:47.814</td>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>1.206055</td>\n",
       "      <td>1.070312</td>\n",
       "      <td>6.0</td>\n",
       "      <td>FMEDuvfm-pllraU6dtn8vKNQGNZMgYU</td>\n",
       "      <td>5.975656e+18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.072849e+18</td>\n",
       "      <td>392184377613098015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.387</td>\n",
       "      <td>0.819824</td>\n",
       "      <td>0.342041</td>\n",
       "      <td>1891515180541284343</td>\n",
       "      <td>4063522808706112974</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      advertiser_id  source_id                 created         country_code  \\\n",
       "130               3          0 2019-03-07 23:29:13.617  6333597102633388268   \n",
       "215               3          0 2019-03-07 23:59:09.969  6333597102633388268   \n",
       "643               3          0 2019-03-08 00:04:11.194  6333597102633388268   \n",
       "1991              3          0 2019-03-08 10:24:47.814  6333597102633388268   \n",
       "\n",
       "      latitude  longitude  carrier_id                         trans_id  \\\n",
       "130   1.206055   1.070312         6.0  RcMoGv9oLfpzkpmUKwyj3GeF6RUalWE   \n",
       "215   1.206055   1.070312         6.0  _n6W8pe5-DyzcWjAyx_1Ir2MrGofV8o   \n",
       "643   1.206055   1.070312         6.0  zv6mw0gkpo22AL3deRc0VvTkeubn-0k   \n",
       "1991  1.206055   1.070312         6.0  FMEDuvfm-pllraU6dtn8vKNQGNZMgYU   \n",
       "\n",
       "          os_minor  agent_device      os_major         specs_brand  brand  \\\n",
       "130   5.975656e+18           NaN  3.072849e+18  392184377613098015    NaN   \n",
       "215   5.975656e+18           NaN  3.072849e+18  392184377613098015    NaN   \n",
       "643   5.975656e+18           NaN  3.072849e+18  392184377613098015    NaN   \n",
       "1991  5.975656e+18           NaN  3.072849e+18  392184377613098015    NaN   \n",
       "\n",
       "      timeToClick    touchX    touchY             ref_type  \\\n",
       "130         2.927  0.917969  0.068970  1891515180541284343   \n",
       "215       281.442  0.872070  0.109985  1891515180541284343   \n",
       "643       299.161  0.883789  0.406006  1891515180541284343   \n",
       "1991        6.387  0.819824  0.342041  1891515180541284343   \n",
       "\n",
       "                 ref_hash  \n",
       "130   4063522808706112974  \n",
       "215   4063522808706112974  \n",
       "643   4063522808706112974  \n",
       "1991  4063522808706112974  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clicks.loc[clicks['ref_hash']== '4063522808706112974'].head(4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4063522808706112974    41\n",
       "6841615430383729819    32\n",
       "1244272413279716745    26\n",
       "Name: ref_hash, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clicks['ref_hash'].value_counts().head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>date</th>\n",
       "      <th>device_id</th>\n",
       "      <th>platform</th>\n",
       "      <th>ref_type_id</th>\n",
       "      <th>source_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1655</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-11 14:14:52.170506</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1665</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-11 14:15:03.789245</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2192</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-11 14:14:09.838105</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2498</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-11 14:21:01.879003</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4095</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-11 13:57:53.254093</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5703</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-11 14:20:51.915593</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6268</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-11 14:15:16.714159</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12520</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 02:59:54.704551</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15561</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 03:27:57.888289</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27730</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-07 14:27:41.382050</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28833</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-07 14:04:38.386425</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31079</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-07 14:01:34.799761</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31276</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-07 14:04:30.440935</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31614</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-07 14:46:47.262589</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35838</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-07 14:00:19.861870</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36604</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-07 14:46:45.660698</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36626</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-07 14:47:04.586819</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43380</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-07 14:01:03.987771</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44525</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-07 14:01:04.288402</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49239</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-07 14:46:58.609065</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58233</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-08 21:54:17.261588</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61640</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-08 21:59:08.222124</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64867</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-08 22:04:13.092584</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66896</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-08 22:09:10.721190</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69353</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-08 22:29:12.906684</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70498</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-08 22:39:42.573449</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70778</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-08 22:42:24.301753</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71868</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-08 22:39:40.722180</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75260</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-08 22:34:14.726627</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75698</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-08 21:59:08.410147</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19287519</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-11 13:27:12.795376</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19288694</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-11 13:42:10.930874</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19288832</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-11 13:46:13.323393</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19316514</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-09 15:56:35.845470</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19367772</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-11 17:04:57.247292</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19369355</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-11 16:59:29.547474</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19371237</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-11 17:03:49.747080</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19371244</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-11 17:03:56.744965</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19393931</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 15:15:50.460386</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19426133</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 03:23:53.613382</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19426384</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 03:00:34.917036</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19426483</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 03:03:07.998290</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19430864</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-13 05:08:09.739375</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19431054</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-13 05:07:44.805734</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19519623</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 11:56:44.211304</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19519755</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 11:26:56.955534</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19522232</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 11:41:32.733862</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19525065</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 11:26:54.637440</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19525066</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 11:26:55.234700</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19526313</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 11:27:39.577917</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19526788</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 11:41:29.955477</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19526789</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 11:41:31.087272</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19527650</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 11:31:29.197059</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19533016</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 16:42:19.950870</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19533354</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 16:50:09.813139</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19545627</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 16:55:10.767710</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19547476</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 17:15:16.331838</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19550384</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 17:00:16.182365</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19555831</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 17:17:17.014764</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19555832</th>\n",
       "      <td>6333597102633388268</td>\n",
       "      <td>2019-03-12 17:17:17.090459</td>\n",
       "      <td>4063522808706112974</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5596 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      country                       date            device_id  \\\n",
       "1655      6333597102633388268 2019-03-11 14:14:52.170506  4063522808706112974   \n",
       "1665      6333597102633388268 2019-03-11 14:15:03.789245  4063522808706112974   \n",
       "2192      6333597102633388268 2019-03-11 14:14:09.838105  4063522808706112974   \n",
       "2498      6333597102633388268 2019-03-11 14:21:01.879003  4063522808706112974   \n",
       "4095      6333597102633388268 2019-03-11 13:57:53.254093  4063522808706112974   \n",
       "5703      6333597102633388268 2019-03-11 14:20:51.915593  4063522808706112974   \n",
       "6268      6333597102633388268 2019-03-11 14:15:16.714159  4063522808706112974   \n",
       "12520     6333597102633388268 2019-03-12 02:59:54.704551  4063522808706112974   \n",
       "15561     6333597102633388268 2019-03-12 03:27:57.888289  4063522808706112974   \n",
       "27730     6333597102633388268 2019-03-07 14:27:41.382050  4063522808706112974   \n",
       "28833     6333597102633388268 2019-03-07 14:04:38.386425  4063522808706112974   \n",
       "31079     6333597102633388268 2019-03-07 14:01:34.799761  4063522808706112974   \n",
       "31276     6333597102633388268 2019-03-07 14:04:30.440935  4063522808706112974   \n",
       "31614     6333597102633388268 2019-03-07 14:46:47.262589  4063522808706112974   \n",
       "35838     6333597102633388268 2019-03-07 14:00:19.861870  4063522808706112974   \n",
       "36604     6333597102633388268 2019-03-07 14:46:45.660698  4063522808706112974   \n",
       "36626     6333597102633388268 2019-03-07 14:47:04.586819  4063522808706112974   \n",
       "43380     6333597102633388268 2019-03-07 14:01:03.987771  4063522808706112974   \n",
       "44525     6333597102633388268 2019-03-07 14:01:04.288402  4063522808706112974   \n",
       "49239     6333597102633388268 2019-03-07 14:46:58.609065  4063522808706112974   \n",
       "58233     6333597102633388268 2019-03-08 21:54:17.261588  4063522808706112974   \n",
       "61640     6333597102633388268 2019-03-08 21:59:08.222124  4063522808706112974   \n",
       "64867     6333597102633388268 2019-03-08 22:04:13.092584  4063522808706112974   \n",
       "66896     6333597102633388268 2019-03-08 22:09:10.721190  4063522808706112974   \n",
       "69353     6333597102633388268 2019-03-08 22:29:12.906684  4063522808706112974   \n",
       "70498     6333597102633388268 2019-03-08 22:39:42.573449  4063522808706112974   \n",
       "70778     6333597102633388268 2019-03-08 22:42:24.301753  4063522808706112974   \n",
       "71868     6333597102633388268 2019-03-08 22:39:40.722180  4063522808706112974   \n",
       "75260     6333597102633388268 2019-03-08 22:34:14.726627  4063522808706112974   \n",
       "75698     6333597102633388268 2019-03-08 21:59:08.410147  4063522808706112974   \n",
       "...                       ...                        ...                  ...   \n",
       "19287519  6333597102633388268 2019-03-11 13:27:12.795376  4063522808706112974   \n",
       "19288694  6333597102633388268 2019-03-11 13:42:10.930874  4063522808706112974   \n",
       "19288832  6333597102633388268 2019-03-11 13:46:13.323393  4063522808706112974   \n",
       "19316514  6333597102633388268 2019-03-09 15:56:35.845470  4063522808706112974   \n",
       "19367772  6333597102633388268 2019-03-11 17:04:57.247292  4063522808706112974   \n",
       "19369355  6333597102633388268 2019-03-11 16:59:29.547474  4063522808706112974   \n",
       "19371237  6333597102633388268 2019-03-11 17:03:49.747080  4063522808706112974   \n",
       "19371244  6333597102633388268 2019-03-11 17:03:56.744965  4063522808706112974   \n",
       "19393931  6333597102633388268 2019-03-12 15:15:50.460386  4063522808706112974   \n",
       "19426133  6333597102633388268 2019-03-12 03:23:53.613382  4063522808706112974   \n",
       "19426384  6333597102633388268 2019-03-12 03:00:34.917036  4063522808706112974   \n",
       "19426483  6333597102633388268 2019-03-12 03:03:07.998290  4063522808706112974   \n",
       "19430864  6333597102633388268 2019-03-13 05:08:09.739375  4063522808706112974   \n",
       "19431054  6333597102633388268 2019-03-13 05:07:44.805734  4063522808706112974   \n",
       "19519623  6333597102633388268 2019-03-12 11:56:44.211304  4063522808706112974   \n",
       "19519755  6333597102633388268 2019-03-12 11:26:56.955534  4063522808706112974   \n",
       "19522232  6333597102633388268 2019-03-12 11:41:32.733862  4063522808706112974   \n",
       "19525065  6333597102633388268 2019-03-12 11:26:54.637440  4063522808706112974   \n",
       "19525066  6333597102633388268 2019-03-12 11:26:55.234700  4063522808706112974   \n",
       "19526313  6333597102633388268 2019-03-12 11:27:39.577917  4063522808706112974   \n",
       "19526788  6333597102633388268 2019-03-12 11:41:29.955477  4063522808706112974   \n",
       "19526789  6333597102633388268 2019-03-12 11:41:31.087272  4063522808706112974   \n",
       "19527650  6333597102633388268 2019-03-12 11:31:29.197059  4063522808706112974   \n",
       "19533016  6333597102633388268 2019-03-12 16:42:19.950870  4063522808706112974   \n",
       "19533354  6333597102633388268 2019-03-12 16:50:09.813139  4063522808706112974   \n",
       "19545627  6333597102633388268 2019-03-12 16:55:10.767710  4063522808706112974   \n",
       "19547476  6333597102633388268 2019-03-12 17:15:16.331838  4063522808706112974   \n",
       "19550384  6333597102633388268 2019-03-12 17:00:16.182365  4063522808706112974   \n",
       "19555831  6333597102633388268 2019-03-12 17:17:17.014764  4063522808706112974   \n",
       "19555832  6333597102633388268 2019-03-12 17:17:17.090459  4063522808706112974   \n",
       "\n",
       "         platform ref_type_id source_id  \n",
       "1655            1           1         0  \n",
       "1665            1           1         0  \n",
       "2192            1           1         0  \n",
       "2498            1           1         0  \n",
       "4095            1           1         0  \n",
       "5703            1           1         0  \n",
       "6268            1           1         0  \n",
       "12520           1           1         0  \n",
       "15561           1           1         0  \n",
       "27730           1           1         0  \n",
       "28833           1           1         0  \n",
       "31079           1           1         0  \n",
       "31276           1           1         0  \n",
       "31614           1           1         0  \n",
       "35838           1           1         0  \n",
       "36604           1           1         0  \n",
       "36626           1           1         0  \n",
       "43380           1           1         0  \n",
       "44525           1           1         0  \n",
       "49239           1           1         0  \n",
       "58233           1           1         0  \n",
       "61640           1           1         0  \n",
       "64867           1           1         0  \n",
       "66896           1           1         0  \n",
       "69353           1           1         0  \n",
       "70498           1           1         0  \n",
       "70778           1           1         0  \n",
       "71868           1           1         0  \n",
       "75260           1           1         0  \n",
       "75698           1           1         0  \n",
       "...           ...         ...       ...  \n",
       "19287519        1           1         0  \n",
       "19288694        1           1         0  \n",
       "19288832        1           1         0  \n",
       "19316514        1           1         0  \n",
       "19367772        1           1         0  \n",
       "19369355        1           1         0  \n",
       "19371237        1           1         0  \n",
       "19371244        1           1         0  \n",
       "19393931        1           1         0  \n",
       "19426133        1           1         0  \n",
       "19426384        1           1         0  \n",
       "19426483        1           1         0  \n",
       "19430864        1           1         0  \n",
       "19431054        1           1         0  \n",
       "19519623        1           1         0  \n",
       "19519755        1           1         0  \n",
       "19522232        1           1         0  \n",
       "19525065        1           1         0  \n",
       "19525066        1           1         0  \n",
       "19526313        1           1         0  \n",
       "19526788        1           1         0  \n",
       "19526789        1           1         0  \n",
       "19527650        1           1         0  \n",
       "19533016        1           1         0  \n",
       "19533354        1           1         0  \n",
       "19545627        1           1         0  \n",
       "19547476        1           1         0  \n",
       "19550384        1           1         0  \n",
       "19555831        1           1         0  \n",
       "19555832        1           1         0  \n",
       "\n",
       "[5596 rows x 6 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "auctions.loc[auctions['device_id'] == '4063522808706112974']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "633139769114048761     27762\n",
       "7202276635029175071    23055\n",
       "7298861376107043945    18188\n",
       "6302840482782120473    16400\n",
       "5376802567578262905    16367\n",
       "Name: device_id, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "auctions['device_id'].value_counts().head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "633139769114048761     27762\n",
       "7202276635029175071    23055\n",
       "7298861376107043945    18188\n",
       "6302840482782120473    16400\n",
       "5376802567578262905    16367\n",
       "8963711959081981780    14362\n",
       "795159065504552200     12275\n",
       "6841615430383729819    12077\n",
       "5384039226444052914    11632\n",
       "3198179064438296471    11565\n",
       "519658456951133298     10541\n",
       "2435544514359302887    10274\n",
       "8751190726444323885    10256\n",
       "1835469468095030098    10121\n",
       "1800317677967270083    10036\n",
       "9027154272484048876     9958\n",
       "7574657722181550308     9767\n",
       "5051610571898428259     9642\n",
       "320899491737875154      9388\n",
       "8292793494956750501     9315\n",
       "201759600719985554      9158\n",
       "8021195662481548645     9128\n",
       "7650408579713341483     9020\n",
       "6392567838173461255     8994\n",
       "1478017482044456084     8839\n",
       "6203407103275384250     8823\n",
       "68016455690089173       8809\n",
       "8496718680483076445     8682\n",
       "8491787427844905567     8513\n",
       "4834359787191717922     8495\n",
       "                       ...  \n",
       "5965915879234710356        1\n",
       "5962371857976871552        1\n",
       "8775830221902480502        1\n",
       "6400673246409330938        1\n",
       "8848059905278451239        1\n",
       "2387472711882692396        1\n",
       "6919864391159784728        1\n",
       "6918436643991280288        1\n",
       "1265394499505454114        1\n",
       "1192257502202972678        1\n",
       "9203091890242370281        1\n",
       "116130905366082001         1\n",
       "6746874540432696049        1\n",
       "2349117575888212633        1\n",
       "3115384134991732020        1\n",
       "6658609489857321554        1\n",
       "6703916290356312366        1\n",
       "3122486780808724561        1\n",
       "9128287699651516130        1\n",
       "9090768995352915488        1\n",
       "9043530082076844214        1\n",
       "8981735392801911779        1\n",
       "3142217809159395792        1\n",
       "6583122778867177908        1\n",
       "6552357094313704           1\n",
       "8900190054383012348        1\n",
       "6516634122275645386        1\n",
       "8850035083239840651        1\n",
       "6420983180219968660        1\n",
       "954913365148193776         1\n",
       "Name: device_id, Length: 206977, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "auctions['device_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}