def PortSupPlot(fld, clr, ttl):
"""builodw charts
fld = field
clr = colour
ttl = title
"""
UpCase = fld.title()
df = u.run_sql_query(
u.full_path('Ann_report_sql', '{} frequented.sql'.format(fld)),
i.shared)
def title(tr):
return tr.title()
df[fld] = df[fld].apply(title)
df['combined'] = pd.concat([
df['{} with count GT 10'.format(fld)].dropna(),
df['{} with count LT 10'.format(fld)].dropna()
]).reindex_like(df)
df = df.sort_values(['combined'], ascending=1)
df = df.drop(
['{} with count GT 10'.format(fld), '{} with count LT 10'.format(fld)],
axis=1)
df = df.sort_values(by='combined', ascending=False)
df = df.set_index(fld, drop=True)
fig, ax = plt.subplots(figsize=(10, 3))
df[:20].plot(kind='bar', color=clr, ax=ax, legend=False, alpha=0.75)
ax.spines['bottom'].set_color(clr)
ax.spines['left'].set_color(clr)
ax.spines['top'].set_color('#ffffff')
ax.spines['right'].set_color('#ffffff')
ax.spines['left'].set_lw(0.5)
ax.spines['bottom'].set_lw(0.5)
ax.tick_params(axis='y', colors='#282f65', labelsize=10)
ax.tick_params(axis='x', colors='#282f65', labelsize=10)
ax.set_ylabel(ttl, color='#282f65')
ax.set_xlabel('')
plt.savefig('plots/{} Visited.png'.format(UpCase),
bbox_inches='tight',
transparent=True)
import utils as u
import matplotlib.pyplot as plt
import matplotlib.patches as mpatches
import matplotlib.font_manager as font_manager
import pandas as pd
from math import pi
pd.options.mode.chained_assignment = None
pd.set_option("display.max_rows", 999)
pd.set_option("display.max_columns", 20)
date_from = '2018-01-01'
date_to = '2018-12-31'
user = '******'
client = "TSAKOS SHIPPING"
shared = {"df": date_from, "dt": date_to, "cl": client, "u": user}
df = u.run_sql_query(u.full_path("Ann_report_sql", "spider_plot.sql"), shared)
df = df.drop_duplicates(subset='id', keep='last').reset_index(drop=True)
df['TSP_Result'] = df['TSP_Result'].replace('> 25 mins', '0.3')
def clients_global_summary():
vc = []
dc = []
df2 = df[~((df.id.duplicated(keep=False)) &
(df.BDN == 'no'))].reset_index(drop=True)
perc_red = 100 - (len(df2[df2['overall_lr_outcome'] == 'RED']) / len(df2)) * 100
df_bdn = df2.groupby('BDN').count().reset_index(drop=False)
df_bdn = df_bdn[['BDN', 'id']].T
perc_bdn = (df_bdn.iloc[1, 1] / (df_bdn.iloc[1, 0] + df_bdn.iloc[1, 1])) * 100
sample_count_hfo = len(df2[df2['material_type'] == 'HFO'])
import matplotlib.pyplot as plt
import matplotlib as mpl
import seaborn as sns
import utils as u
import input as i
mpl.rcParams.update(mpl.rcParamsDefault)
pd.options.mode.chained_assignment = None
pd.set_option("display.max_rows", 999)
pd.set_option("display.max_columns", 20)
# get input for queries
criteria = list(i.shared.values())
df = u.run_sql_query(u.full_path('Ann_report_sql', 'loaded_quan_diff.sql'),
i.shared)
df2 = df[[
'ship', 'adv_density', 'TESTED_DEN', 'bunker_quantity', 'material_type'
]]
df2 = df2.dropna()
df2['adv_density'] = pd.to_numeric(df2['adv_density'], errors='coerce')
df2['TESTED_DEN'] = pd.to_numeric(df2['TESTED_DEN'], errors='coerce')
df2['bunker_quantity'] = pd.to_numeric(df2['bunker_quantity'], errors='coerce')
df2['quan_diff'] = (df2['TESTED_DEN'] - df2['adv_density']) \
* df2['bunker_quantity']
df3 = df2[['ship', 'bunker_quantity', 'quan_diff', 'material_type']]
df3['sample'] = 1
# Tidy up supplier text)
import pandas as pd
import matplotlib.pyplot as plt
import utils as u
import input as i
try:
df = u.run_sql_query('Ann_report_sql/AlSiGT40Table.sql', i.shared)
df = df.dropna()
df['Al&Si (mg/kg)'] = df['Al&Si (mg/kg)'].astype(int)
df = df[df['Al&Si (mg/kg)'] > 40]
df['Al&Si (mg/kg)'] = df['Al&Si (mg/kg)'].astype(str)
df = df.reset_index(drop=True)
def low(l):
return l.title()
df['Ship'] = df['Ship'].apply(low)
df['Supplier'] = df['Supplier'].apply(low)
columns = ["Port", "PCounts", "Supplier", "SCounts"]
ports = df['Port'].value_counts()
ports = ports.reset_index(drop=False)
suppliers = df['Supplier'].value_counts()
suppliers = suppliers.reset_index(drop=False)
combined = pd.merge(ports, suppliers, left_index=True, right_index=True)
df2 = df.groupby(['Port', 'Supplier'])['Ship'].count().reset_index()
df4 = df.groupby(['Port'])['Ship'].count().reset_index()
Created on Thu Nov 16 11:37:07 2017
@author: TAQDJO
"""
import json
import pandas as pd
import seaborn as sns
import utils as u
import matplotlib.pyplot as plt
import matplotlib as mpl
import input as i
mpl.rcParams.update(mpl.rcParamsDefault)
sns.despine(left=True, bottom=True)
sns.set_style("dark")
df_Ps = u.run_sql_query(
u.full_path('Ann_report_sql', 'port_suppliers_ frequented.sql'), i.shared)
def PortSupPlot(fld, clr, ttl):
"""builodw charts
fld = field
clr = colour
ttl = title
"""
UpCase = fld.title()
df = u.run_sql_query(
u.full_path('Ann_report_sql', '{} frequented.sql'.format(fld)),
i.shared)
def title(tr):
return tr.title()
import seaborn as sns
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib as mpl
import utils as u
import input as ii
mpl.rcParams.update(mpl.rcParamsDefault)
sns.set_style("white")
pd.options.mode.chained_assignment = None
pd.set_option("display.max_rows", 999)
pd.set_option("display.max_columns", 20)
# Grab and clean DF for Sample distribution
df = u.run_sql_query(u.full_path('Ann_report_sql', 'Fleet_general_info.sql'),
ii.shared)
df['Names'] = [
'Total ships registered on FOBAS program: ', 'Total Samples\nSubmitted: ',
'Bunker Drip\nSamples: ', 'Fuel Audit\nProgramme: ', '\nOthers: ',
'Sample Bottle Kits Ordered: ', 'Average analysis turnaround time (lab): ',
'Investigations: '
]
df = df.fillna(0)
df['count'] = df['count'].astype(int)
df = df.dropna()
df_plot = df.iloc[1:5]
df_plot = df_plot.sort_values(by='count', ascending=False)
new_labels = df_plot['Names'].tolist()
new_counts = df_plot['count'].tolist()
@author: TAQDJO
"""
from pptx import Presentation
from pptx.util import Inches
from pptx.dml.color import RGBColor
from pptx.enum.text import PP_ALIGN
from pptx.util import Pt
import json
import utils as u
import input as i
c = i.shared['cl']
n = f'PPTPeriodicalTemplate {c}.pptx'
df = u.run_sql_query(
u.full_path('Ann_report_sql', 'den_incidences_offspec.sql'), i.shared)
###########################
prs = Presentation(n)
slide = prs.slides[27]
shapes = slide.shapes
rows = len(df) + 1
cols = 10
left = Inches(0.7)
top = Inches(1.2)
width = Inches(2.0)
height = Inches(0.6)
table = shapes.add_table(rows, cols, left, top, width, height).table
#margin_top
def fuel_type(fl, color_):
"""doc"""
i.shared.update({'fuel': fl})
criteria = list(i.shared.values())
df = u.run_sql_query(u.full_path('Ann_report_sql', 'offSpecsPeriodic.sql'), i.shared)
for col in df.columns:
if df[col].dtype == 'int64' and col[-1] == 'a':
df = df.drop(col, axis=1)
df2 = df.sum()
df3 = df2[df2 != 0]
df4 = pd.DataFrame(df3)
df4 = df4.reset_index()
df4.columns = ['test', 'count']
df4['count'] = df4['count'].astype(float)
def truncy(s):
"""doc"""
if s is not None:
return s[:len(s)-9]
df4.test = df4.test.apply(truncy)
df4['perc'] = ((df4['count'] / df4['count'].sum()) * 100).round(2)
df4['labels'] = df4.test.str.cat(df4['perc'].astype(str), sep='\n')
df4 = df4.sort_values(by='count').reset_index(drop=True)
df4['counts'] = np.arange(1, max(df4.index)+2)
plt.figure(figsize=(5, 5))
ax = plt.axes([0.025, 0.025, 1, 1], polar=True)
theta = df4.test
theta = np.linspace(0.0, 2*np.pi, len(df4.test), endpoint=False)
theta3 = np.linspace(0, np.pi)
radii = df4['count']
width = np.pi/(len(df4)/2)
bars = plt.bar(theta, radii, width=width, bottom=0)
radii2 = 10 * np.arange(0, len(df4), 1)
for r, bars in zip(radii2, bars):
bars.set_facecolor(cm.binary(r / (len(df4)*10.)))
bars.set_alpha(0.8)
ax.set_ylim(0, max(df4['count']))
ax.set_yticklabels([])
ax.set_xlim(0, len(theta3)+0.262)
ax.spines['polar'].set_visible(False)
print(criteria)
if criteria[4] == 1:
ax.set_title('Global off specification characteristics\nHFO percentage'
, color='#282f65', size=12)
else:
ax.set_title('Global off specification characteristics\nMGO percentage'
, color='#282f65', size=12)
t = ax.title
t.set_position([.5, 1.15])
ax.set_xticklabels(df4['labels'], color=color_, fontsize=8)
ax.patch.set_facecolor(color_)
ax.patch.set_alpha(0.25)
ax.set_xticks(np.pi/180 * np.linspace(-360, -360/len(df4.index),
len(df4.index)))
ax.set_theta_offset(360 + len(df4) / 100)
ax.yaxis.grid(False)
plt.savefig('plots/PPTOffSpecPerc{}.png'.format(fl)
, bbox_inches='tight', dpi=200)
plt.show()
# -*- coding: utf-8 -*-
"""
Created on Wed Mar 09 09:40:50 2016
@author: TAQDJO
"""
import json
import utils as u
date_from = input("Date from: ")
date_to = input("Date to: ")
client_name = input("Enter client name: ")
user = input("Your first name: ")
shared = {"df": date_from, "dt": date_to, "cl": client_name, "u": user}
fp = open("shared.json", "w")
json.dump(shared, fp)
fp.close()
df = u.run_sql_query(full_path("Ann_report_sql", "Fleet_general_info.sql"), shared)
df.to_csv('plots/genInf.csv')
from pptx.dml.color import RGBColor
from pptx.util import Pt
from pptx import Presentation
import utils as u
import json
import input as i
c = i.shared['cl']
n = f'PPTPeriodicalTemplate {c}.pptx'
df = u.run_sql_query(u.full_path('Ann_report_sql', 'iso_revision.sql'),
i.shared)
def slide_num(n, txt, pres):
prs = Presentation(pres)
slide = prs.slides[n]
for shape in slide.shapes:
if not shape.has_text_frame:
continue
text_frame = shape.text_frame
a = df.iloc[0]['revision']
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = txt.format(str(a))
font = run.font
font.name = 'Source Sans Pro'
font.size = Pt(16)
font.bold = False
font.italic = None # cause value to be inherited from theme
font.color.rgb = RGBColor(0xff, 0xff, 0xff)
prs.save(pres)
from matplotlib import pyplot as plt
import utils as u
import seaborn as sns
import input as i
sns.set_style("white")
df = u.run_sql_query('Ann_report_sql/Client_AlSi.sql', i.shared)
df = df.dropna()
fig, ax = plt.subplots(figsize=(7, 7))
sns.distplot(df['Al&Si (mg/kg)'].astype(int), color='#d4c3dc', rug=True)
sns.distplot(df['Al&Si (mg/kg)'].astype(int), rug=True,
kde_kws={"color": "#284d97", "lw": 1},
hist_kws={"histtype": "step", "linewidth": 1,
"alpha": 1, "color": "#70378d"})
sns.despine(left=True)
ax.set_yticks([])
ax.tick_params(axis='x', colors='#70378d')
ax.set_xlabel('Al&Si (mg/kg)', color='#284d97')
ax.spines['bottom'].set_color('#70378d')
# ax.set_xlim(0, 100)
plt.savefig("plots/dist_plot.jpg", bbox_inches='tight', transparent=True, dpi=200)
plt.show()
import matplotlib.pyplot as plt
import matplotlib as mpl
from itertools import groupby
import pandas as pd
import utils as u
import input as i
mpl.rcParams.update(mpl.rcParamsDefault)
pd.options.mode.chained_assignment = None
criteria = i.shared.values()
df_supplier_reasons = u.run_sql_query(
u.full_path('Ann_report_sql', 'offSpecsPS.sql'), i.shared)
df_supplier_reasons = df_supplier_reasons.drop('port_name', axis=1)
df_supplier_reason = pd.melt(df_supplier_reasons,
id_vars=["supplier_name"],
var_name="test_name",
value_name="t_value")
df_supplier_reason['t_value'] = df_supplier_reason['t_value'].astype(int)
df_supplier_reason = df_supplier_reason[df_supplier_reason.t_value > 0]
df_supplier_reason_a = df_supplier_reason[
df_supplier_reason.test_name.str.contains('_a')]
df_supplier_reason_a = df_supplier_reason_a.sort_values(by=['supplier_name'])
df_supplier_reason_r = df_supplier_reason[
df_supplier_reason.test_name.str.contains('_r')]
df_supplier_reason_r = df_supplier_reason_r.sort_values(by=['supplier_name'])
def plot_status(status, col, trm, name):
df = status
df['test_name'] = df['test_name'].str.replace('_a', '')
import matplotlib.pyplot as plt
import pandas as pd
import utils as u
import seaborn as sns
import input as i
df_global_status = u.run_sql_query(
u.full_path('Ann_report_sql', 'gar_count_global.sql'), i.shared)
df_client_status = u.run_sql_query(
u.full_path('Ann_report_sql', 'gar_count_client.sql'), i.shared)
print(df_global_status)
print(df_client_status)
def title(a_):
return a_.title()
df_global_status.outcome = df_global_status.outcome.apply(title)
df_client_status.outcome = df_client_status.outcome.apply(title)
df_global_status['outcome'] = pd.Categorical(df_global_status['outcome'],
["Green", "Amber", "Red"])
df_global_status = df_global_status.sort_values(by=['outcome'])
df_client_status['outcome'] = pd.Categorical(df_client_status['outcome'],
["Green", "Amber", "Red"])
df_client_status = df_client_status.sort_values(by=['outcome'])
if len(df_client_status) < 3:
df_client_status.loc[len(df_client_status)] = ['Red', 0]
try:
df_global_status['Global'] = (df_global_status['count'] /
df_global_status['count'].sum()) * 100
import utils as u
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.font_manager as font_manager
import input as i
shared = i.shared
df = u.run_sql_query(u.full_path("Ann_report_sql", "KPI.sql"), shared)
df = df.dropna()
def ttl(a):
return a.title()
df.port = df.port.apply(ttl)
df.client = df.client.apply(ttl)
df.collected_sampled = df.collected_sampled.astype('int')
df.received_collected = df.received_collected.astype('int')
df["sampled_to_lab"] = df.collected_sampled + df.received_collected
df_cl_filt = df[df.client.str.contains(shared['cl'], case=False)]
cl_name = df_cl_filt.client.unique()
df_cl = df_cl_filt.groupby('port')['collected_sampled',
'received_collected'].mean().astype('int')
ax = df_cl.plot.bar(figsize=(20, 7),
color=['#00AB91', '#3B8EDE'],
edgecolor='white',
lw=2,
width=0.8)