Data Analysis of Purchase Order of an Organization with AI

In this post, I will explain the  Python data Analysis Concept with AI Question with Live Data and Code and Data.

Topic Main Points

 1- Python
 2- Pandas
 3- Numpy
 4- MatplotLib
 5- PO of Industrial Organization.
 6- AI of Industrial Purchase Question of a perticular Session.

Now lets Start, I will explain the AI Question which could be raised against a perticular data set  PO against a perticular session.

  

I tried top explain  all the Topics Sequenctillay, what i bignner Data Scientiest need to know.

Step 1:- Creating the first Data Frame of  Main PO Details 

# query Master Reading

Pur_order_mast = "SELECT  

                               URN_NO,                                                                  convert(nvarchar,pur_order_date,103)as Date,

 Pur_Order_No as [Order_No],

supp_code as [Sup_Code] 

from purchase_order_mast  

where (PO_Status='Approved' or PO_Status='Not Approved')  

and co_code like '%a%'" 

Pur_df_1 = pd.read_sql(Pur_order_mast, conn)

# Testing of Data

Pur_df_1.head(5)

Step 2:- Create Second Data Frame for PO Details Part

# Query for Details Reading

Pur_order_det = "SELECT URN_NO,it_code,alt_qty as [Item_qty],alt_rate as Rate from purchase_order_det  where (PO_Status='Approved' or PO_Status='Not Approved')and co_code like '%a%'" 

Pur_df_2 = pd.read_sql(Pur_order_det, conn)

# Testing of Data

Pur_df_2.head(5)

Step 3:- Create Third Data Frame of Itemmaster

# Query for Itemmaster Reading

Itemmaster = "SELECT it_code,alt_name as [Item_Name] from itemmaster  where (item_Status='Approved' or item_Status='Not Approved')and co_code like '%a%'" 

Itemmaster = pd.read_sql(Itemmaster, conn)

Step 4:- Create Fourth Data Frame of Supplier Master

# Query for Itemmaster Reading

AccountMaster = "SELECT Sr_no,ac_name from acct_mast  where (acc_Status='Approved' or acc_Status='Not Approved')and co_code like '%a%'" 

AccountMaster = pd.read_sql(AccountMaster, conn)

#Testing of DATA

AccountMaster.head(5)

Step 5:- Merging of all dataframe object into single Data Frame

# Merging of data Sequentially and  Making a useful data

# First we will create master Data Frame of from the available 

# dataframe objects

# first we will merge details and master part

Final_order_details = pd.merge(Pur_df_2,Pur_df_1)

# Purchase order main data and Purchase order details data merging

Final_order_details.head(5)

# Now we will merge the data of accounts name to the current frame

Final_order_details = pd.merge(Final_order_details,AccountMaster, left_on ='Sup_Code', right_on='Sr_no')

# Now we will merge the item name to the data set

Final_order_details = pd.merge(Final_order_details,Itemmaster, on ='it_code')

Final_order_details.head(5)

Step 6:- Function to remove duplicate rows 

# function to remove duplicates key from a DataFrame 

def getDuplicateColumns(df):

    '''

    Get a list of duplicate columns.

    It will iterate over all the columns in dataframe and find the columns whose content are duplicate.

    :param df: Dataframe object

    :return: List of columns whose contents are duplicates.

    '''

    duplicateColumnNames = set()

    # Iterate over all the column in DataFrame

    for x in range(df.shape[1]):

        # Select column at xth index.

        col = df.iloc[:, x]

        # Iterate over all the columns in DataFrame from (x+1)th index till end

        for y in range(x + 1, df.shape[1]):

            # Select column at yth index.

            otherCol = df.iloc[:, y]

            # Check if two columns at x 7 y index are equal

            if col.equals(otherCol):

                duplicateColumnNames.add(df.columns.values[y])

    return list(duplicateColumnNames)

#Final_order_details.drop (["it_code","Sup_Code","Sr_no","Item_Name_x"], axis = 1)

Final_order_details = Final_order_details.drop

(columns=getDuplicateColumns(Final_order_details))

# Finally print the Usable DataFrame and Test 

#and check that only useful data is ready for anylysis.

Final_order_details.head(2)

Now from the available data we have to answer the following Question

# Question 1:- What is the Monthely Costing on Purchase.

Complete Code 

# parameters

import pandas as pd

import pyodbc

from pandas import Series

DB = {'servername': '103.27.100.100',

      'database': 'dizsweb'

     }

# create the connection

