A. Task_Pendency_Report.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem Statement\n",
    "Create Team wise Task Pendency report, share the summary in automailers and update the same on googlesheet through API"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Preprocessing - \n",
    "1.Read all files  \n",
    "2.Clean up data to match column names as data types  \n",
    "3.Replace null values with 0 or 'NA' depending on the datatype  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\300024402\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3058: DtypeWarning: Columns (9,10) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n",
      "C:\\Users\\300024402\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:42: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=False'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass 'sort=True'.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "Forward_3PL=pd.read_excel(r'Report_1.xlsx')\n",
    "MarketPlace=pd.read_excel(r'Report_2.xlsx')\n",
    "Transport_ML=pd.read_excel(r'Report_3.xlsx')\n",
    "Forward_ML=pd.read_excel(r'Report_4.xlsx')\n",
    "Non_returnable_Tasks=pd.read_excel(r'Report_5.xlsx')\n",
    "Pickup_3PL=pd.read_excel(r'Report_6.xlsx')\n",
    "Pickup_ML=pd.read_excel(r'Report_7.xlsx')\n",
    "Master_3PL=pd.read_excel(r'Report_8.xlsx')\n",
    "Master_ML=pd.read_excel(r'Report_9.xlsx')\n",
    "Task_Others=pd.read_excel(r'Report_10.xlsx')\n",
    "taskdetails=pd.read_csv(r'Report_11.gz')\n",
    "Warehouse=pd.read_excel(r'Report-12.xlsx')\n",
    "\n",
    "\n",
    "# In[2]:\n",
    "\n",
    "Pickup_3PL.rename(columns = {'Category':'Category Level 1','Order Number':'Order_ID','Created Date':'Task Created Date','Return ID':'Entity_Id'}, inplace = True) \n",
    "Pickup_ML.rename(columns = {'Category':'Category Level 1','Order Number':'Order_ID','Created Date':'Task Created Date','Return ID':'Entity_Id'}, inplace = True) \n",
    "Transport_ML.rename(columns = {'Order ID':'Order_ID','Packet Id':'Entity_Id'}, inplace = True) \n",
    "Forward_ML.rename(columns = {'Order ID':'Order_ID','Packet Id':'Entity_Id'}, inplace = True) \n",
    "Forward_3PL.rename(columns = {'Order ID':'Order_ID','Packet Id':'Entity_Id'}, inplace = True) \n",
    "Task_Others.rename(columns = {'Created Date':'Task Created Date','Task Id':'Task ID'}, inplace = True) \n",
    "Master_3PL.rename(columns = {'Created Date':'Task Created Date','ReleaseId':'Entity_Id','Task Id':'Task ID'}, inplace = True) \n",
    "Master_ML.rename(columns = {'Created Date':'Task Created Date','ReleaseId':'Entity_Id','Task Id':'Task ID'}, inplace = True) \n",
    "Non_returnable_Tasks.rename(columns = {'Created Date':'Task Created Date','Task Id':'Task ID'}, inplace = True) \n",
    "MarketPlace.rename(columns = {'Date Created':'Task Created Date','Order ID':'Entity_Id'}, inplace = True) \n",
    "Warehouse.rename(columns = {'Created Date':'Task Created Date','Order ID':'Order_ID','ReleaseId':'Entity_Id','Task Category 3':'Category Level 3','Category':'Category Level 1','Sub Category':'Category Level 2'}, inplace = True) \n",
    "\n",
    "\n",
    "# In[3]:\n",
    "\n",
    "ar=['MarketPlace','ML','WH and Logistics','RETURN','Pick-Up','Dispute','3PL']\n",
    "\n",
    "\n",
    "# In[4]:\n",
    "\n",
    "temp=[Task_Others[~((Task_Others['Category Level 3'].isin(['Not Packed','Not Packed_WH']))& (Task_Others['Task Status'].isin(['Information Provided','Open','Re-opened'])))],\n",
    "      Non_returnable_Tasks]\n",
    "Non_Others=pd.concat(temp)\n",
    "\n",
    "\n",
    "MarketPlace.loc[~MarketPlace['Category Level 3'].isnull(),'Order_ID']=MarketPlace['Entity_Id']\n",
    "MarketPlace.loc[~MarketPlace['Category Level 3'].isnull(),'SH']='MarketPlace_Not_Packed'\n",
    "Warehouse.loc[~Warehouse['Category Level 3'].isnull(),'SH']='Warehouse'\n",
    "Warehouse.loc[~Warehouse['Category Level 3'].isnull(),'Tracking Number']='Not Applicable'\n",
    "MarketPlace.loc[~MarketPlace['Category Level 3'].isnull(),'Tracking Number']='Not Applicable'\n",
    "Warehouse['Order_ID']=Warehouse['Order_ID'].astype(str)\n",
    "\n",
    "Warehouse['Entity_Id']=Warehouse['Entity_Id'].astype(str)\n",
    "\n",
    "col=['SH','Category Level 1','Category Level 2','Category Level 3','Order_ID','Task ID'\n",
    "         ,'Task Status','Tenant ID','Tracking Number','Entity_Id','Task Created Date']\n",
    "\n",
    "Pre_packed=pd.concat([MarketPlace[MarketPlace['Category Level 3'].isin(['Not Packed','Not Packed_WH'])][col],Warehouse[Warehouse['Category Level 3'].isin(['Not Packed','Not Packed_WH'])][col]])\n",
    "Pre_packed['Order_ID']=Pre_packed['Order_ID'].astype(str)\n",
    "\n",
    "Non_Others.loc[~(Non_Others['Category Level 2'].isnull()),'Entity_Id'] = 0\n",
    "Non_Others.loc[~(Non_Others['Category Level 2'].isnull()),'SH'] = 'Others'\n",
    "\n",
    "Non_Others.loc[~(Non_Others['Category Level 2'].isnull()),'Entity_Id'] = Non_Others['ReleaseId']\n",
    "Non_Others.loc[(Non_Others['Category Level 2'].isin(['Non-returnable products'])),'SH'] = 'Non-Returnable'\n",
    "\n",
    "Non_Others.loc[(Non_Others['Category Level 2'].isin(['WIP/Packed more than 24 hours'])),'Category Level 3'] = 'WIP/Packed more than 24 hours'\n",
    "\n",
    "Transport_ML.loc[~(Transport_ML['Category Level 3'].isnull()),'Category Level 1'] = 'ML'\n",
    "Transport_ML.loc[(Transport_ML['Category Level 2'].isin(['WIP/Packed more than 24 hours'])),'Category Level 1'] = 'WH and Logistics'\n",
    "\n",
    "Transport_ML.loc[(Transport_ML['Category Level 3'].isin(['WIP/Packed more than 72 hours'])),'Category Level 1'] = 'MarketPlace'\n",
    "Transport_ML.loc[~(Transport_ML['Category Level 2'].isnull()),'SH'] = 'ML_Transport'\n",
    "Transport_ML.loc[(Transport_ML['Category Level 2'].isin(['WIP/Packed more than 24 hours'])),'Category Level 3'] = 'WIP/Packed more than 24 hours'\n",
    "\n",
    "\n",
    "# In[6]:\n",
    "\n",
    "\n",
    "Forward_ML.loc[~(Forward_ML['Category Level 3'].isnull()),'Category Level 1'] = 'ML'\n",
    "Forward_ML.loc[(Forward_ML['Category Level 2'].isin(['Reinvestigation Request'])),'Category Level 1'] = 'Dispute'\n",
    "Forward_ML.loc[~(Forward_ML['Category Level 3'].isnull()),'SH'] = 'ML_Forward'\n",
    "\n",
    "\n",
    "# In[8]:\n",
    "\n",
    "\n",
    "Pickup_3PL.loc[(Pickup_3PL['Category Level 3'].isin(['Delayed more than 3 Business days'])),'Category Level 2'] = 'Delayed'\n",
    "Pickup_3PL.loc[(Pickup_3PL['Category Level 3'].isin(['Pick Up Approvals'])),'Category Level 2'] = 'Approvals'\n",
    "Pickup_3PL.loc[(Pickup_3PL['Category Level 3'].isin(['Disputing pick up attempt'])),'Category Level 2'] = 'Disputed pick up'\n",
    "Pickup_3PL.loc[(Pickup_3PL['Category Level 3'].isin(['Picked up not delivered to WH'])),'Category Level 2'] = 'Picked Up Refund Pending'\n",
    "Pickup_3PL.loc[(Pickup_3PL['Category Level 3'].isin(['Exchange Approvals'])),'Category Level 2'] = 'Approvals'\n",
    "Pickup_3PL.loc[~(Pickup_3PL['Category Level 3'].isnull()),'Category Level 1'] = 'Pick-Up'\n",
    "Pickup_3PL.loc[~(Pickup_3PL['Category Level 3'].isnull()),'SH'] = 'Reverse_3PL'\n",
    "\n",
    "\n",
    "# In[9]:\n",
    "\n",
    "\n",
    "Pickup_ML.loc[(Pickup_ML['Category Level 3'].isin(['Delayed more than 3 Business days'])),'Category Level 2'] = 'Delayed'\n",
    "Pickup_ML.loc[(Pickup_ML['Category Level 3'].isin(['Pick Up Approvals','Exchange Approvals'])),'Category Level 2'] = 'Approvals'\n",
    "Pickup_ML.loc[(Pickup_ML['Category Level 3'].isin(['Disputing pick up attempt'])),'Category Level 2'] = 'Disputed pick up'\n",
    "Pickup_ML.loc[(Pickup_ML['Category Level 3'].isin(['Picked up not delivered to WH'])),'Category Level 2'] = 'Picked Up Refund Pending'\n",
    "Pickup_ML.loc[~(Pickup_ML['Category Level 3'].isnull()),'Category Level 1'] = 'Pick-Up'\n",
    "Pickup_ML.loc[~(Pickup_ML['Category Level 3'].isnull()),'SH'] = 'Reverse_ML'\n",
    "Forward_3PL.loc[~(Forward_3PL['Category Level 3'].isnull()),'SH'] = '3PL_Forward'\n",
    "\n",
    "Non_Others.loc[~(Non_Others['Category Level 3'].isnull()),'Order_ID'] = 'Not Applicable'\n",
    "Non_Others.loc[~(Non_Others['Category Level 3'].isnull()),'Tracking Number'] = 'Not Applicable'\n",
    "\n",
    "Master_3PL.loc[~(Master_3PL['Category Level 3'].isnull()),'Order_ID'] = 'Not Applicable'\n",
    "Master_3PL.loc[~(Master_3PL['Category Level 3'].isnull()),'Tracking Number'] = 'Not Applicable'\n",
    "Master_3PL.loc[~(Master_3PL['Category Level 3'].isnull()),'SH'] = 'Others'\n",
    "\n",
    "Master_ML.loc[~(Master_ML['Category Level 3'].isnull()),'Order_ID'] = 'Not Applicable'\n",
    "Master_ML.loc[~(Master_ML['Category Level 3'].isnull()),'Tracking Number'] = 'Not Applicable'\n",
    "Master_ML.loc[~(Master_ML['Category Level 3'].isnull()),'SH'] = 'Others'\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Combine reports and create various subsets of data for teams handling Forward and Reverse categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\300024402\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:21: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\300024402\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:22: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\300024402\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:23: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\300024402\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:24: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\300024402\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:25: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\300024402\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:27: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\300024402\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:28: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\300024402\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:30: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n"
     ]
    }
   ],
   "source": [
    "# In[10]:\n",
    "\n",
    "\n",
    "columns=['SH','Category Level 1','Category Level 2','Category Level 3','Order_ID','Task ID'\n",
    "         ,'Task Status','Tenant ID','Tracking Number','Entity_Id','Task Created Date']\n",
    "Transport_ML_1=Transport_ML[columns]\n",
    "Forward_ML_1=Forward_ML[columns]\n",
    "Forward_3PL_1=Forward_3PL[columns]\n",
    "Pickup_3PL_1=Pickup_3PL[columns]\n",
    "Pickup_ML_1=Pickup_ML[columns]\n",
    "# Task_Others_1=Task_Others[columns]\n",
    "Master_3PL_1=Master_3PL[columns]\n",
    "Master_ML_1=Master_ML[columns]\n",
    "Non_Others_1=Non_Others[columns]\n",
    "\n",
    "# Change datatype\n",
    "import numpy as np\n",
    "Transport_ML_1['Entity_Id']=Transport_ML_1['Entity_Id'].fillna(0).astype(np.int64)\n",
    "Forward_ML_1['Entity_Id']=Forward_ML_1['Entity_Id'].fillna(0).astype(np.int64)\n",
    "Forward_3PL_1['Entity_Id']=Forward_3PL_1['Entity_Id'].fillna(0).astype(np.int64)\n",
    "Pickup_3PL_1['Entity_Id']=Pickup_3PL_1['Entity_Id'].fillna(0).astype(np.int64)\n",
    "Pickup_ML_1['Entity_Id']=Pickup_ML_1['Entity_Id'].fillna(0).astype(np.int64)\n",
    "# Task_Others_1['Entity_Id']=Task_Others_1['Entity_Id'].fillna(0).astype(np.int64)\n",
    "Master_3PL_1['Entity_Id']=Master_3PL_1['Entity_Id'].fillna(0).astype(np.int64)\n",
    "Master_ML_1['Entity_Id']=Master_ML_1['Entity_Id'].fillna(0).astype(np.int64)\n",
    "# Task_Others['Style Id']=Task_Others['Style Id'].fillna(0).astype(np.int64)\n",
    "Non_Others_1['Entity_Id']=Non_Others_1['Entity_Id'].fillna(0).astype(np.int64)\n",
    "\n",
    "\n",
    "# Concatenate dataframes\n",
    "frames_r=[Pickup_3PL_1,Pickup_ML_1]\n",
    "result_r=pd.concat(frames_r)\n",
    "frames_f=[Transport_ML_1,Forward_ML_1,Forward_3PL_1]\n",
    "result_f=pd.concat(frames_f)\n",
    "frames=[result_f,result_r]\n",
    "result_1=pd.concat(frames)\n",
    "\n",
    "\n",
    "# Remove tasks in from DF A if it is in DF B\n",
    "task_check=[]\n",
    "task_check=result_1['Task ID']\n",
    "task_check_1=[]\n",
    "task_check_1=Non_Others_1['Task ID']\n",
    "Non_Others_1=Non_Others_1[~(Non_Others_1['Task ID'].isin(task_check))]\n",
    "Non_Others_1.drop_duplicates()\n",
    "Master_ML_1=Master_ML_1[~(Master_ML_1['Task ID'].isin(task_check))]\n",
    "Master_3PL_1=Master_3PL_1[~(Master_3PL_1['Task ID'].isin(task_check))]\n",
    "rame=[result_1,Master_ML_1,Master_3PL_1,Non_Others_1, Pre_packed]\n",
    "arr2=[Master_ML_1,Master_3PL_1,Non_Others_1, Pre_packed]\n",
    "master_concat=pd.concat(arr2)\n",
    "result_v1=pd.concat(rame)\n",
    "\n",
    "\n",
    "\n",
    "result_v1['Entity_Id']=result_v1['Entity_Id'].astype(str)\n",
    "result_v1[\"concat\"]=result_v1[\"Category Level 1\"].map(str)+'-'+result_v1[\"Category Level 2\"].map(str)+'-'+result_v1[\"Category Level 3\"].map(str)\n",
    "\n",
    "#Filter only Comp A tasks\n",
    "result=result_v1.loc[((result_v1['Tenant ID'])==1234)]\n",
    "\n",
    "\n",
    "#Remove IST from created date and convert to string\n",
    "from datetime import datetime\n",
    "result[\"Task Created Date\"] = [sub.replace('IST ', '') for sub in result[\"Task Created Date\"]]\n",
    "\n",
    "#Calculate ageing of open tasks\n",
    "t=result[\"Task Created Date\"].tolist()\n",
    "i=0\n",
    "length=len(result[\"Task Created Date\"])\n",
    "ageing_in_hours=[]\n",
    "c=datetime.now()\n",
    "while i < length: \n",
    "    j=(c-datetime.strptime(t[i],\"%a %b %d %H:%M:%S %Y\") ).total_seconds()/(86400) \n",
    "    ageing_in_hours.append(round(j,2))\n",
    "    i+=1\n",
    "result[\"Ageing_in_days\"]=ageing_in_hours\n",
    "\n",
    "\n",
    "# Create ageing busckets basis ageing calculated in previous step\n",
    "result.loc[(result['Ageing_in_days']>10),'Ageing_bucket'] = 'Days10+'\n",
    "result.loc[(result['Ageing_in_days']<=10),'Ageing_bucket'] = '9 to 10'\n",
    "result.loc[(result['Ageing_in_days']<=9),'Ageing_bucket'] = '8 to 9'\n",
    "result.loc[(result['Ageing_in_days']<=8),'Ageing_bucket'] = '7 to 8'\n",
    "result.loc[(result['Ageing_in_days']<=7),'Ageing_bucket'] = '6 to 7'\n",
    "result.loc[(result['Ageing_in_days']<=6),'Ageing_bucket'] = '5 to 6'\n",
    "result.loc[(result['Ageing_in_days']<=5),'Ageing_bucket'] = '4 to 5'\n",
    "result.loc[(result['Ageing_in_days']<=4),'Ageing_bucket'] = '3 to 4'\n",
    "result.loc[(result['Ageing_in_days']<=3),'Ageing_bucket'] = '2 to 3'\n",
    "result.loc[(result['Ageing_in_days']<=2),'Ageing_bucket'] = '1 to 2'\n",
    "result.loc[(result['Ageing_in_days']<=1),'Ageing_bucket'] = '0 to 1'\n",
    "\n",
    "\n",
    "result.loc[(result['Ageing_in_days']>10),'Ageing_bucket_EORS'] = 'Days10+'\n",
    "result.loc[(result['Ageing_in_days']<=10),'Ageing_bucket_EORS'] = '8 to 10'\n",
    "result.loc[(result['Ageing_in_days']<=7),'Ageing_bucket_EORS'] = '4 to 7'\n",
    "result.loc[(result['Ageing_in_days']<=3),'Ageing_bucket_EORS'] = '0 to 3'\n",
    "\n",
    "\n",
    "# In[22]:\n",
    "\n",
    "\n",
    "# Subset data basis the given condition \n",
    "ar_r3=['Defective post usage','PPS-Refund-IMPS','PPS-Refund-NEFT','NEFT-Transfer-NEFT transfer','Self-Shipped No Update from WH','Missing Item in Return Packet','Rejected Reshipment Pending','QA failed by DC/WH','No update from WH','Defective product received','Wrong product received','Price tags missing','Used product received','MRP mismatch','Article ID mismatch']\n",
    "ar_f3=['Re- Investigate POD','Disputing signature','Full Item missing','Partial Item Missing']\n",
    "ar_f1=['3PL','ML','MarketPlace','WH and Logistics']\n",
    "ar_r1=['Pick-Up','ROH']\n",
    "\n",
    "\n",
    "# In[23]:\n",
    "\n",
    "\n",
    "#Packet_ids from seperate reports\n",
    "Fwd_entity1 = (result_f[((result_f['Category Level 1'].isin(ar_f1))|(result_f['Category Level 3'].isin(ar_f3)))&(~result_f['Entity_Id'].isnull())&(~(result_f['Entity_Id']==0))]['Entity_Id']).unique().tolist()\n",
    "\n",
    "\n",
    "#Release_ids from Master reports for fwd\n",
    "Fwd_entity2 = (master_concat[((master_concat['Category Level 1'].isin(ar_f1))|(master_concat['Category Level 3'].isin(ar_f3)))&(~master_concat['Entity_Id'].isnull())&(~master_concat['Category Level 3'].isin(['Not Packed', 'Not Packed_WH']))&(~(master_concat['Entity_Id']==0))]['Entity_Id']).unique().tolist()\n",
    "\n",
    "Fwd_entity3 = (master_concat[(master_concat['Category Level 3'].isin(['Not Packed', 'Not Packed_WH']))&(~master_concat['Entity_Id'].isnull())&(~(master_concat['Entity_Id']==0))]['Entity_Id']).unique().tolist()\n",
    "\n",
    "#Return_ids from seperate reports\n",
    "Rev_entity1 = (result_r[(result_r['Category Level 1'].isin(ar_r1) | result_r['Category Level 3'].isin(ar_r3))&(~result_r['Entity_Id'].isnull())&(~(result_r['Entity_Id']==0))]['Entity_Id']).unique().tolist()\n",
    "\n",
    "\n",
    "#Release_ids from Master reports for reverse\n",
    "Rev_entity2 =(master_concat[(master_concat['Category Level 1'].isin(ar_r1) | master_concat['Category Level 3'].isin(ar_r3))&(~master_concat['Entity_Id'].isnull())&(~(master_concat['Entity_Id']==0))]['Entity_Id']).unique().tolist()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# In[29]:\n",
    "taskdetails['inapp_interactions']=taskdetails['inapp_interactions'].fillna(0).astype(np.int64)\n",
    "taskdetails['Intercation_count_call']=taskdetails['Intercation_count_call'].fillna(0).astype(np.int64)\n",
    "taskdetails['Total_interactions']=taskdetails['inapp_interactions']+taskdetails['Intercation_count_call']\n",
    "\n",
    "\n",
    "taskdetails.loc[(taskdetails['Total_interactions']>3),'Interaction_Bucket'] = '3+'\n",
    "taskdetails.loc[(taskdetails['Total_interactions']==3),'Interaction_Bucket'] = '3'\n",
    "taskdetails.loc[(taskdetails['Total_interactions']==2),'Interaction_Bucket'] = '2'\n",
    "taskdetails.loc[(taskdetails['Total_interactions']<=1),'Interaction_Bucket'] = '0 to 1'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fetch futher details from database based on issue ids extracted in earlier step"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql.cursors\n",
    "\n",
    "order_release_id=\"\"\"\n",
    "select\n",
    "zipcode,\n",
    "ol.created_on,\n",
    "packet_id_fk as Entity_Id,\n",
    "packet_id_fk as packet_return_id,\n",
    "ol.courier_code,\n",
    "ol.status_code,\n",
    "pk.final_amount,\n",
    "ol.payment_method as payment_mode,\n",
    "source_wh_id\n",
    "from order_line ol \n",
    "left join order_release_VIEW ord\n",
    "on ol.store_order_id=ord.store_order_id\n",
    "left join (select packet_id_fk as packet,\n",
    "sum(final_amount) as final_amount\n",
    "from order_line\n",
    "where packet_id_fk in ('\"\"\" +  \"','\".join(map(str, Fwd_entity1)) + \"\"\"')\n",
    "group by 1)pk\n",
    "on ol.packet_id_fk=pk.packet\n",
    "where ol.packet_id_fk in ('\"\"\" +  \"','\".join(map(str, Fwd_entity1)) + \"\"\"') \n",
    "group by 1,2,3,4,5\n",
    "\"\"\"\n",
    "\n",
    "connection = pymysql.connect(host='host',\n",
    "                            user='user',\n",
    "                            password='pswrd',                             \n",
    "                            db='db',\n",
    "                            charset='some charset',\n",
    "                            cursorclass=pymysql.cursors.DictCursor)\n",
    "order_release_id_df1=pd.read_sql(order_release_id,connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "import pymysql.cursors\n",
    "\n",
    "order_release_id=\"\"\"\n",
    "select\n",
    "zipcode,\n",
    "ol.created_on,\n",
    "order_release_id_fk as Entity_Id,\n",
    "packet_id_fk as packet_return_id,\n",
    "ol.courier_code,\n",
    "ol.status_code,\n",
    "pk.final_amount,\n",
    "ol.payment_method as payment_mode,\n",
    "source_wh_id\n",
    "from order_line ol \n",
    "left join order_release_VIEW ord\n",
    "on ol.store_order_id=ord.store_order_id\n",
    "left join (select packet_id_fk as packet,\n",
    "sum(final_amount) as final_amount\n",
    "from order_line\n",
    "where order_release_id_fk in ('\"\"\" +  \"','\".join(map(str, Fwd_entity2)) + \"\"\"')\n",
    "group by 1)pk\n",
    "on ol.packet_id_fk=pk.packet\n",
    "where ol.order_release_id_fk in ('\"\"\" +  \"','\".join(map(str, Fwd_entity2)) + \"\"\"') \n",
    "group by 1,2,3,4,5\n",
    "\"\"\"\n",
    "\n",
    "connection = pymysql.connect(host='host',\n",
    "                            user='user',\n",
    "                            password='pswrd',                             \n",
    "                            db='db',\n",
    "                            charset='some charset',\n",
    "                            cursorclass=pymysql.cursors.DictCursor)\n",
    "order_release_id_df2=pd.read_sql(order_release_id,connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "order_release_id=\"\"\"\n",
    "select\n",
    "zipcode,\n",
    "ol.created_on,\n",
    "ol.store_order_id as Entity_Id,\n",
    "packet_id_fk as packet_return_id,\n",
    "ol.courier_code,\n",
    "ol.status_code,\n",
    "pk.final_amount,\n",
    "ol.payment_method as payment_mode,\n",
    "source_wh_id\n",
    "from order_line ol \n",
    "left join order_release_VIEW ord\n",
    "on ol.store_order_id=ord.store_order_id\n",
    "left join (select order_release_id_fk as order_release,\n",
    "sum(final_amount) as final_amount\n",
    "from order_line\n",
    "where store_order_id in ('\"\"\" +  \"','\".join(map(str, Fwd_entity3)) + \"\"\"')\n",
    "and status_code in ('WP')\n",
    "group by 1)pk\n",
    "on ol.order_release_id_fk=pk.order_release\n",
    "where ol.store_order_id in ('\"\"\" +  \"','\".join(map(str, Fwd_entity3)) + \"\"\"') \n",
    "and ol.status_code in ('WP')\n",
    "group by 1,2,3,4,5\n",
    "\"\"\"\n",
    "\n",
    "connection = pymysql.connect(host='host',\n",
    "                            user='user',\n",
    "                            password='pswrd',                             \n",
    "                            db='db',\n",
    "                            charset='some charset',\n",
    "                            cursorclass=pymysql.cursors.DictCursor)\n",
    "order_release_id_df3=pd.read_sql(order_release_id,connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "order_release_id=\"\"\"\n",
    "select\n",
    "zipcode,\n",
    "order_release_id_fk as Entity_Id,\n",
    "packet_id_fk as packet_return_id,\n",
    "ol.created_on,\n",
    "ol.courier_code,\n",
    "ol.status_code,\n",
    "pk.final_amount,\n",
    "ol.payment_method as payment_mode,\n",
    "source_wh_id\n",
    "from order_line ol\n",
    "left join order_release_VIEW ord\n",
    "on ol.store_order_id=ord.store_order_id\n",
    "left join (select order_release_id_fk as order_release,\n",
    "final_amount\n",
    "from order_line\n",
    "where order_release_id_fk in ('\"\"\" +  \"','\".join(map(str, Fwd_entity3)) + \"\"\"')\n",
    "group by 1)pk\n",
    "on ol.order_release_id_fk=pk.order_release\n",
    "where ol.order_release_id_fk in ('\"\"\" +  \"','\".join(map(str, Fwd_entity3)) + \"\"\"')\n",
    "group by 1,2,3,4,5\n",
    "\"\"\"\n",
    "\n",
    "connection = pymysql.connect(host='host',\n",
    "                            user='user',\n",
    "                            password='pswrd',                             \n",
    "                            db='db',\n",
    "                            charset='some charset',\n",
    "                           cursorclass=pymysql.cursors.DictCursor)\n",
    "order_release_id_df4=pd.read_sql(order_release_id,connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Changed on 16th Jul\n",
    "\n",
    "pushed_not_packed=\"\"\"\n",
    "SELECT cor.portal_order_release_id AS Entity_Id,\n",
    "cor.pushed_to_pick_time AS ptp_time\n",
    "FROM   capture_order_release cor\n",
    "WHERE  cor.order_release_status NOT IN ('CANCELLED')\n",
    "AND  cor.portal_order_release_id IN ('\"\"\" +  \"','\".join(map(str, Fwd_entity3)) + \"\"\"')\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "connection_3=pymysql.connect(host='host',\n",
    "                            user='user',\n",
    "                            password='pswrd',                             \n",
    "                            db='db',\n",
    "                            charset='some charset',\n",
    "                           cursorclass=pymysql.cursors.DictCursor)\n",
    "pushed_not_packed_df=pd.read_sql(pushed_not_packed,connection_3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# In[34]:\n",
    "\n",
    "\n",
    "import pymysql.cursors\n",
    "\n",
    "returns_data=\"\"\"\n",
    "\n",
    "select \n",
    "r.zipcode,\n",
    "rl.created_on,\n",
    "r.id as Entity_Id, \n",
    "return_id as packet_return_id,\n",
    "r.courier_code,\n",
    "r.status_code,\n",
    "final_amount,\n",
    "refund_mode as payment_mode,\n",
    "'NA' as source_wh_id\n",
    "from return_line rl\n",
    "left join ( \n",
    "select \n",
    "id,\n",
    "zipcode,\n",
    "courier_code,\n",
    "status_code,\n",
    "refund_mode,\n",
    "refund_amount as final_amount\n",
    "from \n",
    "returns_VIEW\n",
    ")r\n",
    "on rl.return_id=r.id\n",
    "where return_id in ('\"\"\" +  \"','\".join(map(str, Rev_entity1)) + \"\"\"')\n",
    "order by rl.created_on desc\n",
    "\"\"\"\n",
    "\n",
    "connection = pymysql.connect(host='host',\n",
    "                            user='user',\n",
    "                            password='pswrd',                             \n",
    "                            db='db',\n",
    "                            charset='some charset',\n",
    "                            cursorclass=pymysql.cursors.DictCursor)\n",
    "Returns_data_df_1=pd.read_sql(returns_data,connection)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# In[35]:\n",
    "\n",
    "\n",
    "import pymysql.cursors\n",
    "\n",
    "returns_data=\"\"\"\n",
    "\n",
    "select \n",
    "r.zipcode,\n",
    "rl.created_on,\n",
    "order_release_id as Entity_Id, \n",
    "return_id as packet_return_id,\n",
    "r.courier_code,\n",
    "r.status_code,\n",
    "final_amount,\n",
    "refund_mode as payment_mode,\n",
    "'NA' as source_wh_id\n",
    "from return_line rl\n",
    "left join ( \n",
    "select \n",
    "id,\n",
    "zipcode,\n",
    "courier_code,\n",
    "status_code,\n",
    "refund_mode,\n",
    "refund_amount as final_amount\n",
    "from \n",
    "returns_VIEW\n",
    ")r\n",
    "on rl.return_id=r.id\n",
    "where  order_release_id in ('\"\"\" +  \"','\".join(map(str, Rev_entity2)) + \"\"\"') and r.status_code not in ('DEC')\n",
    "order by rl.created_on desc\n",
    "\"\"\"\n",
    "\n",
    "connection = pymysql.connect(host='host',\n",
    "                            user='user',\n",
    "                            password='pswrd',                             \n",
    "                            db='db',\n",
    "                            charset='some charset',\n",
    "                            cursorclass=pymysql.cursors.DictCursor)\n",
    "Returns_data_df_2=pd.read_sql(returns_data,connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\300024402\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:36: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=False'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass 'sort=True'.\n",
      "\n",
      "C:\\Users\\300024402\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:44: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=False'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass 'sort=True'.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# In[36]:\n",
    "\n",
    "import re \n",
    "result[\"Entity_Id\"]=result[\"Entity_Id\"].astype(str)\n",
    "\n",
    "\n",
    "# In[37]:\n",
    "\n",
    "\n",
    "Returns_data_df_1.drop_duplicates(inplace = True)\n",
    "Returns_data_df_2.drop_duplicates(inplace = True)\n",
    "order_release_id_df1.drop_duplicates(inplace = True)\n",
    "order_release_id_df2.drop_duplicates(inplace = True)\n",
    "order_release_id_df3.drop_duplicates(inplace = True)\n",
    "order_release_id_df4.drop_duplicates(inplace = True)\n",
    "\n",
    "# In[38]:\n",
    "\n",
    "\n",
    "Final_v1=()\n",
    "Returns_data_df_1['Entity_Id']=Returns_data_df_1['Entity_Id'].astype(str)\n",
    "Returns_data_df_2['Entity_Id']=Returns_data_df_2['Entity_Id'].astype(str)\n",
    "fr_r=[Returns_data_df_1,Returns_data_df_2]\n",
    "Final_v1=pd.concat(fr_r)\n",
    "Final_v1.drop_duplicates(inplace = True)\n",
    "\n",
    "\n",
    "# In[39]:\n",
    "\n",
    "\n",
    "order_release_id_df1['Entity_Id']=order_release_id_df1['Entity_Id'].astype(str)\n",
    "order_release_id_df2['Entity_Id']=order_release_id_df2['Entity_Id'].astype(str)\n",
    "order_release_id_df3['Entity_Id']=order_release_id_df3['Entity_Id'].astype(str)\n",
    "order_release_id_df4['Entity_Id']=order_release_id_df4['Entity_Id'].astype(str)\n",
    "fr_f=[order_release_id_df1,order_release_id_df2, order_release_id_df3,order_release_id_df4]\n",
    "Final_v2=pd.concat(fr_f)\n",
    "Final_v2.drop_duplicates(inplace = True)\n",
    "\n",
    "\n",
    "# In[40]:\n",
    "\n",
    "\n",
    "fr=[Final_v1,Final_v2]\n",
    "Final_1=pd.concat(fr)\n",
    "Final_1[\"Entity_Id\"]=Final_1[\"Entity_Id\"].astype(str)\n",
    "result['Entity_Id']=result['Entity_Id'].astype(str)\n",
    "Final_1[\"final_amount\"] = Final_1[\"final_amount\"].fillna(0)\n",
    "\n",
    "\n",
    "# In[41]:\n",
    "\n",
    "\n",
    "Final_2 = pd.merge(result,Final_1,  how='left', left_on=['Entity_Id'], right_on = ['Entity_Id'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract location for all the issues Ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "I am here-1\n",
      "Submitting Query to DDP\n",
      "Query submission success. QueryId:856985\n",
      "Checking Query Status\n",
      "Query still executing. Sleeping:60 seconds\n",
      "Checking Query Status\n",
      "Query still executing. Sleeping:60 seconds\n",
      "Checking Query Status\n",
      "Query still executing. Sleeping:60 seconds\n",
      "Checking Query Status\n",
      "Query still executing. Sleeping:60 seconds\n",
      "Checking Query Status\n",
      "Query still executing. Sleeping:60 seconds\n",
      "Checking Query Status\n",
      "Query still executing. Sleeping:60 seconds\n",
      "Checking Query Status\n",
      "Query still executing. Sleeping:60 seconds\n",
      "Checking Query Status\n",
      "Query still executing. Sleeping:60 seconds\n",
      "Checking Query Status\n",
      "Query still executing. Sleeping:60 seconds\n",
      "Checking Query Status\n",
      "Query still executing. Sleeping:60 seconds\n",
      "Checking Query Status\n",
      "Query still executing. Sleeping:60 seconds\n",
      "Checking Query Status\n",
      "Query still executing. Sleeping:60 seconds\n",
      "Checking Query Status\n",
      "Query still executing. Sleeping:60 seconds\n",
      "Checking Query Status\n",
      "Query still executing. Sleeping:60 seconds\n",
      "Checking Query Status\n",
      "Query still executing. Sleeping:60 seconds\n",
      "Checking Query Status\n",
      "Query completed. Downloading Result\n",
      "\n",
      "File Downloaded:location000.gz\n",
      "I am here -2\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "os.chdir('D:\\\\Python results')\n",
    "import pandas as pd\n",
    "import ddp_new as ddp\n",
    "import numpy as np\n",
    "import datetime\n",
    "import re\n",
    "QUERY_USER='mail id'\n",
    "QUERY_OUT_DIR='D:\\\\Python results'\n",
    "\n",
    "\n",
    "# In[43]:\n",
    "\n",
    "\n",
    "Sql=\"\"\"select pincode,dl.city_group as city\n",
    "from dim_location dl where cast(pincode as varchar) in('\"\"\" +  \"','\".join(map(str, g)) + \"\"\"')\"\"\"\n",
    "\n",
    "\n",
    "# In[44]:\n",
    "\n",
    "\n",
    "QUERY_NAME='location'\n",
    "print(\"I am here-1\")\n",
    "Sql = re.sub(\"%(?![0-9a-fA-F]{2})\", \"%25\", Sql)\n",
    "Sql = re.sub(\"\\\\+\", \"%2B\", Sql)\n",
    "filename=ddp.start(Sql,QUERY_USER,QUERY_OUT_DIR,QUERY_NAME,0)\n",
    "print (\"I am here -2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "location=pd.read_table('D:\\\\Python resultslocation000.gz',compression='gzip',sep=',',header=None,parse_dates=True,error_bad_lines=False)\n",
    "location.columns = location.iloc[0]\n",
    "location = location[1:]\n",
    "\n",
    "k = Final_2['Tracking Number'].unique().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "order_release_id=\"\"\"\n",
    "(select tracking_no as tracking_number,\n",
    "dc.code as DC,\n",
    "max(cast(assignment_date as date)) as assignment_date\n",
    "from\n",
    "trip_order_assignment toa\n",
    "left join trip t\n",
    "on toa.trip_id=t.id  \n",
    "left join delivery_center dc\n",
    "on t.delivery_center_id=dc.id\n",
    "where toa.tracking_no in ('\"\"\" +  \"','\".join(map(str, k)) + \"\"\"')\n",
    "group by tracking_no,dc.code)\n",
    "union\n",
    "(select ots.tracking_number,\n",
    "dc.code as DC,\n",
    "max(cast(ots.last_modified_on as date)) as assignment_date\n",
    "from\n",
    "order_to_ship ots\n",
    "left join delivery_center dc\n",
    "on ots.delivery_center_id=dc.id\n",
    "where ots.tracking_number in ('\"\"\" +  \"','\".join(map(str, k)) + \"\"\"')\n",
    "group by tracking_number,dc.code)\n",
    "union\n",
    "(select rs.tracking_number,\n",
    "dc.code as DC,\n",
    "max(cast(rs.last_modified_on as date)) as assignment_date\n",
    "from\n",
    "return_shipment rs\n",
    "left join delivery_center dc\n",
    "on rs.delivery_center_id = dc.id\n",
    "where rs.tracking_number in ('\"\"\" +  \"','\".join(map(str, k)) + \"\"\"')\n",
    "group by tracking_number,dc.code)\n",
    "\"\"\"\n",
    "connection = pymysql.connect(host='host',\n",
    "                            user='user',\n",
    "                            password='pswrd',                             \n",
    "                            db='db',\n",
    "                            charset='some charset',\n",
    "                            cursorclass=pymysql.cursors.DictCursor)\n",
    "location_dc=pd.read_sql(order_release_id,connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "location_dc['row_number']=location_dc.sort_values('assignment_date', ascending = False).groupby('tracking_number').cumcount()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "location_dc_1=location_dc[location_dc['row_number']==0]\n",
    "# print(location_dc.shape)\n",
    "# print(location_dc_1.shape)\n",
    "location.rename(columns = {'pincode':'zipcode'}, inplace= True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\300024402\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \"\"\"\n"
     ]
    }
   ],
   "source": [
    "location['zipcode']=location['zipcode'].astype(str)\n",
    "location_dc_1['tracking_number']=location_dc_1['tracking_number'].astype(str)\n",
    "\n",
    "Final_3=pd.DataFrame()\n",
    "Final_3 = pd.merge(Final_2,location,  how='left', left_on=['zipcode'], right_on = ['zipcode'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "pushed_not_packed_df['Entity_Id']=pushed_not_packed_df['Entity_Id'].astype(str)\n",
    "Final_4=pd.DataFrame()\n",
    "Final_4 = pd.merge(Final_3,location_dc_1,  how='left', left_on=['Tracking Number'], right_on = ['tracking_number'])\n",
    "\n",
    "Final_5=pd.DataFrame()\n",
    "Final_5 = pd.merge(Final_4,taskdetails,  how='left', left_on=['Task ID'], right_on = ['task_id'])\n",
    "Final_5 = pd.merge(Final_5,pushed_not_packed_df,  how='left', left_on=['Entity_Id'], right_on = ['Entity_Id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "Final_5.loc[((~Final_5.orderdetails_returnid.isnull())\n",
    "             &(Final_5.packet_return_id.isnull())\n",
    "             &(Final_5['SH'].isin(['Reverse_ML, Reverse_3PL'])),\n",
    "             'packet_return_id' )]=Final_5.orderdetails_returnid\n",
    "\n",
    "x=Final_5[Final_5.SH.isin(['Reverse_ML', 'Reverse_3PL'])]['packet_return_id'].fillna(0).unique().astype(np.int64).astype(str).tolist()\n",
    "\n",
    "y=Final_5[Final_5.SH.isin(['ML_Transport', 'ML_Forward','3PL_Forward'])]['packet_return_id'].fillna(0).unique().astype(np.int64).astype(str).tolist()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "add_details=\"\"\"\n",
    "(select  \n",
    "source_return_id as packet_return_id,\n",
    "address_type,\n",
    "shipment_status,\n",
    "'' as dc_inscanned_date\n",
    "from return_shipment rs\n",
    "where source_return_id in ('\"\"\" +  \"','\".join(map(str, x)) + \"\"\"'))\n",
    "\n",
    "union \n",
    "(select order_id as packet_return_id, address_type, 'NA' as shipment_status,\n",
    "max(dc_inscanned_date) as dc_inscanned_date\n",
    "from order_to_ship ots\n",
    "left join \n",
    "\t(\n",
    "\t\tselect source_reference_id , max(received_date) as dc_inscanned_date\n",
    "\t\tfrom ml_shipment mls\n",
    "\t\twhere source_reference_id in ('\"\"\" +  \"','\".join(map(str, y)) + \"\"\"')\n",
    "        group by 1\n",
    "\t)mls\n",
    "on ots.order_id=mls.source_reference_id \n",
    "where order_id in ('\"\"\" +  \"','\".join(map(str, y)) + \"\"\"')\n",
    "group by 1,2,3)\n",
    "\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "connection = pymysql.connect(host='host',\n",
    "                            user='user',\n",
    "                            password='pswrd',                             \n",
    "                            db='db',\n",
    "                            charset='charset',\n",
    "                            cursorclass=pymysql.cursors.DictCursor)\n",
    "add_details_df=pd.read_sql(add_details,connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "Final_5.packet_return_id=Final_5.packet_return_id.fillna(0).astype(np.int64).astype(str)\n",
    "\n",
    "add_details_df.packet_return_id=add_details_df.packet_return_id.fillna(0).astype(np.int64).astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "Final_5=pd.merge(Final_5,add_details_df, how='left', left_on='packet_return_id', right_on='packet_return_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "Inv_SJIT=[List of IDs]\n",
    "\n",
    "JIT_FBM=[List of IDs]\n",
    "\n",
    "MDirect=[List of IDs]\n",
    "\n",
    "VendorFlex=[List of IDs]\n",
    "\n",
    "Final_5.loc[((Final_5.source_wh_id.astype(str).isin(Inv_SJIT)),\n",
    "\n",
    "             'PPMP/Vendor_Flex' )]='Inv_SJIT'\n",
    "\n",
    "Final_5.loc[((Final_5.source_wh_id.astype(str).isin(JIT_FBM)),\n",
    "\n",
    "             'PPMP/Vendor_Flex' )]='JIT_FBM'\n",
    "\n",
    "Final_5.loc[((Final_5.source_wh_id.astype(str).isin(MDirect)),\n",
    "\n",
    "             'PPMP/Vendor_Flex' )]='MDirect'\n",
    "\n",
    "Final_5.loc[((Final_5.source_wh_id.astype(str).isin(VendorFlex)),\n",
    "\n",
    "             'PPMP/Vendor_Flex' )]='VendorFlex'\n",
    "\n",
    "Final_5.loc[(~(Final_5.dc_inscanned_date.isnull()),\n",
    "\n",
    "             'Reached_DC' )]='1'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "Incident_cat_1=()\n",
    "Incident_cat_1=Incident_cat[['cat','Issue Type 1','Issue Type 2','Issue Type 3']]\n",
    "\n",
    "Final_6 = pd.merge(Final_5,Incident_cat_1,how='left', left_on=['concat'], right_on = ['cat'])\n",
    "\n",
    "# In[55]:\n",
    "\n",
    "\n",
    "Final_7 =Final_6[~((Final_6['Category Level 1'].isin(['Pick-Up']))&(Final_6['status_code'].isin(['D','C'])))]\n",
    "Final_7=Final_7.drop_duplicates()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Expoting the issue ids with teams and their respective categories to Google sheets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "SCM_columns=['SH', 'Category Level 1','Category Level 2', 'Category Level 3', 'Task Status','Ageing_bucket','zipcode', 'courier_code',\n",
    "'status_code','source_wh_id', 'city', 'DC','task_queue','queue_name','escalated_to','escalated_on','Total_interactions', 'Interaction_Bucket','order_created_date','address_type','shipment_status', 'Reached_DC','PPMP/Vendor_Flex','Issue Type 1','Issue Type 2', 'Issue Type 3',\n",
    "'Reverse Serviceable_flag', 'Forward Serviceability status']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\300024402\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \"\"\"\n"
     ]
    }
   ],
   "source": [
    "from datetime import date\n",
    "SCM_Review=Final_8[(~Final_8['SH'].isin(['Others','Non-Returnable']))\n",
    "                  |(Final_8['Category Level 3'].isin(['Not Packed','Not Packed_WH']))\n",
    "                  &Final_8['Task Status'].isin(['Re-opened','Open','Information Provided'])]\n",
    "SCM_Review['created_on']=SCM_Review['created_on'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "SCM_Review_1=SCM_Review.fillna(0).groupby (SCM_columns).agg({'Task ID':pd.Series.nunique}).reset_index()\n",
    "SCM_Review_1['Pendency_date']=str(date.today())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Report 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "SAMPLE_SPREADSHEET_ID='1Sheet id'\n",
    "SAMPLE_RANGE_NAME='Sheet name'\n",
    "#authorization\n",
    "gc = py.authorize(service_file='Path//creds.json')\n",
    "\n",
    "#open the google spreadsheet 'Hard Stop' is the sheet name\n",
    "sh = gc.open('Sheet Name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "wks_0=sh.worksheet_by_title('Pendency snapshot RD')\n",
    "wks_0.append_table(SCM_Review_1.fillna(0).values.tolist(),overwrite=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Report 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "SAMPLE_SPREADSHEET_ID='sample spreadsheet'\n",
    "\n",
    "sh1 = gc.open('Return Pendency AQS')\n",
    "wks_1=sh1.worksheet_by_title('Task TN')\n",
    "\n",
    "wks_1.clear()\n",
    "\n",
    "wks_1.set_dataframe(Final_8[(Final_8['Category Level 3'].isin(['Delayed more than 3 Business days','Disputing pick up attempt']))\n",
    "       &(Final_8['courier_code']=='ML')\n",
    "        &(Final_8['SH'].isin(['Reverse_ML']))][['Tracking Number','Task ID']],(1,1), Index=False)\n",
    "\n",
    "\n",
    "from datetime import datetime\n",
    "today = datetime.today().strftime('%d-%m-%Y %H:%M:%S')\n",
    "wks_1.update_value('F1','Last modified on '+today)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create Dataframe for daily automailers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "Task_category=Final_8.pivot_table(index=['Category Level 3'], values=['Task ID'], columns='Ageing_bucket', aggfunc=pd.Series.nunique).fillna(0).astype(np.int64)\n",
    "Task_category.columns=Task_category.columns.get_level_values(1)\n",
    "Task_category=Task_category.rename_axis(['Category Level 3']).reset_index(level=0)\n",
    "Task_category['Total']=Task_category.sum(numeric_only=True, axis=1)\n",
    "Task_category.sort_values(by='Total', ascending=False,inplace=True)\n",
    "Task_category.to_csv('Task_category.csv', index=False)\n",
    "Task_category=pd.read_csv('Task_category.csv')\n",
    "Task_category_1=Task_category[~Task_category['Category Level 3'].isin(['Exchange Approvals', \"Pick up Approvals'\"])][:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "Incient_category=Final_8.pivot_table(index=['Issue Type 3'], values=['Task ID'], columns='Ageing_bucket', aggfunc=pd.Series.nunique).fillna(0).astype(np.int64)\n",
    "Incient_category.columns=Incient_category.columns.get_level_values(1)\n",
    "Incient_category=Incient_category.rename_axis(['Issue Type 3']).reset_index(level=0)\n",
    "Incient_category['Total']=Incient_category.sum(numeric_only=True, axis=1)\n",
    "Incient_category.sort_values(by='Total', ascending=False,inplace=True)\n",
    "Incient_category.to_csv('Incient_category.csv', index=False)\n",
    "Incient_category=pd.read_csv('Incient_category.csv')\n",
    "Incient_category_1=Incient_category[~Incient_category['Issue Type 3'].isin(['Exchange Approval', 'Pick up Approval'])][:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "SH=Final_8.pivot_table(index=['SH'], values=['Task ID'], columns='Ageing_bucket', aggfunc=pd.Series.nunique).fillna(0).astype(np.int64)\n",
    "SH.columns=SH.columns.get_level_values(1)\n",
    "SH=SH.rename_axis(['SH']).reset_index(level=0)\n",
    "SH['Total']=SH.sum(numeric_only=True, axis=1)\n",
    "SH.to_csv('SH.csv', index=False)\n",
    "SH=pd.read_csv('SH.csv')\n",
    "SH.loc['Grand Total']=SH.sum( axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "Task_Count=Final_8[Final_8['Task Status'].isin(['Open','Additional Information Required','Re-opened','Information Provided'])].groupby('Ageing_bucket_EORS').agg({ 'Task ID': pd.Series.nunique}).T.rename_axis(['Ageing']).reset_index()\n",
    "Task_Count.to_csv('Task_Count.csv', index=False)\n",
    "Task_Count=pd.read_csv('Task_Count.csv')\n",
    "\n",
    "Incient_category_EORS=Final_8[Final_8['Task Status'].isin(['Open','Additional Information Required','Re-opened','Information Provided'])].pivot_table(index=['Issue Type 3'], values=['Task ID'], columns='Ageing_bucket_EORS', aggfunc=pd.Series.nunique).fillna(0).astype(np.int64)\n",
    "Incient_category_EORS.columns=Incient_category_EORS.columns.get_level_values(1)\n",
    "Incient_category_EORS=Incient_category_EORS.rename_axis(['Issue Type 3']).reset_index(level=0)\n",
    "Incient_category_EORS['Total']=Incient_category_EORS.sum(numeric_only=True, axis=1)\n",
    "Incient_category_EORS.sort_values(by='Total', ascending=False,inplace=True)\n",
    "Incient_category_EORS.to_csv('Incient_category_EORS.csv', index=False)\n",
    "Incient_category_EORS=pd.read_csv('Incient_category_EORS.csv')\n",
    "Incient_category_EORS_1=Incient_category_EORS[~Incient_category_EORS['Issue Type 3'].isin(['Exchange Approval', 'Pick up Approval'])][:10]\n",
    "\n",
    "Task_Count_Interaction=Final_8[Final_8['Task Status'].isin(['Open','Additional Information Required','Re-opened','Information Provided'])].groupby('Interaction_Bucket').agg({'Task ID': pd.Series.nunique}).T.rename_axis(['Interactions']).reset_index()\n",
    "Task_Count_Interaction.to_csv('Task_Count_Interaction.csv', index=False)\n",
    "Task_Count_Interaction=pd.read_csv('Task_Count_Interaction.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "SH_Open=Final_8[Final_8['Task Status'].isin(['Open'])].pivot_table(index=['SH'], values=['Task ID'], columns='Ageing_bucket', aggfunc=pd.Series.nunique).fillna(0).astype(np.int64)\n",
    "SH_Open.columns=SH_Open.columns.get_level_values(1)\n",
    "SH_Open=SH_Open.rename_axis(['SH']).reset_index(level=0)\n",
    "SH_Open['Total']=SH_Open.sum(numeric_only=True, axis=1)\n",
    "SH_Open.to_csv('SH_Open.csv', index=False)\n",
    "SH_Open=pd.read_csv('SH_Open.csv')\n",
    "SH_Open.loc['Grand Total']=SH_Open.sum( axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "City_split=Final_8.pivot_table(index=['city'], values=['Task ID'], columns='Ageing_bucket', aggfunc=pd.Series.nunique).fillna(0).astype(np.int64)\n",
    "City_split.columns=City_split.columns.get_level_values(1)\n",
    "City_split=City_split.rename_axis(['city']).reset_index(level=0)\n",
    "City_split['Total']=Final_8.groupby(\"city\").agg({ 'Task ID': pd.Series.nunique}).reset_index()['Task ID']\n",
    "City_split.sort_values(by='Total', ascending=False,inplace=True)\n",
    "City_split.to_csv('City_split.csv', index=False)\n",
    "City_split=pd.read_csv('City_split.csv')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sample data to be shared in automailer (Table 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "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>SH</th>\n",
       "      <th>0 to 1</th>\n",
       "      <th>1 to 2</th>\n",
       "      <th>2 to 3</th>\n",
       "      <th>3 to 4</th>\n",
       "      <th>4 to 5</th>\n",
       "      <th>5 to 6</th>\n",
       "      <th>6 to 7</th>\n",
       "      <th>7 to 8</th>\n",
       "      <th>8 to 9</th>\n",
       "      <th>9 to 10</th>\n",
       "      <th>Days10+</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>3PL_Forward</td>\n",
       "      <td>1035</td>\n",
       "      <td>603</td>\n",
       "      <td>463</td>\n",
       "      <td>323</td>\n",
       "      <td>233</td>\n",
       "      <td>118</td>\n",
       "      <td>27</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>2845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>ML_Forward</td>\n",
       "      <td>399</td>\n",
       "      <td>83</td>\n",
       "      <td>44</td>\n",
       "      <td>26</td>\n",
       "      <td>25</td>\n",
       "      <td>14</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>31</td>\n",
       "      <td>648</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>ML_Transport</td>\n",
       "      <td>692</td>\n",
       "      <td>487</td>\n",
       "      <td>355</td>\n",
       "      <td>308</td>\n",
       "      <td>297</td>\n",
       "      <td>216</td>\n",
       "      <td>218</td>\n",
       "      <td>205</td>\n",
       "      <td>128</td>\n",
       "      <td>106</td>\n",
       "      <td>366</td>\n",
       "      <td>3378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>MarketPlace_Not_Packed</td>\n",
       "      <td>16</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Non-Returnable</td>\n",
       "      <td>3566</td>\n",
       "      <td>1862</td>\n",
       "      <td>807</td>\n",
       "      <td>541</td>\n",
       "      <td>398</td>\n",
       "      <td>287</td>\n",
       "      <td>214</td>\n",
       "      <td>154</td>\n",
       "      <td>135</td>\n",
       "      <td>100</td>\n",
       "      <td>402</td>\n",
       "      <td>8466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>Others</td>\n",
       "      <td>944</td>\n",
       "      <td>1155</td>\n",
       "      <td>773</td>\n",
       "      <td>584</td>\n",
       "      <td>498</td>\n",
       "      <td>389</td>\n",
       "      <td>408</td>\n",
       "      <td>281</td>\n",
       "      <td>204</td>\n",
       "      <td>153</td>\n",
       "      <td>1314</td>\n",
       "      <td>6703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>Reverse_3PL</td>\n",
       "      <td>1137</td>\n",
       "      <td>591</td>\n",
       "      <td>328</td>\n",
       "      <td>255</td>\n",
       "      <td>104</td>\n",
       "      <td>45</td>\n",
       "      <td>48</td>\n",
       "      <td>26</td>\n",
       "      <td>13</td>\n",
       "      <td>15</td>\n",
       "      <td>48</td>\n",
       "      <td>2610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>Reverse_ML</td>\n",
       "      <td>365</td>\n",
       "      <td>108</td>\n",
       "      <td>19</td>\n",
       "      <td>30</td>\n",
       "      <td>25</td>\n",
       "      <td>11</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>45</td>\n",
       "      <td>643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>Warehouse</td>\n",
       "      <td>24</td>\n",
       "      <td>20</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>35</td>\n",
       "      <td>21</td>\n",
       "      <td>32</td>\n",
       "      <td>16</td>\n",
       "      <td>14</td>\n",
       "      <td>22</td>\n",
       "      <td>132</td>\n",
       "      <td>346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Grand Total</td>\n",
       "      <td>3PL_ForwardML_ForwardML_TransportMarketPlace_N...</td>\n",
       "      <td>8178</td>\n",
       "      <td>4913</td>\n",
       "      <td>2804</td>\n",
       "      <td>2082</td>\n",
       "      <td>1615</td>\n",
       "      <td>1101</td>\n",
       "      <td>974</td>\n",
       "      <td>713</td>\n",
       "      <td>507</td>\n",
       "      <td>407</td>\n",
       "      <td>2366</td>\n",
       "      <td>25660</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                            SH  0 to 1  \\\n",
       "0                                                  3PL_Forward    1035   \n",
       "1                                                   ML_Forward     399   \n",
       "2                                                 ML_Transport     692   \n",
       "3                                       MarketPlace_Not_Packed      16   \n",
       "4                                               Non-Returnable    3566   \n",
       "5                                                       Others     944   \n",
       "6                                                  Reverse_3PL    1137   \n",
       "7                                                   Reverse_ML     365   \n",
       "8                                                    Warehouse      24   \n",
       "Grand Total  3PL_ForwardML_ForwardML_TransportMarketPlace_N...    8178   \n",
       "\n",
       "             1 to 2  2 to 3  3 to 4  4 to 5  5 to 6  6 to 7  7 to 8  8 to 9  \\\n",
       "0               603     463     323     233     118      27       7       4   \n",
       "1                83      44      26      25      14      11       7       4   \n",
       "2               487     355     308     297     216     218     205     128   \n",
       "3                 4       0       0       0       0       0       0       0   \n",
       "4              1862     807     541     398     287     214     154     135   \n",
       "5              1155     773     584     498     389     408     281     204   \n",
       "6               591     328     255     104      45      48      26      13   \n",
       "7               108      19      30      25      11      16      17       5   \n",
       "8                20      15      15      35      21      32      16      14   \n",
       "Grand Total    4913    2804    2082    1615    1101     974     713     507   \n",
       "\n",
       "             9 to 10  Days10+  Total  \n",
       "0                  5       27   2845  \n",
       "1                  4       31    648  \n",
       "2                106      366   3378  \n",
       "3                  0        1     21  \n",
       "4                100      402   8466  \n",
       "5                153     1314   6703  \n",
       "6                 15       48   2610  \n",
       "7                  2       45    643  \n",
       "8                 22      132    346  \n",
       "Grand Total      407     2366  25660  "
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SH"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "SH_1=SH.set_index('SH')\n",
    "SH_1.rename(index={(SH_1.index.values[9]):'Grand Total'},inplace=True)\n",
    "SH_1=SH_1.loc[['MarketPlace_Not_Packed', 'Warehouse','ML_Forward','ML_Transport','3PL_Forward','Reverse_ML','Reverse_3PL','Non-Returnable','Others', 'Grand Total']]\n",
    "SH_1=SH_1.reset_index()\n",
    "\n",
    "SH_Open_1=SH_Open.set_index('SH')\n",
    "SH_Open_1.rename(index={(SH_Open_1.index.values[9]):'Grand Total'},inplace=True)\n",
    "SH_Open_1=SH_Open_1.loc[['MarketPlace_Not_Packed', 'Warehouse','ML_Forward','ML_Transport','3PL_Forward','Reverse_ML','Reverse_3PL','Non-Returnable','Others', 'Grand Total']]\n",
    "SH_Open_1=SH_Open_1.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import date\n",
    "today=date.today()\n",
    "today=today.strftime(\"%d-%b-%Y\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Write all the dataframes (i.e. teamwise reports) to Excel file (to be attached in automailer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "exclefilename=\"Task_Pendency_Report \"+today+\".xlsx\"\n",
    "writer = pd.ExcelWriter(exclefilename, engine='xlsxwriter')\n",
    "\n",
    "\n",
    "# Write each dataframe to a different worksheet. \n",
    "SH_1.to_excel(writer, sheet_name='SH',index=False)\n",
    "SH_Open_1.to_excel(writer, sheet_name='SH',index=False, startcol=0,startrow=13)\n",
    "Incient_category.to_excel(writer, sheet_name='Incient_category',index=False)\n",
    "Task_category.to_excel(writer, sheet_name='Task_category',index=False)\n",
    "City_split.to_excel(writer, sheet_name='City_split',index=False)\n",
    "Final_8.to_excel(writer, sheet_name='Raw Data',index=False)\n",
    "\n",
    "# Close the Pandas Excel writer and output the Excel file.\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "    recipients= ['List of reciepients'\n",
    "    ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Send automailer with summary and details excel report"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Checking for Internet Connection\n",
      "\n",
      "Sending...\n",
      "Sent\n"
     ]
    }
   ],
   "source": [
    "    import mimetypes\n",
    "    from email.mime.multipart import MIMEMultipart\n",
    "    from email import encoders\n",
    "    from email.message import Message\n",
    "    from email.mime.audio import MIMEAudio\n",
    "    from email.mime.base import MIMEBase\n",
    "    from email.mime.image import MIMEImage\n",
    "    from email.mime.text import MIMEText\n",
    "    import datetime\n",
    "    import sys\n",
    "    import smtplib\n",
    "\n",
    "    print (\"Checking for Internet Connection\\n\")\n",
    "    username = \"username\"\n",
    "    password = \"password\"\n",
    "    server = smtplib.SMTP(\"smtp.gmail.com:587\")\n",
    "    server.starttls()\n",
    "    server.login(username,password)\n",
    "    emailfrom = \"email id\"\n",
    "\n",
    "\n",
    "    emailto=\", \".join(recipients)\n",
    "\n",
    "    msg = MIMEMultipart('alternative')\n",
    "\n",
    "\n",
    "    msg[\"From\"] = emailfrom\n",
    "    msg[\"To\"] = emailto\n",
    "    msg[\"Subject\"] = \"CC Task Pendency Report- \"+today\n",
    "\n",
    "\n",
    "    part2 = MIMEBase('application', \"octet-stream\")\n",
    "\n",
    "    part2.set_payload(open(\"Task_Pendency_Report \"+today+\".xlsx\", \"rb\").read())\n",
    "    encoders.encode_base64(part2)\n",
    "\n",
    "    part2.add_header('Content-Disposition', \"attachment\", filename=\"Task_Pendency_Report \"+today+\".xlsx\")\n",
    "    msg.attach(part2)\n",
    "\n",
    "    import io\n",
    "    str_io = io.StringIO()\n",
    "    SH_1.to_html(buf=str_io, classes='table table-striped', index = False)\n",
    "    html_str1 = str_io.getvalue()\n",
    "\n",
    "    str_io4 = io.StringIO()\n",
    "    SH_Open_1.to_html(buf=str_io4, classes='table table-striped',index = False)\n",
    "    html_str4 = str_io4.getvalue()\n",
    "\n",
    "    str_io2 = io.StringIO()\n",
    "    Incient_category_1.to_html(buf=str_io2, classes='table table-striped',index = False)\n",
    "    html_str2 = str_io2.getvalue()\n",
    "\n",
    "    str_io3 = io.StringIO()\n",
    "    Task_category_1.to_html(buf=str_io3, classes='table table-striped',index = False)\n",
    "    html_str3 = str_io3.getvalue()\n",
    "\n",
    "\n",
    "\n",
    "    email_content = \"\"\"\n",
    "    <head>\n",
    "    <meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\">\n",
    "    <title>test</title>\n",
    "    <style type=\"text/css\" media=\"screen\">\n",
    "    table{\n",
    "    background-color: #f7fdff;\n",
    "    empty-cells:hide;\n",
    "        border-collapse: collapse;\n",
    "        border-spacing: 2px;  \n",
    "    }\n",
    "    th  {background-color:#586266; color:white;text-align: center;}\n",
    "    td {\n",
    "        text-align: center;\n",
    "\n",
    "    }\n",
    "\n",
    "    </style>\n",
    "    </head>\n",
    "    <body>\n",
    "\n",
    "    <p>\n",
    "\n",
    "    >>>>>>>>>>AUTOGENERATED MAIL<<<<<<<<<<>>>>>>>>>><<<<<<<<<<\n",
    "    <br>\n",
    "    <br>\n",
    "\n",
    "    <strong> Hi All, \n",
    "    <br>\n",
    "\n",
    "    Please find the updated Task Pendency report for \n",
    "            \"\"\"+today+\"\"\".\n",
    "\n",
    "\n",
    "\n",
    "    </p>\n",
    "\n",
    "    <br>\n",
    "\n",
    "    <b>1. Overall Pendency:</b>\n",
    "    <br>\n",
    "\n",
    "\n",
    "    <br>\n",
    "\n",
    "    \"\"\"+ html_str1 +  \"\"\"\n",
    "\n",
    "    <br>\n",
    "\n",
    "    <b>1.1 Overall Pendency (Task Status - Open):</b>\n",
    "    <br>\n",
    "    <br>\n",
    "    \"\"\"+ html_str4 +  \"\"\"\n",
    "\n",
    "    <br>\n",
    "\n",
    "    <b>2. Top 10 Customer's Issue:</b>\n",
    "    <br>\n",
    "    <br>\n",
    "    \"\"\"+ html_str2 +  \"\"\"\n",
    "\n",
    "    <br>\n",
    "\n",
    "    <b>3. Top 10 Task Category:</b>\n",
    "    <br>\n",
    "    <br>\n",
    "    \"\"\"+ html_str3 +  \"\"\"\n",
    "\n",
    "    <br>\n",
    "    <br>\n",
    "    <br>\n",
    "    <br>\n",
    "\n",
    "    </body>\n",
    "    \"\"\"\n",
    "\n",
    "    HTML_BODY = MIMEText(email_content.encode('utf-8'), 'html','utf-8')\n",
    "    msg.attach(HTML_BODY)\n",
    "    server = smtplib.SMTP(\"smtp.gmail.com:587\")\n",
    "    server.starttls()\n",
    "    server.login(username,password)\n",
    "    print(\"Sending...\")\n",
    "    server.sendmail(emailfrom, recipients, msg.as_string())\n",
    "\n",
    "    server.quit()\n",
    "    print(\"Sent\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Send automailer to Escalations team sepaartely for high priority issues"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "Ed_1=Final_8[(~Final_8['escalated_to'].isnull())\n",
    "           &((~Final_8['SH'].isin(['Others','Non-Returnable']))\n",
    "               |(Final_8['Category Level 3'].isin(['Not Packed','Not Packed_WH'])))][['SH','Category Level 1','Category Level 2',\n",
    "                       'Category Level 3','escalated_to','escalated_on','Order_ID','Task ID','Task Status','Tracking Number','Entity_Id','Task Created Date','Ageing_in_days',\n",
    "                       'Ageing_bucket','zipcode','packet_return_id','courier_code','status_code','source_wh_id','city','tracking_number',\n",
    "                       'DC','assignment_date','incident','queue_name','Intercation_count_call','inapp_interactions','task_queue',\n",
    "                       'orderdetails_returnid','Total_interactions','address_type','shipment_status','dc_inscanned_date','PPMP/Vendor_Flex',\n",
    "                       'Issue Type 1','Issue Type 2','Issue Type 3']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "t=Ed_1[\"escalated_on\"].tolist()\n",
    "\n",
    "from datetime import datetime\n",
    "# In[20]:\n",
    "\n",
    "\n",
    "#calculate ageing\n",
    "i=0\n",
    "length=len(Ed_1[\"escalated_on\"])\n",
    "ageing_in_hours=[]\n",
    "c=datetime.now()\n",
    "while i < length:\n",
    "    j=(c-datetime.strptime(str(pd.to_datetime(t[i])),\"%Y-%m-%d %H:%M:%S\") ).total_seconds()/(86400)\n",
    "    ageing_in_hours.append(round(j,2))\n",
    "    i+=1\n",
    "Ed_1[\"Ageing_from_escalation\"]=ageing_in_hours\n",
    "\n",
    "\n",
    "Ed_1.loc[(Ed_1['Ageing_from_escalation']>10),'ED_bucket'] = 'Days10+'\n",
    "Ed_1.loc[(Ed_1['Ageing_from_escalation']<=10),'ED_bucket'] = '9 to 10'\n",
    "Ed_1.loc[(Ed_1['Ageing_from_escalation']<=9),'ED_bucket'] = '8 to 9'\n",
    "Ed_1.loc[(Ed_1['Ageing_from_escalation']<=8),'ED_bucket'] = '7 to 8'\n",
    "Ed_1.loc[(Ed_1['Ageing_from_escalation']<=7),'ED_bucket'] = '6 to 7'\n",
    "Ed_1.loc[(Ed_1['Ageing_from_escalation']<=6),'ED_bucket'] = '5 to 6'\n",
    "Ed_1.loc[(Ed_1['Ageing_from_escalation']<=5),'ED_bucket'] = '4 to 5'\n",
    "Ed_1.loc[(Ed_1['Ageing_from_escalation']<=4),'ED_bucket'] = '3 to 4'\n",
    "Ed_1.loc[(Ed_1['Ageing_from_escalation']<=3),'ED_bucket'] = '2 to 3'\n",
    "Ed_1.loc[(Ed_1['Ageing_from_escalation']<=2),'ED_bucket'] = '1 to 2'\n",
    "Ed_1.loc[(Ed_1['Ageing_from_escalation']<=1),'ED_bucket'] = '0 to 1'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "Task_category_ED=Ed_1.pivot_table(index=['Category Level 3'], values=['Task ID'], columns='Ageing_bucket', aggfunc=pd.Series.nunique).fillna(0).astype(np.int64)\n",
    "Task_category_ED.columns=Task_category_ED.columns.get_level_values(1)\n",
    "Task_category_ED=Task_category_ED.rename_axis(['Category Level 3']).reset_index(level=0)\n",
    "Task_category_ED['Total']=Task_category_ED.sum(numeric_only=True, axis=1)\n",
    "Task_category_ED.sort_values(by='Total', ascending=False,inplace=True)\n",
    "Task_category_ED.to_csv('Task_category_ED.csv', index=False)\n",
    "Task_category_ED=pd.read_csv('Task_category_ED.csv')\n",
    "Task_category_ED_1=Task_category_ED[~Task_category_ED['Category Level 3'].isin(['Exchange Approvals', \"Pick up Approvals'\"])][:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "SH_ED=Ed_1.pivot_table(index=['escalated_to'], values=['Task ID'], columns='Ageing_bucket', aggfunc=pd.Series.nunique).fillna(0).astype(np.int64)\n",
    "SH_ED.columns=SH_ED.columns.get_level_values(1)\n",
    "SH_ED=SH_ED.rename_axis(['Task_Queue']).reset_index(level=0)\n",
    "SH_ED['Total']=SH_ED.sum(numeric_only=True, axis=1)\n",
    "SH_ED.to_csv('SH_ED_ED.csv', index=False)\n",
    "SH_ED=pd.read_csv('SH_ED_ED.csv')\n",
    "SH_ED.loc['Grand Total']=SH_ED.sum( axis=0)\n",
    "SH_ED=SH_ED.set_index('Task_Queue')\n",
    "SH_ED.rename(index={(SH_ED.index.values[len(SH_ED)-1]):'Grand Total'},inplace=True)\n",
    "SH_ED.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "SH_ED_2=Ed_1.pivot_table(index=['SH'], values=['Task ID'], columns='ED_bucket', aggfunc=pd.Series.nunique).fillna(0).astype(np.int64)\n",
    "SH_ED_2.columns=SH_ED_2.columns.get_level_values(1)\n",
    "SH_ED_2=SH_ED_2.rename_axis(['SH']).reset_index(level=0)\n",
    "SH_ED_2['Total']=SH_ED_2.sum(numeric_only=True, axis=1)\n",
    "SH_ED_2.to_csv('SH_ED_2.csv', index=False)\n",
    "SH_ED_2=pd.read_csv('SH_ED_2.csv')\n",
    "SH_ED_2.loc['Grand Total']=SH_ED_2.sum( axis=0)\n",
    "SH_ED_2=SH_ED_2.set_index('SH')\n",
    "SH_ED_2.rename(index={(SH_ED_2.index.values[len(SH_ED_2)-1]):'Grand Total'},inplace=True)\n",
    "SH_ED_2.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "SH_ED_1=Ed_1.pivot_table(index=['escalated_to'], values=['Task ID'], columns='ED_bucket', aggfunc=pd.Series.nunique).fillna(0).astype(np.int64)\n",
    "SH_ED_1.columns=SH_ED_1.columns.get_level_values(1)\n",
    "SH_ED_1=SH_ED_1.rename_axis(['Task_Queue']).reset_index(level=0)\n",
    "SH_ED_1['Total']=SH_ED_1.sum(numeric_only=True, axis=1)\n",
    "SH_ED_1.to_csv('SH_ED_1.csv', index=False)\n",
    "SH_ED_1=pd.read_csv('SH_ED_1.csv')\n",
    "SH_ED_1.loc['Grand Total']=SH_ED_1.sum( axis=0)\n",
    "SH_ED_1=SH_ED_1.set_index('Task_Queue')\n",
    "SH_ED_1.rename(index={(SH_ED_1.index.values[len(SH_ED_1)-1]):'Grand Total'},inplace=True)\n",
    "SH_ED_1.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "today_ED=str(date.today())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "exclefilename=\"ED_Pendency \"+today_ED+\".xlsx\"\n",
    "writer = pd.ExcelWriter(exclefilename, engine='xlsxwriter')\n",
    "SH_ED.to_excel(writer, sheet_name='Summary',index=False,startcol=0,startrow=2)\n",
    "worksheet=writer.sheets['Summary']\n",
    "worksheet.write('A1', 'Ageing_from_creation')\n",
    "worksheet.write('A11', 'Ageing_from_escalation')\n",
    "\n",
    "SH_ED_1.to_excel(writer, sheet_name='Summary',index=False,startcol=0,startrow=12)\n",
    "SH_ED_2.to_excel(writer, sheet_name='Stakeholder_Cut',index=False)\n",
    "Task_category_ED.to_excel(writer, sheet_name='Task_category_ED',index=False)\n",
    "Ed_1.to_excel(writer, sheet_name='Raw Data',index=False)\n",
    "\n",
    "\n",
    "# Close the Pandas Excel writer and output the Excel file.\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "recipients_2=['List of recipients'']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Checking for Internet Connection\n",
      "\n",
      "Sending...\n",
      "Sent\n"
     ]
    }
   ],
   "source": [
    "import mimetypes\n",
    "from email.mime.multipart import MIMEMultipart\n",
    "from email import encoders\n",
    "from email.message import Message\n",
    "from email.mime.audio import MIMEAudio\n",
    "from email.mime.base import MIMEBase\n",
    "from email.mime.image import MIMEImage\n",
    "from email.mime.text import MIMEText\n",
    "import datetime\n",
    "import sys\n",
    "import smtplib\n",
    "\n",
    "print (\"Checking for Internet Connection\\n\")\n",
    "username = \"username\"\n",
    "password = \"pswrd\"\n",
    "server = smtplib.SMTP(\"smtp.gmail.com:587\")\n",
    "server.starttls()\n",
    "server.login(username,password)\n",
    "emailfrom = \"cemail id\"\n",
    "\n",
    "emailto=\", \".join(recipients_2)\n",
    "msg = MIMEMultipart('alternative')\n",
    "\n",
    "\n",
    "msg[\"From\"] = emailfrom\n",
    "msg[\"To\"] = emailto\n",
    "msg[\"Subject\"] = \"SCM ED Task Pendency Report- \"\n",
    "\n",
    "\n",
    "part2 = MIMEBase('application', \"octet-stream\")\n",
    "\n",
    "part2.set_payload(open(\"ED_Pendency \"+today_ED+\".xlsx\", \"rb\").read())\n",
    "encoders.encode_base64(part2)\n",
    "\n",
    "part2.add_header('Content-Disposition', \"attachment\", filename=\"ED_Pendency \"+today_ED+\".xlsx\")\n",
    "msg.attach(part2)\n",
    "\n",
    "\n",
    "import io\n",
    "str_io = io.StringIO()\n",
    "SH_ED_2.to_html(buf=str_io, classes='table table-striped', index = False)\n",
    "html_str3 = str_io.getvalue()\n",
    "\n",
    "str_io = io.StringIO()\n",
    "SH_ED.to_html(buf=str_io, classes='table table-striped', index = False)\n",
    "html_str1 = str_io.getvalue()\n",
    "\n",
    "str_io = io.StringIO()\n",
    "SH_ED_1.to_html(buf=str_io, classes='table table-striped', index = False)\n",
    "html_str2 = str_io.getvalue()\n",
    "\n",
    "str_io4 = io.StringIO()\n",
    "Task_category_ED_1.to_html(buf=str_io4, classes='table table-striped',index = False)\n",
    "html_str4 = str_io4.getvalue()\n",
    "\n",
    "\n",
    "email_content = \"\"\"\n",
    "<head>\n",
    "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\">\n",
    "<title>test</title>\n",
    "<style type=\"text/css\" media=\"screen\">\n",
    "table{\n",
    "background-color: #f7fdff;\n",
    "empty-cells:hide;\n",
    "   border-collapse: collapse;\n",
    "   border-spacing: 2px;\n",
    "   padding: 15px;\n",
    "}\n",
    "th  {background-color:#586266; color:white;text-align: center;}\n",
    "td {\n",
    "   text-align: center;\n",
    "   padding: 7px;\n",
    "   \n",
    " \n",
    "}\n",
    "\n",
    "</style>\n",
    "</head>\n",
    "<body>\n",
    "\n",
    "<p>\n",
    "\n",
    ">>>>>>>>>>AUTOGENERATED MAIL<<<<<<<<<<>>>>>>>>>><<<<<<<<<<\n",
    "<br>\n",
    "<br>\n",
    "\n",
    "<strong> Hi All,\n",
    "<br>\n",
    "\n",
    "Please find the Updated SCM ED Task Pendency for\n",
    "       \"\"\"+today+\"\"\".\n",
    "       \n",
    "</p>\n",
    "\n",
    "<br>\n",
    "\n",
    "<b>1. Overall SH ED Pendency (from escalation):</b>\n",
    "<br>\n",
    "\n",
    "\n",
    "<br>\n",
    "\n",
    "\"\"\"+ html_str3 +  \"\"\"\n",
    "\n",
    "<br>\n",
    "<b>2. Overall ED Pendency (from task creation):</b>\n",
    "<br>\n",
    "\n",
    "\n",
    "<br>\n",
    "\n",
    "\"\"\"+ html_str1 +  \"\"\"\n",
    "\n",
    "<br>\n",
    "<b>3. ED Pendency (from escalation):</b>\n",
    "<br>\n",
    "\n",
    "\n",
    "<br>\n",
    "\n",
    "\"\"\"+ html_str2 +  \"\"\"\n",
    "\n",
    "<br>\n",
    "\n",
    "<b>4. Top 10 Task Categories:</b>\n",
    "<br>\n",
    "<br>\n",
    "\"\"\"+ html_str4 +  \"\"\"\n",
    "\n",
    "<br>\n",
    "\n",
    "<br>\n",
    "\n",
    "\n",
    "<br>\n",
    "<br>\n",
    "<br>\n",
    "<br>\n",
    "\n",
    "</body>\n",
    "\"\"\"\n",
    "\n",
    "HTML_BODY = MIMEText(email_content.encode('utf-8'), 'html','utf-8')\n",
    "\n",
    "msg.attach(HTML_BODY)\n",
    "\n",
    "\n",
    "server = smtplib.SMTP(\"smtp.gmail.com:587\")\n",
    "\n",
    "server.starttls()\n",
    "\n",
    "server.login(username,password)\n",
    "print(\"Sending...\")\n",
    "server.sendmail(emailfrom, recipients_2, msg.as_string())\n",
    "\n",
    "\n",
    "server.quit()\n",
    "print(\"Sent\")"
   ]
  },
  {
   "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}