crm duplicates_20sep_ruth.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9b850784",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import openpyxl\n",
    "pd.set_option('display.max_rows', None)\n",
    "import datetime\n",
    "import os\n",
    "import re\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "3f8a78b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ruth\n",
    "\n",
    "path_input = r'C:\\Users\\YeoRuth\\OneDrive - GC International AG\\dataIntegrity\\data_input\\\\'\n",
    "path_output = r'C:\\Users\\YeoRuth\\OneDrive - GC International AG\\dataIntegrity\\data_output\\\\'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "95a3c6ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "def dataRun(df):\n",
    "    df['Sales Order Date']=pd.to_datetime(df['Sales Order Date'], format=\"%d-%m-%Y\")\n",
    "    # extract duplicates\n",
    "    df1 = df[df.duplicated(subset=['Account Code','SKU Code'],keep='first')]\n",
    "    # extract duplicates keep all\n",
    "    new = pd.DataFrame()\n",
    "    for a,s in zip(df1['Account Code'], df1['SKU Code']):\n",
    "        x = df[(df['Account Code']==a) & (df['SKU Code']==s)]\n",
    "        new = pd.concat([new,x])\n",
    "    # create date format in new columns \"dateDelta\"\n",
    "    new['dateDelta']=[datetime.date(i.year,i.month,i.day) for i in new['Sales Order Date']]\n",
    "    # sort the dataframe\n",
    "    new = new.sort_values(by=['Account Code','dateDelta'])\n",
    "    # create new dataframe to store duplicates which are 30 days apart\n",
    "    new2=pd.DataFrame()\n",
    "\n",
    "    # create list of unique account codes\n",
    "    accCodes = new['Account Code'].unique()\n",
    "\n",
    "    # iterates over dataframe \"new\" consisting of full duplicated data\n",
    "    for a in accCodes:\n",
    "        new1=new[new['Account Code']==a].reset_index(drop=True)\n",
    "        length = len(new1)\n",
    "\n",
    "        if length ==2:\n",
    "\n",
    "            d0=new1.loc[0,'dateDelta']\n",
    "            d1=new1.loc[1,'dateDelta']\n",
    "            delta = d1-d0\n",
    "            if delta.days <= 30:\n",
    "#                 print(f\"< 30days,{length},{a}\")\n",
    "                new2=pd.concat([new2,new1])\n",
    "\n",
    "        elif length==3:\n",
    "\n",
    "            d0=new1.loc[0,'dateDelta']\n",
    "            d1=new1.loc[1,'dateDelta']\n",
    "            delta = d1-d0\n",
    "            if delta.days > 30:\n",
    "                new2=pd.concat([new2,new1.loc[0:1]])\n",
    "            d0=new1.loc[1,'dateDelta']\n",
    "            d1=new1.loc[2,'dateDelta']\n",
    "            delta = d1-d0\n",
    "            if delta.days > 30:\n",
    "                new2=pd.concat([new2,new1.lc[1:2]])\n",
    "                \n",
    "    \n",
    "    return new2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "a1688b99",
   "metadata": {},
   "outputs": [],
   "source": [
    "# churning out individual excel files of duplicated sales data\n",
    "os.chdir(path_input)\n",
    "list_input = os.listdir()\n",
    "for i in list_input:\n",
    "    if re.search(r'^Transfer',i):\n",
    "        df = pd.read_excel(i, header=4)\n",
    "        cty_list = df.Country.unique()\n",
    "        for cty in cty_list:\n",
    "            x = df[df.Country==cty]\n",
    "            result = dataRun(x)\n",
    "            result.to_excel(path_output+f'duplicated_sales_{cty}.xlsx', index=False)\n",
    "            \n",
    "# consolidating individual excel files of duplicated sales data into one file\n",
    "\n",
    "os.chdir(path_output)\n",
    "list_output = os.listdir()\n",
    "new=pd.DataFrame()\n",
    "for m in list_output:\n",
    "    if re.search(r'^duplicated',m):\n",
    "        df1 = pd.read_excel(m)\n",
    "        df1['Sales Order Date'] = [i.strftime(\"%Y-%m-%d\") for i in df1['Sales Order Date']]\n",
    "        new = pd.concat([new,df1])\n",
    "        \n",
    "        # remove individual files\n",
    "        os.remove(m)\n",
    "        \n",
    "# determining the month of report\n",
    "new1 = new.copy()\n",
    "new1['Sales Order Date']=pd.to_datetime(new1['Sales Order Date'])\n",
    "new1['Sales Order Month']=[i.strftime(\"%b\") for i in new1['Sales Order Date']]\n",
    "mth = new1['Sales Order Month'].unique()\n",
    "mth_str = mth[1]+' & '+mth[0]\n",
    "new.to_excel(path_output+f'crm_duplicated_sales_{mth_str}.xlsx', index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "487f6e3c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5c319a52",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4bf3bd3b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f2201a54",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b9fade66",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc3b2d6e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd34b5e7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8a74dd7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "044adde5",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3345468",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}