wl_clean_df.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "82d5d449",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9730d01b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('/home/laptop/Downloads/worldline_notifications.csv',\n",
    "usecols=['Date', 'Action/recurringType', 'ResultDescription', 'Geo', 'Email', 'amount', 'currency'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f89f739d",
   "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>Action/recurringType</th>\n",
       "      <th>ResultDescription</th>\n",
       "      <th>Geo</th>\n",
       "      <th>Email</th>\n",
       "      <th>amount</th>\n",
       "      <th>currency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1/Jun/2021</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Transaction succeeded</td>\n",
       "      <td>UK</td>\n",
       "      <td>andyjplatt@hotmail.co.uk</td>\n",
       "      <td>28.95</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1/Jun/2021</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Transaction succeeded</td>\n",
       "      <td>UK</td>\n",
       "      <td>blackburn.barrie4@gmail.com</td>\n",
       "      <td>28.95</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1/Jun/2021</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Transaction succeeded</td>\n",
       "      <td>UK</td>\n",
       "      <td>paulineastley35@gmail.com</td>\n",
       "      <td>28.95</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1/Jun/2021</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Transaction succeeded</td>\n",
       "      <td>UK</td>\n",
       "      <td>paulineastley35@gmail.com</td>\n",
       "      <td>28.95</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1/Jun/2021</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Transaction succeeded</td>\n",
       "      <td>UK</td>\n",
       "      <td>paulineastley35@gmail.com</td>\n",
       "      <td>28.95</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1/Jun/2021</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Transaction succeeded</td>\n",
       "      <td>UK</td>\n",
       "      <td>k.mackison@yahoo.co.uk</td>\n",
       "      <td>28.95</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1/Jun/2021</td>\n",
       "      <td>REPEATED</td>\n",
       "      <td>transaction declined (amount exceeds credit)</td>\n",
       "      <td>UK</td>\n",
       "      <td>nichol41@hotmail.co.uk</td>\n",
       "      <td>28.95</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1/Jun/2021</td>\n",
       "      <td>REPEATED</td>\n",
       "      <td>Transaction succeeded</td>\n",
       "      <td>UK</td>\n",
       "      <td>oyeyinkas@yahoo.com</td>\n",
       "      <td>28.95</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1/Jun/2021</td>\n",
       "      <td>CREATED</td>\n",
       "      <td>transaction pending</td>\n",
       "      <td>CY</td>\n",
       "      <td>remon.cyprus@gmail.com</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1/Jun/2021</td>\n",
       "      <td>CREATED</td>\n",
       "      <td>Transaction succeeded</td>\n",
       "      <td>CY</td>\n",
       "      <td>remon.cyprus@gmail.com</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date Action/recurringType  \\\n",
       "0  1/Jun/2021                  NaN   \n",
       "1  1/Jun/2021                  NaN   \n",
       "2  1/Jun/2021                  NaN   \n",
       "3  1/Jun/2021                  NaN   \n",
       "4  1/Jun/2021                  NaN   \n",
       "5  1/Jun/2021                  NaN   \n",
       "6  1/Jun/2021             REPEATED   \n",
       "7  1/Jun/2021             REPEATED   \n",
       "8  1/Jun/2021              CREATED   \n",
       "9  1/Jun/2021              CREATED   \n",
       "\n",
       "                              ResultDescription Geo  \\\n",
       "0                         Transaction succeeded  UK   \n",
       "1                         Transaction succeeded  UK   \n",
       "2                         Transaction succeeded  UK   \n",
       "3                         Transaction succeeded  UK   \n",
       "4                         Transaction succeeded  UK   \n",
       "5                         Transaction succeeded  UK   \n",
       "6  transaction declined (amount exceeds credit)  UK   \n",
       "7                         Transaction succeeded  UK   \n",
       "8                           transaction pending  CY   \n",
       "9                         Transaction succeeded  CY   \n",
       "\n",
       "                         Email  amount currency  \n",
       "0     andyjplatt@hotmail.co.uk   28.95      GBP  \n",
       "1  blackburn.barrie4@gmail.com   28.95      GBP  \n",
       "2    paulineastley35@gmail.com   28.95      GBP  \n",
       "3    paulineastley35@gmail.com   28.95      GBP  \n",
       "4    paulineastley35@gmail.com   28.95      GBP  \n",
       "5       k.mackison@yahoo.co.uk   28.95      GBP  \n",
       "6       nichol41@hotmail.co.uk   28.95      GBP  \n",
       "7          oyeyinkas@yahoo.com   28.95      GBP  \n",
       "8       remon.cyprus@gmail.com     NaN      NaN  \n",
       "9       remon.cyprus@gmail.com     NaN      NaN  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "99614883",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Date                    False\n",
       "Action/recurringType     True\n",
       "ResultDescription       False\n",
       "Geo                      True\n",
       "Email                    True\n",
       "amount                   True\n",
       "currency                 True\n",
       "dtype: bool"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().any() ## Where do I have NaaN values?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "39bb91e6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Action/recurringType      706\n",
       "Geo                        61\n",
       "Email                      59\n",
       "amount                  73684\n",
       "currency                73684\n",
       "dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_columns1 = df.columns[df.isnull().any()] ## How many NaaN values do I have in total?\n",
    "df[null_columns1].isnull().sum()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "d5d699e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.dropna() ## drop blanks from df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "1a9d35ef",
   "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>Action/recurringType</th>\n",
       "      <th>ResultDescription</th>\n",
       "      <th>Geo</th>\n",
       "      <th>Email</th>\n",
       "      <th>amount</th>\n",
       "      <th>currency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1/Jun/2021</td>\n",
       "      <td>REPEATED</td>\n",
       "      <td>transaction declined (amount exceeds credit)</td>\n",
       "      <td>UK</td>\n",
       "      <td>nichol41@hotmail.co.uk</td>\n",
       "      <td>28.95</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1/Jun/2021</td>\n",
       "      <td>REPEATED</td>\n",
       "      <td>Transaction succeeded</td>\n",
       "      <td>UK</td>\n",
       "      <td>oyeyinkas@yahoo.com</td>\n",
       "      <td>28.95</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1/Jun/2021</td>\n",
       "      <td>INITIAL</td>\n",
       "      <td>Transaction succeeded</td>\n",
       "      <td>CY</td>\n",
       "      <td>remon.cyprus@gmail.com</td>\n",
       "      <td>1.00</td>\n",
       "      <td>EUR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1/Jun/2021</td>\n",
       "      <td>INITIAL</td>\n",
       "      <td>transaction declined (wrong expiry date)</td>\n",
       "      <td>CY</td>\n",
       "      <td>marios-ano@live.com</td>\n",
       "      <td>1.00</td>\n",
       "      <td>EUR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2/Jun/2021</td>\n",
       "      <td>REPEATED</td>\n",
       "      <td>Transaction succeeded</td>\n",
       "      <td>UK</td>\n",
       "      <td>katiestephenson055@gmail.com</td>\n",
       "      <td>28.95</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2/Jun/2021</td>\n",
       "      <td>REPEATED</td>\n",
       "      <td>transaction declined (amount exceeds credit)</td>\n",
       "      <td>CY</td>\n",
       "      <td>disanayakasamantha29@gmail.com</td>\n",
       "      <td>28.95</td>\n",
       "      <td>EUR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>2/Jun/2021</td>\n",
       "      <td>REPEATED</td>\n",
       "      <td>transaction declined by authorization system</td>\n",
       "      <td>UK</td>\n",
       "      <td>f.burton@simplybiz.co.uk</td>\n",
       "      <td>28.95</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2/Jun/2021</td>\n",
       "      <td>REPEATED</td>\n",
       "      <td>Transaction succeeded</td>\n",
       "      <td>UK</td>\n",
       "      <td>c-wing@sky.com</td>\n",
       "      <td>28.95</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>3/Jun/2021</td>\n",
       "      <td>REPEATED</td>\n",
       "      <td>transaction declined (amount exceeds credit)</td>\n",
       "      <td>UK</td>\n",
       "      <td>smithpatricia784@gmail.com</td>\n",
       "      <td>28.95</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>3/Jun/2021</td>\n",
       "      <td>REPEATED</td>\n",
       "      <td>Transaction succeeded</td>\n",
       "      <td>UK</td>\n",
       "      <td>bigga_baby@yahoo.co.uk</td>\n",
       "      <td>28.95</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date Action/recurringType  \\\n",
       "6   1/Jun/2021             REPEATED   \n",
       "7   1/Jun/2021             REPEATED   \n",
       "15  1/Jun/2021              INITIAL   \n",
       "26  1/Jun/2021              INITIAL   \n",
       "30  2/Jun/2021             REPEATED   \n",
       "31  2/Jun/2021             REPEATED   \n",
       "32  2/Jun/2021             REPEATED   \n",
       "33  2/Jun/2021             REPEATED   \n",
       "59  3/Jun/2021             REPEATED   \n",
       "60  3/Jun/2021             REPEATED   \n",
       "\n",
       "                               ResultDescription Geo  \\\n",
       "6   transaction declined (amount exceeds credit)  UK   \n",
       "7                          Transaction succeeded  UK   \n",
       "15                         Transaction succeeded  CY   \n",
       "26      transaction declined (wrong expiry date)  CY   \n",
       "30                         Transaction succeeded  UK   \n",
       "31  transaction declined (amount exceeds credit)  CY   \n",
       "32  transaction declined by authorization system  UK   \n",
       "33                         Transaction succeeded  UK   \n",
       "59  transaction declined (amount exceeds credit)  UK   \n",
       "60                         Transaction succeeded  UK   \n",
       "\n",
       "                             Email  amount currency  \n",
       "6           nichol41@hotmail.co.uk   28.95      GBP  \n",
       "7              oyeyinkas@yahoo.com   28.95      GBP  \n",
       "15          remon.cyprus@gmail.com    1.00      EUR  \n",
       "26             marios-ano@live.com    1.00      EUR  \n",
       "30    katiestephenson055@gmail.com   28.95      GBP  \n",
       "31  disanayakasamantha29@gmail.com   28.95      EUR  \n",
       "32        f.burton@simplybiz.co.uk   28.95      GBP  \n",
       "33                  c-wing@sky.com   28.95      GBP  \n",
       "59      smithpatricia784@gmail.com   28.95      GBP  \n",
       "60          bigga_baby@yahoo.co.uk   28.95      GBP  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "07945233",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(28699, 7)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ae2f2414",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Date'] = pd.to_datetime(df.Date) ## create official timestamp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "3b35af37",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.loc[(df['Date'] >= '2021-07-01')] ## start df from July 21"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "0189860d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4000      2021-07-01\n",
       "4001      2021-07-01\n",
       "4002      2021-07-01\n",
       "4003      2021-07-01\n",
       "4004      2021-07-01\n",
       "             ...    \n",
       "103032    2022-02-21\n",
       "103033    2022-02-21\n",
       "103034    2022-02-21\n",
       "103035    2022-02-21\n",
       "103036    2022-02-21\n",
       "Name: Date, Length: 19718, dtype: object"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Date'].dt.date ## omit time from Date index, keep only date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "72a2f830",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['REPEATED']"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Action/recurringType'].unique().tolist() ## show unique values under Action/RecurringType"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "0e444ee9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[(df['Action/recurringType'] != 'INITIAL')] ## ommit initial"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "eca69acb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['REPEATED']"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Action/recurringType'].unique().tolist() ## check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "b3f46ef4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel('/home/laptop/Downloads/worldline_notifications_filtered.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1bb7829b",
   "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}