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