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()

Data Science Plot example with Real data Complete with Code

Python Data Science Learning

This post is dedicated to the new developer which are in starting phase Data Science Learning.

In this Post i have develop a graph of a organization  for analysis of the costing of organization. In Starting Phase i have used only two paramaters of Coasting one is Sale and Second is Purchase. Both Sale and Purchase are always constant part of any organization whether it is production or service or any kind of organization.

I will Explain the Complete Topic in following Points-

Step 1- Import the Pandas Dataframe Library.

# parameters and libraray which is imported 

# pandas for dataframe

import pandas as pd

#pyodbc for sql server 

import pyodbc 

Step 2- Define the server Name and location and database name

DB = {

            'servername': '103.27.100.100',

            'database': 'DIZSWEB'

          }

Step 3- Create the Connection String to database

# create the connection with SQL Server

conn = pyodbc.connect(

   'DRIVER = {SQL Server};

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

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

    UID = DIZSWEB;

    PWD = DIZSWEB;

    Trusted_Connection = no'

)

Step 4- Create the Query

# Sales Query of the organization

Sales = "SELECT CO_CODE, 

               Month(INV_DATE) as INV_DATE,                                                   sum(Acc_Total_Amount) 

               FROM invoice_mast 

               where CO_CODE='DIZSWEB' and                                                   Acc_Total_Amount>0 

               group by CO_CODE, 

               Month(INV_DATE) 

               order by Month(INV_DATE);"

# Purchase Query of the organization

Purchase = "SELECT CO_CODE,

                    Month(Pur_Voucher_Date) as Pur_Voucher_Date,

                    sum(Acc_Main_Total_Amount) 

                    FROM Purchase_Main_mast 

                    where CO_CODE='DIZSWEB' 

                    group by CO_CODE, 

                    Month(Pur_Voucher_Date) 

                    order by Month(Pur_Voucher_Date);"

Step 5- Reading of SQL Query into DataFrame

#Reading the Sales sql query data into pandas datafrme

Sales_df = pd.read_sql(Sales, conn)

#Reading the Purchase sql query data into pandas datafrme

Purchase_df = pd.read_sql(Purchase, conn)

Step 6- Testing of Data

   To Test the data of DataFrame following two cmd is used 

       # Print the first 5 rows of Sales_df

          Sales_df.head(5)

       # Then Press Shift+Enter

      # Print the first 5 rows of Purchase_df

         Purchase_df.head(5)

      # Then Press Shift+Enter

Step 7- Renamaing of Column

  Column Renaming will help us to merge the data into a single DataFrame for analysis.

# Then rename the Column Name 

    Sales_df.rename(columns={

                                  "":"CR_Amount",

                                  "CO_CODE":"Comapany",                                                                "INV_DATE":"Date"

                                   }, inplace = True);

# Test Again 

Sales_df

# Then rename the Column Name 

Purchase_df.rename(columns=

                                 {

                                   "":"DR_Amount",                                                                               "CO_CODE":"Comapany", 

                                   "Pur_Voucher_Date":"Date"

                                  }, inplace = True);

# Test Again

Purchase_df

Step 8- Merging of datainto a single DataFrame

# Now Join the two dataframe in this tutotial i have merge the data frame on left outer join of sql type 

result = pd.merge

               ( 

                Sales_df, 

                Purchase_df, 

                how='left', 

                on=['Date','Comapany']

               )

# I have store the data into a new varible that is result

# Print the result again Shift+Enter

result

Step 9- Importing of MatplotLib for graph Ploting

# Now i am goin to plot the data for this i am importing two lybrary

# matplotlib pyplot

# dates beacuse my data will be ploated according to the baseis of date

        import matplotlib.pyplot as plt

        import matplotlib.dates as dates

        %matplotlib inline

Step 10- Defining the Axis and Parametere and variable

# define of combine dataframe

df=pd.DataFrame(

                            { 

                             'x': result.Date, 

                             'y1': result.CR_Amount, 

                             'y2': result.DR_Amount

                             }

                             )

           plt.plot( 

                       'x', 'y1', 

                       data=df, 

                       marker='o', 

                       markerfacecolor='blue', 

                       markersize=1, 

                       color='skyblue', 

                       linewidth=1

                      )

           plt.plot( 

                      'x', 'y2', 

                      data=df, 

                      marker='', 

                      color='olive', 

                      linewidth=1

                      )

plt.legend()

Step 11- Final Plot of data

After analysis of the graph i found that in starting of Indian financial year sell of the organization was on a constant pace and their was enough profit to the organizasation on the basis of current data but as the session passes the sale of the organization goes downward. So now we have to analysis the factor of  sales degradation it could be the government, environment, company product issues, market issue, Season is not suitable for the product.

Competor increament, Costing is increasing because of that orzanization reduce production.

Data Science Sample example for Beginner

Laerning Exapmple of Data Science

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