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.
No comments:
Post a Comment