cell_to_homes.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "79fff5a4-f938-4c45-a638-3d3fbc4b08e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import dask.dataframe as dd\n",
    "import glob\n",
    "\n",
    "pd.options.mode.chained_assignment = None  # default='warn'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "afb66c02-8276-4866-875d-64e297688e1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "dir_path = \"/work/group/oit_research_data/mobility/data/data_DFW_2021/data/DFW_2021_01/\"\n",
    "file_name = \"A10_DFW\"\n",
    "ext = \".tsv\"\n",
    "file_path = dir_path+file_name+ext"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "9c01d73d-5dff-48fb-bc16-9f8e1b158561",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the start and end hours for filtering\n",
    "start_hour = 1\n",
    "end_hour = 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e4a87975-ed53-4424-8d6c-2541f6af219c",
   "metadata": {},
   "outputs": [],
   "source": [
    "threshold=1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "70ec6996-5038-45b5-ae1a-0a559f564bd8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to convert Unix timestamps to seconds (if not already in seconds)\n",
    "def convert_timestamp_to_dt(ddf, tz='US/Central'):        \n",
    "    ddf['DateTime'] = dd.to_datetime(ddf['Unix Timestamp'],unit='s')    \n",
    "    #ddf['DateTime'] = ddf[\"DateTime\"].round(\"H\")\n",
    "    ddf.DateTime = ddf.DateTime.dt.tz_localize('UTC').dt.tz_convert(tz)  \n",
    "    #ddf['DateTime']=ddf['DateTime'].astype(str)\n",
    "    #ddf.DateTime = ddf.DateTime.str.slice(stop=-6)\n",
    "    ddf['Date'] = ddf.DateTime.dt.date\n",
    "    return ddf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "75166053-4daf-48ad-865a-b5e5e0d18d8a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def keep_rows_between_hours(df, start_hour, end_hour):\n",
    "    df = df[(df['DateTime'].dt.hour >= start_hour) & (df['DateTime'].dt.hour <= end_hour)]\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5704757e-e4c3-4ead-a748-eca1f1a3e9f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the large TSV file in chunks using Dask\n",
    "columns=[\"Polygon_ID\",\"Device_ID\",\"Lat\",\"Lon\",\"Unix Timestamp\"]\n",
    "df = dd.read_csv(file_path, sep='\\t', names=columns, header=None,\n",
    "                 dtype={\"Polygon_ID\":'object',\"Device_ID\":'object',\"Lat\":'float64',\"Lon\":'float64',\"Unix Timestamp\":'object'}).compute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a57037e3-708f-43b9-a76d-999b5718cc51",
   "metadata": {},
   "outputs": [],
   "source": [
    "ddf = dd.from_pandas(df, npartitions=256)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "f38ea31a-cf90-4637-8a8e-07ab871b97e3",
   "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>Polygon_ID</th>\n",
       "      <th>Device_ID</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Lon</th>\n",
       "      <th>Unix Timestamp</th>\n",
       "      <th>DateTime</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>P2</td>\n",
       "      <td>9a220574a893533efb3773aa3ca3282ba5834775</td>\n",
       "      <td>32.166847</td>\n",
       "      <td>-97.080640</td>\n",
       "      <td>1609459264</td>\n",
       "      <td>2020-12-31 18:01:04-06:00</td>\n",
       "      <td>2020-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>P2</td>\n",
       "      <td>9a220574a893533efb3773aa3ca3282ba5834775</td>\n",
       "      <td>32.166851</td>\n",
       "      <td>-97.080673</td>\n",
       "      <td>1609460462</td>\n",
       "      <td>2020-12-31 18:21:02-06:00</td>\n",
       "      <td>2020-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>P2</td>\n",
       "      <td>9a220574a893533efb3773aa3ca3282ba5834775</td>\n",
       "      <td>32.166850</td>\n",
       "      <td>-97.080641</td>\n",
       "      <td>1609461666</td>\n",
       "      <td>2020-12-31 18:41:06-06:00</td>\n",
       "      <td>2020-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>P2</td>\n",
       "      <td>9a220574a893533efb3773aa3ca3282ba5834775</td>\n",
       "      <td>32.166862</td>\n",
       "      <td>-97.080650</td>\n",
       "      <td>1609462887</td>\n",
       "      <td>2020-12-31 19:01:27-06:00</td>\n",
       "      <td>2020-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>P2</td>\n",
       "      <td>9a220574a893533efb3773aa3ca3282ba5834775</td>\n",
       "      <td>32.166862</td>\n",
       "      <td>-97.080650</td>\n",
       "      <td>1609462888</td>\n",
       "      <td>2020-12-31 19:01:28-06:00</td>\n",
       "      <td>2020-12-31</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215919</th>\n",
       "      <td>E14</td>\n",
       "      <td>8bbde1b26d7ce211705e6f1cc808d51dcacf5b95</td>\n",
       "      <td>32.227679</td>\n",
       "      <td>-97.009669</td>\n",
       "      <td>1610322995</td>\n",
       "      <td>2021-01-10 17:56:35-06:00</td>\n",
       "      <td>2021-01-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215920</th>\n",
       "      <td>E14</td>\n",
       "      <td>8bbde1b26d7ce211705e6f1cc808d51dcacf5b95</td>\n",
       "      <td>32.229966</td>\n",
       "      <td>-97.012108</td>\n",
       "      <td>1610323040</td>\n",
       "      <td>2021-01-10 17:57:20-06:00</td>\n",
       "      <td>2021-01-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215921</th>\n",
       "      <td>D14</td>\n",
       "      <td>8bbde1b26d7ce211705e6f1cc808d51dcacf5b95</td>\n",
       "      <td>32.236599</td>\n",
       "      <td>-97.010914</td>\n",
       "      <td>1610323085</td>\n",
       "      <td>2021-01-10 17:58:05-06:00</td>\n",
       "      <td>2021-01-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215922</th>\n",
       "      <td>B13</td>\n",
       "      <td>8bbde1b26d7ce211705e6f1cc808d51dcacf5b95</td>\n",
       "      <td>32.243516</td>\n",
       "      <td>-97.014395</td>\n",
       "      <td>1610323130</td>\n",
       "      <td>2021-01-10 17:58:50-06:00</td>\n",
       "      <td>2021-01-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215923</th>\n",
       "      <td>A13</td>\n",
       "      <td>8bbde1b26d7ce211705e6f1cc808d51dcacf5b95</td>\n",
       "      <td>32.250849</td>\n",
       "      <td>-97.018124</td>\n",
       "      <td>1610323175</td>\n",
       "      <td>2021-01-10 17:59:35-06:00</td>\n",
       "      <td>2021-01-10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>215924 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Polygon_ID                                 Device_ID        Lat  \\\n",
       "0              P2  9a220574a893533efb3773aa3ca3282ba5834775  32.166847   \n",
       "1              P2  9a220574a893533efb3773aa3ca3282ba5834775  32.166851   \n",
       "2              P2  9a220574a893533efb3773aa3ca3282ba5834775  32.166850   \n",
       "3              P2  9a220574a893533efb3773aa3ca3282ba5834775  32.166862   \n",
       "4              P2  9a220574a893533efb3773aa3ca3282ba5834775  32.166862   \n",
       "...           ...                                       ...        ...   \n",
       "215919        E14  8bbde1b26d7ce211705e6f1cc808d51dcacf5b95  32.227679   \n",
       "215920        E14  8bbde1b26d7ce211705e6f1cc808d51dcacf5b95  32.229966   \n",
       "215921        D14  8bbde1b26d7ce211705e6f1cc808d51dcacf5b95  32.236599   \n",
       "215922        B13  8bbde1b26d7ce211705e6f1cc808d51dcacf5b95  32.243516   \n",
       "215923        A13  8bbde1b26d7ce211705e6f1cc808d51dcacf5b95  32.250849   \n",
       "\n",
       "              Lon Unix Timestamp                  DateTime        Date  \n",
       "0      -97.080640     1609459264 2020-12-31 18:01:04-06:00  2020-12-31  \n",
       "1      -97.080673     1609460462 2020-12-31 18:21:02-06:00  2020-12-31  \n",
       "2      -97.080641     1609461666 2020-12-31 18:41:06-06:00  2020-12-31  \n",
       "3      -97.080650     1609462887 2020-12-31 19:01:27-06:00  2020-12-31  \n",
       "4      -97.080650     1609462888 2020-12-31 19:01:28-06:00  2020-12-31  \n",
       "...           ...            ...                       ...         ...  \n",
       "215919 -97.009669     1610322995 2021-01-10 17:56:35-06:00  2021-01-10  \n",
       "215920 -97.012108     1610323040 2021-01-10 17:57:20-06:00  2021-01-10  \n",
       "215921 -97.010914     1610323085 2021-01-10 17:58:05-06:00  2021-01-10  \n",
       "215922 -97.014395     1610323130 2021-01-10 17:58:50-06:00  2021-01-10  \n",
       "215923 -97.018124     1610323175 2021-01-10 17:59:35-06:00  2021-01-10  \n",
       "\n",
       "[215924 rows x 7 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "convert_timestamp_to_dt(df, tz='US/Central')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "b593fd45-ba17-481a-893b-14e252493990",
   "metadata": {},
   "outputs": [],
   "source": [
    "meta_dict = {\"Polygon_ID\":'object',\"Device_ID\":'object',\"Lat\":'float64',\"Lon\":'float64',\"Unix Timestamp\":'object', 'DateTime':'object','Date':'object'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "4ca2efa3-be8e-4086-bcd4-926987a4cb79",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert Unix timestamps to seconds\n",
    "ddf = ddf.map_partitions(convert_timestamp_to_dt, meta=meta_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "886f16f5-aec9-4b58-af25-9552f2a18025",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop rows between specific hours\n",
    "ddf = ddf.map_partitions(keep_rows_between_hours, start_hour=start_hour, end_hour=end_hour,\n",
    "                        meta=meta_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "c68e4cbe-13f7-4bbd-8b67-69bc2bd7875b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def chunk(s):\n",
    "    # for the comments, assume only a single grouping column, the \n",
    "    # implementation can handle multiple group columns.\n",
    "    #\n",
    "    # s is a grouped series. value_counts creates a multi-series like \n",
    "    # (group, value): count\n",
    "    return s.value_counts()\n",
    "\n",
    "\n",
    "def agg(s):\n",
    "#     print('agg',s.apply(lambda s: s.groupby(level=-1).sum()))\n",
    "    # s is a grouped multi-index series. In .apply the full sub-df will passed\n",
    "    # multi-index and all. Group on the value level and sum the counts. The\n",
    "    # result of the lambda function is a series. Therefore, the result of the \n",
    "    # apply is a multi-index series like (group, value): count\n",
    "    #return s.apply(lambda s: s.groupby(level=-1).sum())\n",
    "\n",
    "    # faster version using pandas internals\n",
    "    s = s._selected_obj\n",
    "    return s.groupby(level=list(range(s.index.nlevels))).sum()\n",
    "\n",
    "\n",
    "def finalize(s):\n",
    "    # s is a multi-index series of the form (group, value): count. First\n",
    "    # manually group on the group part of the index. The lambda will receive a\n",
    "    # sub-series with multi index. Next, drop the group part from the index.\n",
    "    # Finally, determine the index with the maximum value, i.e., the mode.\n",
    "    level = list(range(s.index.nlevels - 1))\n",
    "    return (\n",
    "        s.groupby(level=level)\n",
    "        .apply(lambda s: s.reset_index(level=level, drop=True).idxmax())\n",
    "    )\n",
    "\n",
    "max_occurence = dd.Aggregation('mode', chunk, agg, finalize)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "8cc51d88-ec0c-40d0-bb48-eac990d1b200",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group by timestamp and ID, then aggregate as needed\n",
    "grouped_df = ddf.groupby(['Device_ID','Date']).agg({\n",
    "    'Lat': max_occurence,\n",
    "    'Lon': max_occurence\n",
    "}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "22a57e00-d2a5-4d10-9dd9-703c09c35356",
   "metadata": {},
   "outputs": [],
   "source": [
    "v = grouped_df['Device_ID'].value_counts().compute()\n",
    "grouped_df = grouped_df[grouped_df['Device_ID'].isin(v.index[v.gt(threshold)])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "1384fc37-200e-441b-a6f4-ec7f93ae4968",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group by timestamp and ID, then aggregate as needed\n",
    "grouped_df = grouped_df.groupby(['Device_ID']).agg({\n",
    "    'Lat': max_occurence,\n",
    "    'Lon': max_occurence\n",
    "}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "8e6dfe12-1957-4d81-aca0-83284ba22738",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Processing complete. Processed file saved as 'processed_output.tsv'.\n"
     ]
    }
   ],
   "source": [
    "grouped_df.compute().to_csv(file_name+'_output.tsv', sep='\\t', index=False)#, single_file=True)\n",
    "\n",
    "print(\"Processing complete. Processed file saved as 'processed_output.tsv'.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "fbe396f9-7fa3-4989-ac51-af5899058b1e",
   "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>Device_ID</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0169a31330d568fbd19b0e10dfc7fed970b18647</td>\n",
       "      <td>32.248650</td>\n",
       "      <td>-97.016680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01f7fc93e2a7f945f9796410926791086edd6cab</td>\n",
       "      <td>32.166109</td>\n",
       "      <td>-97.082704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>021cb3cf2220e4439a0ad2144b7c9f4a84556e09</td>\n",
       "      <td>32.226383</td>\n",
       "      <td>-97.071365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>081585eb026761641fddc97ff7e470a14648b218</td>\n",
       "      <td>32.229558</td>\n",
       "      <td>-96.997914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0dc05346cc569d1accce2ed6d42035f6601d7c05</td>\n",
       "      <td>32.210590</td>\n",
       "      <td>-97.082148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>f48953055864d49633ef11e6345951f96645e2e4</td>\n",
       "      <td>32.158283</td>\n",
       "      <td>-97.038029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>f561c29493ecffd0343f341c4e368fd6f68fa08e</td>\n",
       "      <td>32.189232</td>\n",
       "      <td>-97.077105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>faaa4bd8d948d48d7646bc23b2aaf773f2b23a6b</td>\n",
       "      <td>32.182456</td>\n",
       "      <td>-96.984255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>fecf6ce048e98f683d912941397689ae3079cfdc</td>\n",
       "      <td>32.229660</td>\n",
       "      <td>-96.997900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>fefbd65ed48b8fe38c8a588def1ba2d466bd93f7</td>\n",
       "      <td>32.182090</td>\n",
       "      <td>-96.988270</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>93 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   Device_ID        Lat        Lon\n",
       "0   0169a31330d568fbd19b0e10dfc7fed970b18647  32.248650 -97.016680\n",
       "1   01f7fc93e2a7f945f9796410926791086edd6cab  32.166109 -97.082704\n",
       "2   021cb3cf2220e4439a0ad2144b7c9f4a84556e09  32.226383 -97.071365\n",
       "3   081585eb026761641fddc97ff7e470a14648b218  32.229558 -96.997914\n",
       "4   0dc05346cc569d1accce2ed6d42035f6601d7c05  32.210590 -97.082148\n",
       "..                                       ...        ...        ...\n",
       "88  f48953055864d49633ef11e6345951f96645e2e4  32.158283 -97.038029\n",
       "89  f561c29493ecffd0343f341c4e368fd6f68fa08e  32.189232 -97.077105\n",
       "90  faaa4bd8d948d48d7646bc23b2aaf773f2b23a6b  32.182456 -96.984255\n",
       "91  fecf6ce048e98f683d912941397689ae3079cfdc  32.229660 -96.997900\n",
       "92  fefbd65ed48b8fe38c8a588def1ba2d466bd93f7  32.182090 -96.988270\n",
       "\n",
       "[93 rows x 3 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test = pd.read_csv(file_name+'_output.tsv', sep='\\t')\n",
    "test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afeb28f9-21ea-4886-8732-e97570916632",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "fdea8e72-35cc-4bd2-a72a-b2a155832450",
   "metadata": {},
   "outputs": [],
   "source": [
    "def custom_mode(series):\n",
    "    return series.mode().iloc[0] if not series.mode().empty else series.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "477c37a4-f039-4111-9b9f-379f9741a5a8",
   "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>Date</th>\n",
       "      <th>Device_ID</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>0091dec73e15a15f183c43d463fda73694f21a54</td>\n",
       "      <td>32.229470</td>\n",
       "      <td>-96.997918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>00d33e9d6974e69ceee8149b9181674bac8dad14</td>\n",
       "      <td>32.190241</td>\n",
       "      <td>-97.011447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>0169a31330d568fbd19b0e10dfc7fed970b18647</td>\n",
       "      <td>32.248615</td>\n",
       "      <td>-97.016586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>03070cde168148a85ebddbfb6fd0bedb1e8cc9a5</td>\n",
       "      <td>32.192838</td>\n",
       "      <td>-97.019206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>05119d77c268ff91ce14810bed564956cfbbea47</td>\n",
       "      <td>32.153957</td>\n",
       "      <td>-97.083419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5171</th>\n",
       "      <td>2021-01-31</td>\n",
       "      <td>f7e3266a538277dfb881b8322205eccf7bfad4af</td>\n",
       "      <td>32.178496</td>\n",
       "      <td>-97.049001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5172</th>\n",
       "      <td>2021-01-31</td>\n",
       "      <td>f82f05cd861234cbf6f9314fe09323e6964b708a</td>\n",
       "      <td>32.153996</td>\n",
       "      <td>-97.081610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5173</th>\n",
       "      <td>2021-01-31</td>\n",
       "      <td>fa15341e695b7ceb0af5fa877184abea1700393b</td>\n",
       "      <td>32.206200</td>\n",
       "      <td>-97.021057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5174</th>\n",
       "      <td>2021-01-31</td>\n",
       "      <td>fecf6ce048e98f683d912941397689ae3079cfdc</td>\n",
       "      <td>32.229650</td>\n",
       "      <td>-96.997900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5175</th>\n",
       "      <td>2021-01-31</td>\n",
       "      <td>fefbd65ed48b8fe38c8a588def1ba2d466bd93f7</td>\n",
       "      <td>32.182090</td>\n",
       "      <td>-96.988200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5176 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Date                                 Device_ID        Lat  \\\n",
       "0     2020-12-31  0091dec73e15a15f183c43d463fda73694f21a54  32.229470   \n",
       "1     2020-12-31  00d33e9d6974e69ceee8149b9181674bac8dad14  32.190241   \n",
       "2     2020-12-31  0169a31330d568fbd19b0e10dfc7fed970b18647  32.248615   \n",
       "3     2020-12-31  03070cde168148a85ebddbfb6fd0bedb1e8cc9a5  32.192838   \n",
       "4     2020-12-31  05119d77c268ff91ce14810bed564956cfbbea47  32.153957   \n",
       "...          ...                                       ...        ...   \n",
       "5171  2021-01-31  f7e3266a538277dfb881b8322205eccf7bfad4af  32.178496   \n",
       "5172  2021-01-31  f82f05cd861234cbf6f9314fe09323e6964b708a  32.153996   \n",
       "5173  2021-01-31  fa15341e695b7ceb0af5fa877184abea1700393b  32.206200   \n",
       "5174  2021-01-31  fecf6ce048e98f683d912941397689ae3079cfdc  32.229650   \n",
       "5175  2021-01-31  fefbd65ed48b8fe38c8a588def1ba2d466bd93f7  32.182090   \n",
       "\n",
       "            Lon  \n",
       "0    -96.997918  \n",
       "1    -97.011447  \n",
       "2    -97.016586  \n",
       "3    -97.019206  \n",
       "4    -97.083419  \n",
       "...         ...  \n",
       "5171 -97.049001  \n",
       "5172 -97.081610  \n",
       "5173 -97.021057  \n",
       "5174 -96.997900  \n",
       "5175 -96.988200  \n",
       "\n",
       "[5176 rows x 4 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "testgb = df.groupby(['Date', 'Device_ID']).agg({\n",
    "    'Lat': custom_mode,\n",
    "    'Lon': custom_mode\n",
    "}).reset_index()\n",
    "testgb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "ffdf1b9b-7ab5-4ce3-bdbf-570481bb53a4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "341b40f3b21088cb276b73350e307f12ce548be7    32\n",
       "2e5a7143bb24dc1094ffb3a102b232ad8314164e    32\n",
       "9115f80810a344266d027c7f1ef1c79a90437d38    32\n",
       "961b66385f96094ff34c150ddfd377ca0426ae86    32\n",
       "974e1c6765054fe74a522ccc7be433fea8358829    32\n",
       "                                            ..\n",
       "4d5ac2cd7fbd46c633390b6fa36c9084ad912c59     1\n",
       "67f21307eade5671e5839265aecbfbed3306cfec     1\n",
       "689b710db4b7f6a65b8df98ad4f2ce292431037d     1\n",
       "6b5f96395858a33a4bec9dc3c70bdc769c11695c     1\n",
       "f7e3266a538277dfb881b8322205eccf7bfad4af     1\n",
       "Name: Device_ID, Length: 1446, dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "testgb['Device_ID'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "a57452c0-40b1-4d2f-a0f7-5d3d622d2dfe",
   "metadata": {},
   "outputs": [],
   "source": [
    "v = testgb.Device_ID.value_counts()\n",
    "testgbth = testgb[testgb.Device_ID.isin(v.index[v.gt(threshold)])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "ca354246-b706-43c6-896a-f8c2e44a0dfe",
   "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>Device_ID</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0091dec73e15a15f183c43d463fda73694f21a54</td>\n",
       "      <td>32.219490</td>\n",
       "      <td>-97.077060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00d33e9d6974e69ceee8149b9181674bac8dad14</td>\n",
       "      <td>32.186615</td>\n",
       "      <td>-97.036289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0108e1decd9e0843f6d869b7f97de6feb0561852</td>\n",
       "      <td>32.233630</td>\n",
       "      <td>-97.079580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0169a31330d568fbd19b0e10dfc7fed970b18647</td>\n",
       "      <td>32.247693</td>\n",
       "      <td>-97.018151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01924eca355e4f73f8951dfacaf465b6a7c3c278</td>\n",
       "      <td>32.197014</td>\n",
       "      <td>-97.045866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>574</th>\n",
       "      <td>feca7e83d4db189a55035d8be649a37d9a098fc9</td>\n",
       "      <td>32.228107</td>\n",
       "      <td>-97.012096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>575</th>\n",
       "      <td>fecf6ce048e98f683d912941397689ae3079cfdc</td>\n",
       "      <td>32.229660</td>\n",
       "      <td>-96.997900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>576</th>\n",
       "      <td>fefbd65ed48b8fe38c8a588def1ba2d466bd93f7</td>\n",
       "      <td>32.182090</td>\n",
       "      <td>-96.988270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>577</th>\n",
       "      <td>ff2079221c8118d2b7eba027ad59d32194211299</td>\n",
       "      <td>32.188329</td>\n",
       "      <td>-97.019285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>578</th>\n",
       "      <td>fffdc8d6d4cbf86179b762083a4645248741d2f9</td>\n",
       "      <td>32.151194</td>\n",
       "      <td>-97.028597</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>579 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    Device_ID        Lat        Lon\n",
       "0    0091dec73e15a15f183c43d463fda73694f21a54  32.219490 -97.077060\n",
       "1    00d33e9d6974e69ceee8149b9181674bac8dad14  32.186615 -97.036289\n",
       "2    0108e1decd9e0843f6d869b7f97de6feb0561852  32.233630 -97.079580\n",
       "3    0169a31330d568fbd19b0e10dfc7fed970b18647  32.247693 -97.018151\n",
       "4    01924eca355e4f73f8951dfacaf465b6a7c3c278  32.197014 -97.045866\n",
       "..                                        ...        ...        ...\n",
       "574  feca7e83d4db189a55035d8be649a37d9a098fc9  32.228107 -97.012096\n",
       "575  fecf6ce048e98f683d912941397689ae3079cfdc  32.229660 -96.997900\n",
       "576  fefbd65ed48b8fe38c8a588def1ba2d466bd93f7  32.182090 -96.988270\n",
       "577  ff2079221c8118d2b7eba027ad59d32194211299  32.188329 -97.019285\n",
       "578  fffdc8d6d4cbf86179b762083a4645248741d2f9  32.151194 -97.028597\n",
       "\n",
       "[579 rows x 3 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "testgbth.groupby(['Device_ID']).agg({\n",
    "    'Lat': custom_mode,\n",
    "    'Lon': custom_mode\n",
    "}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "127f8083-c74f-4fc5-8fd0-44369ecae252",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}