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)