conn = pyodbc.connect('DRIVER={SQL Server};

SERVER=' + DB['servername'] + ';

DATABASE=' + DB['database'] + ';

UID= dizsweb;PWD= dizsweb

;Trusted_Connection=no')

# function to remove duplicates key from a DataFrame

def getDuplicateColumns(df):

    '''

    Get a list of duplicate columns.

    It will iterate over all the columns in dataframe and find the columns whose content are duplicate.

    :param df: Dataframe object

    :return: List of columns whose contents are duplicates.

    '''

    duplicateColumnNames = set()

    for x in range(df.shape[1]):

        # Select column at xth index.

        col = df.iloc[:, x]

        # Iterate over all the columns in DataFrame from (x+1)th index till end

        for y in range(x + 1, df.shape[1]):

            otherCol = df.iloc[:, y]

            if col.equals(otherCol):

                duplicateColumnNames.add(df.columns.values[y])

    return list(duplicateColumnNames)

Pur_order_mast = "SELECT URN_NO,pur_order_date as Date,Pur_Order_No as [Order_No],supp_code as [Sup_Code] from purchase_order_mast  where (PO_Status='Approved' or PO_Status='Not Approved')  and co_code like '%a%'"

Pur_df_1 = pd.read_sql(Pur_order_mast, conn)

Pur_df_1.head(5)

Pur_order_det = "SELECT URN_NO,it_code,alt_qty as [Item_qty],alt_rate as Rate from purchase_order_det  where (PO_Status='Approved' or PO_Status='Not Approved')and co_code like '%a%'"

Pur_df_2 = pd.read_sql(Pur_order_det, conn)

Pur_df_2.head(5)

Itemmaster = "SELECT it_code,alt_name as [Item_Name] from itemmaster  where (item_Status='Approved' or item_Status='Not Approved')and co_code like '%a%'"

Itemmaster = pd.read_sql(Itemmaster, conn)

Itemmaster.head(5)

AccountMaster = "SELECT Sr_no,ac_name from acct_mast  where (acc_Status='Approved' or acc_Status='Not Approved')and co_code like '%a%'"

AccountMaster = pd.read_sql(AccountMaster, conn)

Final_order_details = pd.merge(Pur_df_2,Pur_df_1)

Final_order_details.head(5)

Final_order_details = pd.merge(Final_order_details,AccountMaster, left_on ='Sup_Code', right_on='Sr_no')

Final_order_details = pd.merge(Final_order_details,Itemmaster, on ='it_code')

Final_order_details['Amount'] =  Final_order_details['Item_qty']* Final_order_details['Rate']

Final_order_details.head(5)

#Final_order_details.drop(["it_code","Sup_Code","Sr_no","Item_Name_x"], axis = 1)

Final_order_details = Final_order_details.drop(columns=getDuplicateColumns(Final_order_details))

Final_order_details.head(5)

Final_order_details.head(5)

Final_order_details.head(2)

Monthely_purchase_costing = Final_order_details[['Date','Amount']]

Monthely_purchase_costing.info()

Monthely_purchase_costing['Date'] = pd.to_datetime(Monthely_purchase_costing['Date'],format='%y-%m-%d')

Monthely_purchase_costing.head(8)

Monthely_purchase_costing.info()

Monthely_purchase_costing2 = Monthely_purchase_costing.groupby([pd.Grouper(key='Date', freq='M')]).agg({'Amount':sum})

Monthely_purchase_costing2

Monthely_purchase_costing2.info()

Monthely_purchase_costing["Year"],Monthely_purchase_costing["Month"],Monthely_purchase_costing["Day"] = Monthely_purchase_costing.Date.dt.year,Monthely_purchase_costing.Date.dt.month,Monthely_purchase_costing.Date.dt.day

Monthely_purchase_costing_OK=Monthely_purchase_costing

Monthely_purchase_costing_OK

Month_costing = Monthely_purchase_costing_OK.groupby(['Month']).sum()

import matplotlib.dates as dates

%matplotlib inline

df=pd.DataFrame(

    { 'x': Month_costing1.index,

     'y1': Month_costing1.Amount

    })

plt.plot( 'x', 'y1', data=df, marker='o', markerfacecolor='blue', markersize=1, color='skyblue', linewidth=1)

plt.legend()

Purchase To Pay - New Item Creation

Item Opening in any Organization is very important phase because if proper structure and process is not followed then a single item can have multiple name and multiple location of storage and different rate and different value. Matching of such items can not be easy always.

Demerits of Wrong Item Name and Code opening.

1- Recourse Miss Usage- 

Same Item Can have multiple Storage Location

2- Loss To Company- 

Item exists in Store with XYZ name but the user          department calls the Item with ABC Name. So in          Emergency case already available item will be indent or purchased again.

   

3- Costing –

Same item with different name and rate will effect the                  organization costing.

   4- Quality - 

        Same Item with different name and purchased from                          different Supplier will have different quality                                      parameters.

   5- User confusion-

        A old user may be familiar to the name of item but a new                   joined user will not able to distinguish the products.

   6- Taxes Losses-

        U supplier may sell the item in X taxation's and V                              supplier  may sell the item in Y taxation's. So it will be 

        loss to company and comes in category of fraud in   b                        Government.

Merits of Single Item Name In Organization.

   1- Zero Recourse Miss Usage- 

        In Emergency condition, Item can be track in various                        location of organization

   2- Zero Loss to Company- 

        Item have the Common name with Standard and all user                  will be familiar with that, Because Standard is followed                    in Name Creation So new user will also be familiar with                  that.

   3- Costing -

           Single item Costing will be always easy

   4- Quality - 

       Standard parameter is defined for item So quality                             compromises is not possible at the time of purchase.

   5- User confusion-

       Standard is followed so confusion to user will be Zero.

   6- Taxes Losses-

        Standard is followed so no taxes issues will occur. 

Types of Items in Organization with respect to Purchase and Store.

1- Consumables/ Salable Items.

2- Service Item

3- Asset Item.

Consumables/ Salable Items.

Those which effect our Stock directly.

Those items which can be used once only are called Consumables Items. 

Those items which is sold is called salable Items.

 

Consumables Items are various type such as:-

   1- Raw Material

   2- Electrical Machinery

   3- Mechanical Machinery

   4- Office 

   5- Computer

   6- Cleaning

Theses are some categories even the users or respected organization can distribute the items into various subgroup according to usage to specify the location and consumption of item and make costing of the item easier.

   Example- Raw Material 

        Definition - All those material on which by doing some                     mac hing or performing some process a new Item is produced is called RAW Material.

   
   Now If i am working in Organization where Finish Good is           produced from 5 Separate Semi-Finish Good then fro these 5 different Semi- Finish Goods we have different RAW Material So we can separate our RAW Material on the basis of process or naming of machining or something else.
  Advantage of doing this will be help to analysis the costing of different process and optimization of Utilization.

Service Items- 

Those items which do not have physical existence but have cost effect on company.

   Like:- Employee Salary, 

              Machine Repairing Service, 

              Software Services.

              Third Party Audit Service.

Asset Items:-  

Those Items which have its Value in market and increases or decreases its cost with time. 

     Like:- Land of Company,

                Machines.

Item Master Creation Process

Now the Important part of this topic is what are the points which should be in consideration before creating the item master of any organization.

Item Master of any Organization may change according to work and process. But some important points should be consideration.

Primary (Mandatory)

   1- Item Code 

   2- Item Name (Standard Process)  

   3- Unit of Measurement

   4- Type of Item(Con, Service, Asset)

   5- Tax

Secondary

   1- Group of Item 

   2- Local Name of Item

   3- Secondary Measurement Unit.

   4- Unit Price

   5- Storage Location/Usage Location

                               

Tertiary

   1- Weight

   2- Minimum Stock Availability

   3- Maximum Stock  Availability

   4- Supplier Part No/ Customer Part No/ Machine Name

   5- Costing Center

   6- Peak usage Month / quarter

Arduino Uno Code for E- Dustbin

E - Dustibin is the future of world, So every developing country is now finding the ways of collecting the garbage according to their category, like plastic garbage , metallic garbage, or biodegardable and not degradable items saparately.
So to have this thing in mind we have develop a science project of moving and auto open close and E- dustbin. Which will move at predefine path and collect the garbage and also automatically opens when user comes in near to that. 

#include <Servo.h>

Servo myservo;
int arr[9];

int punnu; 

void setup()
{
  Serial.begin(9600);
  myservo.attach(9);
  pinMode(8,INPUT);
   pinMode(10,INPUT);
    pinMode(11,INPUT);
}
int sawan()
{
int sensorValue1 = analogRead(A0);
int sensorValue2 = analogRead(A1);
int sensorValue3 = analogRead(A2);
return sensorValue1+sensorValue2+sensorValue3/6;

  }
void loop()
{
  int sum=0;

  for(int i=0;i<9;i++)
    {
         arr[i]=sawan();
    }
     for(int i=0;i<9;i++)
      {
         sum=sum+arr[i];
         sum=sum/9;
         punnu=sum;
    }
 chauhan: 
  int sensorValue1 = analogRead(A0);
  int sensorValue2 = analogRead(A1);
  int sensorValue3 = analogRead(A2);
    if((sensorValue1>punnu)||(sensorValue2>punnu)||(sensorValue3>punnu))
    {
      for (int pos = 0; pos <= 180; pos += 1)
      {
        myservo.write(180);           
        delay(500);                     
      }
    }
  else
   {
     for (int pos = 180; pos >= 0; pos -= 1)
     {
       myservo.write(pos);           
      delay(5000);                   
     }
   }
goto chauhan;
}