Data2021.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "\n",
    "file = 'C:\\\\Users\\\\cml\\\\Downloads\\\\AIR export\\\\Aalborg\\\\Hjælpemidler minus 50_Rasmus_Details-CPR.xlsx'\n",
    "df = pd.read_excel(file, engine='openpyxl', converters={'ID': str, 'Kategori ISO nummer': str})\n",
    "\n",
    "df['Seq'] = df.groupby(['ID', 'Kategori ISO nummer']).cumcount()\n",
    "df = df[['ID', 'Birth Year', 'Gender', 'Kategori ISO nummer', 'Kørselsdato', 'Seq']]\n",
    "\n",
    "df['LendDate'] = df.apply(lambda x: x['Kørselsdato'] if x['Seq'] % 2 == 0 else pd.NaT, axis=1)\n",
    "df['ReturnDate'] = df.apply(lambda x: x['Kørselsdato'] if x['Seq'] % 2 == 1 else pd.NaT, axis=1)\n",
    "\n",
    "df['ReturnDate'] = df.groupby(['ID', 'Kategori ISO nummer'])['ReturnDate'].shift(-1)\n",
    "df = df.dropna(subset=['LendDate', 'ReturnDate'], thresh=1)\n",
    "\n",
    "#df['ReturnDate'] = df['ReturnDate'].shift(-1)\n",
    "#df = df.dropna(subset=['LendDate', 'ReturnDate'], thresh=1)\n",
    "#df = df.drop(['Kørselsdato', 'Seq'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>ID</th>\n",
       "      <th>Birth Year</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Kategori ISO nummer</th>\n",
       "      <th>Kørselsdato</th>\n",
       "      <th>Seq</th>\n",
       "      <th>LendDate</th>\n",
       "      <th>ReturnDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2429541786</td>\n",
       "      <td>23</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>22271812</td>\n",
       "      <td>06/08/19</td>\n",
       "      <td>0</td>\n",
       "      <td>06/08/19</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2429541786</td>\n",
       "      <td>23</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>12060611</td>\n",
       "      <td>19/02/18</td>\n",
       "      <td>0</td>\n",
       "      <td>19/02/18</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2429541786</td>\n",
       "      <td>23</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>12072401</td>\n",
       "      <td>19/02/18</td>\n",
       "      <td>0</td>\n",
       "      <td>19/02/18</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2430269034</td>\n",
       "      <td>26</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>22271812</td>\n",
       "      <td>09/03/20</td>\n",
       "      <td>0</td>\n",
       "      <td>09/03/20</td>\n",
       "      <td>09/11/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2430269034</td>\n",
       "      <td>26</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>12362124</td>\n",
       "      <td>14/10/19</td>\n",
       "      <td>0</td>\n",
       "      <td>14/10/19</td>\n",
       "      <td>29/07/21</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72044</th>\n",
       "      <td>74711770410</td>\n",
       "      <td>99</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>18301509</td>\n",
       "      <td>13/06/19</td>\n",
       "      <td>0</td>\n",
       "      <td>13/06/19</td>\n",
       "      <td>07/09/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72046</th>\n",
       "      <td>74711770410</td>\n",
       "      <td>99</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>12220308</td>\n",
       "      <td>28/09/21</td>\n",
       "      <td>0</td>\n",
       "      <td>28/09/21</td>\n",
       "      <td>07/09/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72048</th>\n",
       "      <td>74711770410</td>\n",
       "      <td>99</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>12220308</td>\n",
       "      <td>25/08/16</td>\n",
       "      <td>2</td>\n",
       "      <td>25/08/16</td>\n",
       "      <td>29/09/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72050</th>\n",
       "      <td>74711770410</td>\n",
       "      <td>99</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>04330301</td>\n",
       "      <td>07/09/21</td>\n",
       "      <td>0</td>\n",
       "      <td>07/09/21</td>\n",
       "      <td>28/09/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72051</th>\n",
       "      <td>74711770410</td>\n",
       "      <td>99</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>99999999</td>\n",
       "      <td>28/09/21</td>\n",
       "      <td>0</td>\n",
       "      <td>28/09/21</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>48292 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                ID  Birth Year  Gender Kategori ISO nummer Kørselsdato  Seq  \\\n",
       "0       2429541786          23  FEMALE            22271812    06/08/19    0   \n",
       "1       2429541786          23  FEMALE            12060611    19/02/18    0   \n",
       "2       2429541786          23  FEMALE            12072401    19/02/18    0   \n",
       "3       2430269034          26  FEMALE            22271812    09/03/20    0   \n",
       "5       2430269034          26  FEMALE            12362124    14/10/19    0   \n",
       "...            ...         ...     ...                 ...         ...  ...   \n",
       "72044  74711770410          99  FEMALE            18301509    13/06/19    0   \n",
       "72046  74711770410          99  FEMALE            12220308    28/09/21    0   \n",
       "72048  74711770410          99  FEMALE            12220308    25/08/16    2   \n",
       "72050  74711770410          99  FEMALE            04330301    07/09/21    0   \n",
       "72051  74711770410          99  FEMALE            99999999    28/09/21    0   \n",
       "\n",
       "       LendDate ReturnDate  \n",
       "0      06/08/19        NaN  \n",
       "1      19/02/18        NaN  \n",
       "2      19/02/18        NaN  \n",
       "3      09/03/20   09/11/20  \n",
       "5      14/10/19   29/07/21  \n",
       "...         ...        ...  \n",
       "72044  13/06/19   07/09/21  \n",
       "72046  28/09/21   07/09/21  \n",
       "72048  25/08/16   29/09/21  \n",
       "72050  07/09/21   28/09/21  \n",
       "72051  28/09/21        NaN  \n",
       "\n",
       "[48292 rows x 8 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "59ff6fbb0321898508cf6243593820bf2585fcfb6693fd00e85ec94ed8847fd0"
  },
  "kernelspec": {
   "display_name": "Python 3.8.8 64-bit ('py38-air': conda)",
   "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.8.8"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}