Remit Money Dataset.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": [
    "df = pd.read_excel(r'C:\\Users\\srika\\Documents\\Python Scripts\\data.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Column1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jack</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3,000.00 CAD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>168,990.00 INR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PENDING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SENT ON AUG 17, 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Jack</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>400.00 CAD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>22,140.00 INR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>DELIVERED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>SENT ON JUL 22, 2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Column1\n",
       "0                  Jack\n",
       "1          3,000.00 CAD\n",
       "2        168,990.00 INR\n",
       "3               PENDING\n",
       "4  SENT ON AUG 17, 2020\n",
       "5                  Jack\n",
       "6            400.00 CAD\n",
       "7         22,140.00 INR\n",
       "8             DELIVERED\n",
       "9  SENT ON JUL 22, 2020"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(n=10)"
   ]
  },
  {
   "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>Column1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>Jack</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>978.00 CAD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>52,047.90 INR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>DELIVERED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>SENT ON APR 25, 2018</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Column1\n",
       "120                  Jack\n",
       "121            978.00 CAD\n",
       "122         52,047.90 INR\n",
       "123             DELIVERED\n",
       "124  SENT ON APR 25, 2018"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.DataFrame(columns=['Name','AmountCAD','AmountINR','Status','DateSent'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Name</th>\n",
       "      <th>AmountCAD</th>\n",
       "      <th>AmountINR</th>\n",
       "      <th>Status</th>\n",
       "      <th>DateSent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Name, AmountCAD, AmountINR, Status, DateSent]\n",
       "Index: []"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://numpy.org/doc/stable/reference/generated/numpy.reshape.html\n",
    "df3 = pd.DataFrame(np.reshape(df.values,(25,5)), columns=['Name','AmountCAD','AmountINR','Status','DateSent'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Name</th>\n",
       "      <th>AmountCAD</th>\n",
       "      <th>AmountINR</th>\n",
       "      <th>Status</th>\n",
       "      <th>DateSent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jack</td>\n",
       "      <td>3,000.00 CAD</td>\n",
       "      <td>168,990.00 INR</td>\n",
       "      <td>PENDING</td>\n",
       "      <td>SENT ON AUG 17, 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jack</td>\n",
       "      <td>400.00 CAD</td>\n",
       "      <td>22,140.00 INR</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>SENT ON JUL 22, 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jack</td>\n",
       "      <td>2,590.00 CAD</td>\n",
       "      <td>139,412.00 INR</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>SENT ON APR 25, 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jack</td>\n",
       "      <td>600.00 CAD</td>\n",
       "      <td>30,450.00 INR</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>SENT ON MAR 18, 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jack</td>\n",
       "      <td>500.00 CAD</td>\n",
       "      <td>27,040.00 INR</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>SENT ON JAN 25, 2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Name     AmountCAD       AmountINR     Status              DateSent\n",
       "0  Jack  3,000.00 CAD  168,990.00 INR    PENDING  SENT ON AUG 17, 2020\n",
       "1  Jack    400.00 CAD   22,140.00 INR  DELIVERED  SENT ON JUL 22, 2020\n",
       "2  Jack  2,590.00 CAD  139,412.00 INR  DELIVERED  SENT ON APR 25, 2020\n",
       "3  Jack    600.00 CAD   30,450.00 INR  DELIVERED  SENT ON MAR 18, 2020\n",
       "4  Jack    500.00 CAD   27,040.00 INR  DELIVERED  SENT ON JAN 25, 2020"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.str.split.html\n",
    "new  = df3['AmountCAD'].str.split(\".\", n = 1, expand = True) \n",
    "new2  = df3['AmountINR'].str.split(\".\", n = 1, expand = True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3['AmountCAD'] = new[0]\n",
    "df3['AmountINR'] = new2[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Name</th>\n",
       "      <th>AmountCAD</th>\n",
       "      <th>AmountINR</th>\n",
       "      <th>Status</th>\n",
       "      <th>DateSent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jack</td>\n",
       "      <td>3,000</td>\n",
       "      <td>168,990</td>\n",
       "      <td>PENDING</td>\n",
       "      <td>SENT ON AUG 17, 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jack</td>\n",
       "      <td>400</td>\n",
       "      <td>22,140</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>SENT ON JUL 22, 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jack</td>\n",
       "      <td>2,590</td>\n",
       "      <td>139,412</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>SENT ON APR 25, 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jack</td>\n",
       "      <td>600</td>\n",
       "      <td>30,450</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>SENT ON MAR 18, 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jack</td>\n",
       "      <td>500</td>\n",
       "      <td>27,040</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>SENT ON JAN 25, 2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Name AmountCAD AmountINR     Status              DateSent\n",
       "0  Jack     3,000   168,990    PENDING  SENT ON AUG 17, 2020\n",
       "1  Jack       400    22,140  DELIVERED  SENT ON JUL 22, 2020\n",
       "2  Jack     2,590   139,412  DELIVERED  SENT ON APR 25, 2020\n",
       "3  Jack       600    30,450  DELIVERED  SENT ON MAR 18, 2020\n",
       "4  Jack       500    27,040  DELIVERED  SENT ON JAN 25, 2020"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.str.replace.html\n",
    "df3['AmountCAD'] = df3['AmountCAD'].str.replace(',', '').astype(int)\n",
    "df3['AmountINR'] = df3['AmountINR'].str.replace(',', '').astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>Name</th>\n",
       "      <th>AmountCAD</th>\n",
       "      <th>AmountINR</th>\n",
       "      <th>Status</th>\n",
       "      <th>DateSent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jack</td>\n",
       "      <td>3000</td>\n",
       "      <td>168990</td>\n",
       "      <td>PENDING</td>\n",
       "      <td>SENT ON AUG 17, 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jack</td>\n",
       "      <td>400</td>\n",
       "      <td>22140</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>SENT ON JUL 22, 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jack</td>\n",
       "      <td>2590</td>\n",
       "      <td>139412</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>SENT ON APR 25, 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jack</td>\n",
       "      <td>600</td>\n",
       "      <td>30450</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>SENT ON MAR 18, 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jack</td>\n",
       "      <td>500</td>\n",
       "      <td>27040</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>SENT ON JAN 25, 2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Name  AmountCAD  AmountINR     Status              DateSent\n",
       "0  Jack       3000     168990    PENDING  SENT ON AUG 17, 2020\n",
       "1  Jack        400      22140  DELIVERED  SENT ON JUL 22, 2020\n",
       "2  Jack       2590     139412  DELIVERED  SENT ON APR 25, 2020\n",
       "3  Jack        600      30450  DELIVERED  SENT ON MAR 18, 2020\n",
       "4  Jack        500      27040  DELIVERED  SENT ON JAN 25, 2020"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>AmountCAD</th>\n",
       "      <th>AmountINR</th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Jack</th>\n",
       "      <td>34850</td>\n",
       "      <td>1874865</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jill</th>\n",
       "      <td>6916</td>\n",
       "      <td>359759</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      AmountCAD  AmountINR  Name\n",
       "Name                            \n",
       "Jack      34850    1874865    22\n",
       "Jill       6916     359759     3"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.groupby(\n",
    "   ['Name']\n",
    ").agg(\n",
    "    {\n",
    "         'AmountCAD':sum,  \n",
    "         'AmountINR':sum, \n",
    "         'Name': \"count\"\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "AmountCAD      41766\n",
       "AmountINR    2234624\n",
       "Name              25\n",
       "dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.agg(\n",
    "    {\n",
    "         'AmountCAD':sum,  \n",
    "         'AmountINR':sum, \n",
    "         'Name': \"count\"\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "new3  = df3['DateSent'].str.split(\"ON \", n = 1, expand = True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3['DateSent'] = new3[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>Name</th>\n",
       "      <th>AmountCAD</th>\n",
       "      <th>AmountINR</th>\n",
       "      <th>Status</th>\n",
       "      <th>DateSent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jack</td>\n",
       "      <td>3000</td>\n",
       "      <td>168990</td>\n",
       "      <td>PENDING</td>\n",
       "      <td>AUG 17, 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jack</td>\n",
       "      <td>400</td>\n",
       "      <td>22140</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>JUL 22, 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jack</td>\n",
       "      <td>2590</td>\n",
       "      <td>139412</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>APR 25, 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jack</td>\n",
       "      <td>600</td>\n",
       "      <td>30450</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>MAR 18, 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jack</td>\n",
       "      <td>500</td>\n",
       "      <td>27040</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>JAN 25, 2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Name  AmountCAD  AmountINR     Status      DateSent\n",
       "0  Jack       3000     168990    PENDING  AUG 17, 2020\n",
       "1  Jack        400      22140  DELIVERED  JUL 22, 2020\n",
       "2  Jack       2590     139412  DELIVERED  APR 25, 2020\n",
       "3  Jack        600      30450  DELIVERED  MAR 18, 2020\n",
       "4  Jack        500      27040  DELIVERED  JAN 25, 2020"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Name         object\n",
       "AmountCAD     int32\n",
       "AmountINR     int32\n",
       "Status       object\n",
       "DateSent     object\n",
       "dtype: object"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "new4 = df3['DateSent'].str.split(\" \", n = 1, expand = True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "new5 = new4[1].str.split(\",\", n = 1, expand = True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3['Month'] = new4[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3['Day'] = new5[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3['Year'] = new5[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>Name</th>\n",
       "      <th>AmountCAD</th>\n",
       "      <th>AmountINR</th>\n",
       "      <th>Status</th>\n",
       "      <th>DateSent</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jack</td>\n",
       "      <td>3000</td>\n",
       "      <td>168990</td>\n",
       "      <td>PENDING</td>\n",
       "      <td>AUG 17, 2020</td>\n",
       "      <td>AUG</td>\n",
       "      <td>17</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jack</td>\n",
       "      <td>400</td>\n",
       "      <td>22140</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>JUL 22, 2020</td>\n",
       "      <td>JUL</td>\n",
       "      <td>22</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jack</td>\n",
       "      <td>2590</td>\n",
       "      <td>139412</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>APR 25, 2020</td>\n",
       "      <td>APR</td>\n",
       "      <td>25</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jack</td>\n",
       "      <td>600</td>\n",
       "      <td>30450</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>MAR 18, 2020</td>\n",
       "      <td>MAR</td>\n",
       "      <td>18</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jack</td>\n",
       "      <td>500</td>\n",
       "      <td>27040</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>JAN 25, 2020</td>\n",
       "      <td>JAN</td>\n",
       "      <td>25</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Name  AmountCAD  AmountINR     Status      DateSent Month Day   Year\n",
       "0  Jack       3000     168990    PENDING  AUG 17, 2020   AUG  17   2020\n",
       "1  Jack        400      22140  DELIVERED  JUL 22, 2020   JUL  22   2020\n",
       "2  Jack       2590     139412  DELIVERED  APR 25, 2020   APR  25   2020\n",
       "3  Jack        600      30450  DELIVERED  MAR 18, 2020   MAR  18   2020\n",
       "4  Jack        500      27040  DELIVERED  JAN 25, 2020   JAN  25   2020"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "d = {\n",
    "            'JAN' : 1,\n",
    "            'FEB' : 2,\n",
    "            'MAR' : 3,\n",
    "            'APR' : 4,\n",
    "            'MAY' : 5,\n",
    "            'JUN' : 6,\n",
    "            'JUL' : 7,\n",
    "            'AUG' : 8,\n",
    "            'SEP' : 9, \n",
    "            'OCT' : 10,\n",
    "            'NOV' : 11,\n",
    "            'DEC' : 12\n",
    "    }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.Month = df3.Month.map(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>Name</th>\n",
       "      <th>AmountCAD</th>\n",
       "      <th>AmountINR</th>\n",
       "      <th>Status</th>\n",
       "      <th>DateSent</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jack</td>\n",
       "      <td>3000</td>\n",
       "      <td>168990</td>\n",
       "      <td>PENDING</td>\n",
       "      <td>AUG 17, 2020</td>\n",
       "      <td>8</td>\n",
       "      <td>17</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jack</td>\n",
       "      <td>400</td>\n",
       "      <td>22140</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>JUL 22, 2020</td>\n",
       "      <td>7</td>\n",
       "      <td>22</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jack</td>\n",
       "      <td>2590</td>\n",
       "      <td>139412</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>APR 25, 2020</td>\n",
       "      <td>4</td>\n",
       "      <td>25</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jack</td>\n",
       "      <td>600</td>\n",
       "      <td>30450</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>MAR 18, 2020</td>\n",
       "      <td>3</td>\n",
       "      <td>18</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jack</td>\n",
       "      <td>500</td>\n",
       "      <td>27040</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>JAN 25, 2020</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Name  AmountCAD  AmountINR     Status      DateSent  Month Day   Year\n",
       "0  Jack       3000     168990    PENDING  AUG 17, 2020      8  17   2020\n",
       "1  Jack        400      22140  DELIVERED  JUL 22, 2020      7  22   2020\n",
       "2  Jack       2590     139412  DELIVERED  APR 25, 2020      4  25   2020\n",
       "3  Jack        600      30450  DELIVERED  MAR 18, 2020      3  18   2020\n",
       "4  Jack        500      27040  DELIVERED  JAN 25, 2020      1  25   2020"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3['Date'] = pd.to_datetime(df3[['Year','Month','Day']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>Name</th>\n",
       "      <th>AmountCAD</th>\n",
       "      <th>AmountINR</th>\n",
       "      <th>Status</th>\n",
       "      <th>DateSent</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Year</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jack</td>\n",
       "      <td>3000</td>\n",
       "      <td>168990</td>\n",
       "      <td>PENDING</td>\n",
       "      <td>AUG 17, 2020</td>\n",
       "      <td>8</td>\n",
       "      <td>17</td>\n",
       "      <td>2020</td>\n",
       "      <td>2020-08-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jack</td>\n",
       "      <td>400</td>\n",
       "      <td>22140</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>JUL 22, 2020</td>\n",
       "      <td>7</td>\n",
       "      <td>22</td>\n",
       "      <td>2020</td>\n",
       "      <td>2020-07-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jack</td>\n",
       "      <td>2590</td>\n",
       "      <td>139412</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>APR 25, 2020</td>\n",
       "      <td>4</td>\n",
       "      <td>25</td>\n",
       "      <td>2020</td>\n",
       "      <td>2020-04-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jack</td>\n",
       "      <td>600</td>\n",
       "      <td>30450</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>MAR 18, 2020</td>\n",
       "      <td>3</td>\n",
       "      <td>18</td>\n",
       "      <td>2020</td>\n",
       "      <td>2020-03-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jack</td>\n",
       "      <td>500</td>\n",
       "      <td>27040</td>\n",
       "      <td>DELIVERED</td>\n",
       "      <td>JAN 25, 2020</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>2020</td>\n",
       "      <td>2020-01-25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Name  AmountCAD  AmountINR     Status      DateSent  Month Day   Year  \\\n",
       "0  Jack       3000     168990    PENDING  AUG 17, 2020      8  17   2020   \n",
       "1  Jack        400      22140  DELIVERED  JUL 22, 2020      7  22   2020   \n",
       "2  Jack       2590     139412  DELIVERED  APR 25, 2020      4  25   2020   \n",
       "3  Jack        600      30450  DELIVERED  MAR 18, 2020      3  18   2020   \n",
       "4  Jack        500      27040  DELIVERED  JAN 25, 2020      1  25   2020   \n",
       "\n",
       "        Date  \n",
       "0 2020-08-17  \n",
       "1 2020-07-22  \n",
       "2 2020-04-25  \n",
       "3 2020-03-18  \n",
       "4 2020-01-25  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head()"
   ]
  },
  {
   "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}