plotting

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Fri Jul 26 00:24:36 2019

@author: jha
"""

#%%
import csv
import mysql.connector as mariadb
from tabulate import tabulate
import pandas as pd
import numpy as np

amrdb= mariadb.connect(
        host="localhost",
        user="root",
        passwd="Sukhoi@90",
        database ="myamr"
        )
cursor = amrdb.cursor(buffered=True) # else it fetches one row for everytime it is executed 
print("We are at line 31 we have connection, lets begin")

#%%

#%%
dfplot=pd.DataFrame(columns=["antibiotics","SS","SnS","ECS","ECnS","CS","CnS","LS","LnS"])
list_antibiotics=[]
lst=[]
#%%

#%%

numrows= cursor.execute("SELECT antibiotics FROM ECnS ")
print("Selected %s rows" %numrows)
print("Selected %s rows " %cursor.rowcount)
lst=list(cursor.fetchall())#fetch all rows at once

for i in lst:
    if i not in list_antibiotics: 
        #print("yo",i[0])
        list_antibiotics.append(i)

       

#%%

#%%
list_anti=[]
for tup in list_antibiotics:
    for val in tup:
        list_anti.append(val)
se=pd.Series(list_anti)
dfplot["antibiotics"]= se.values
#


#%%

#%%
numrows= cursor.execute("SELECT antibiotics,percent_nonS FROM SNS ")
print("Selected %s rows" %numrows)
print("Selected %s rows " %cursor.rowcount)
lst=list(cursor.fetchall())#fetch all rows at once
lstd=dict(lst) 


dfplot['SnS'] = dfplot['antibiotics'].map(lstd)

#%%

#%%
from sqlalchemy import create_engine
cnx = create_engine('mysql+pymysql://root:Sukhoi@90@localhost/myamr')
dfplot.to_sql(name='astall', con=cnx, if_exists = 'replace', index=False)
print("we are at line 356 in code")



#%%


#susceptible
#%%


# libraries
import numpy as np
import matplotlib.pyplot as plt
 
# set width of bar
barWidth = 0.25
 
# set height of bar
bars1 = dfplot["SS"]#.sort_values('SS',ascending=False)
bars2 = dfplot["CS"]#.sort_values('CS',ascending=False)
bars3 = dfplot["ECS"]#.sort_values('ECS',ascending=False)
bars4 = dfplot["LS"]#.sort_values('LS',ascending=False)
 
# Set position of bar on X axis
r1 = np.arange(len(bars1))
r2 = [x + 4*barWidth for x in r1]
r3 = [x + 3*barWidth for x in r2]
r4 = [x+ 2*barWidth for x in r3]
# Make the plot
plt.bar(r1, bars1, color='cadetblue', width=barWidth, edgecolor='white', label='Salmonella')
plt.bar(r2, bars2, color='cornflowerblue', width=barWidth, edgecolor='white', label='Campylobacter')
plt.bar(r3, bars3, color='navy', width=barWidth, edgecolor='white', label='Ecoli')
plt.bar(r4, bars4, color='slateblue', width=barWidth, edgecolor='white', label='Listeria')
 
# Add xticks on the middle of the group bars
plt.xlabel('group', fontweight='bold')
plt.xticks([r + barWidth for r in range(len(bars1))], dfplot["antibiotics"], rotation=90)
plt.title("percentage susceptibility of 4 microbes for 50 antibiotics" )
plt.xlabel("antibiotics")
plt.ylabel("percentage susceptibility")
# Create legend & Show graphic
plt.legend()
plt.show()











#%%








#NON susceptible
#%%


# libraries
import numpy as np
import matplotlib.pyplot as plt
 
# set width of bar
barWidth = 0.25

dfsns=dfplot.sort_values("SnS",ascending=False)
dfcns=dfplot.sort_values("CnS",ascending=False)
dfecns=dfplot.sort_values("ECnS",ascending=False)
dflns=dfplot.sort_values("LnS",ascending=False)

 
# set height of bar
bars1 = dfsns["SnS"]#.sort_values('SS',ascending=False)
bars2 = dfcns["CnS"]#.sort_values('CS',ascending=False)
bars3 = dfecns["ECnS"]#.sort_values('ECS',ascending=False)
bars4 = dflns["LnS"]#.sort_values('LS',ascending=False)
 
# Set position of bar on X axis
r1 = np.arange(len(bars1))
r2 = [x + barWidth for x in r1]
r3 = [x + barWidth for x in r2]
r4 = [x + barWidth for x in r3]
# Make the plot
plt.bar(r1, bars1, color='red', width=barWidth, edgecolor='white', label='Salmonella')
plt.bar(r2, bars2, color='salmon', width=barWidth, edgecolor='white', label='Campylobacter')
plt.bar(r3, bars3, color='brown', width=barWidth, edgecolor='white', label='Ecoli')
plt.bar(r4, bars4, color='chocolate', width=barWidth, edgecolor='white', label='Listeria')
 
# Add xticks on the middle of the group bars
plt.xlabel('group', fontweight='bold')
plt.xticks([r + barWidth for r in range(len(bars1))], dfplot["antibiotics"], rotation=90)
plt.title("percentage of non-susceptibility of 4 microbes for 50 antibiotics" )
plt.xlabel("antibiotics")
plt.ylabel("percentage non-susceptible")
plt.tight_layout()
plt.legend(loc='upper right')

# Create legend & Show graphic
plt.legend()
plt.show()










#%%


#%%

dfplotT=dfplot.T
dfplotT=dfplotT.reset_index()
dfplotT.columns=dfplotT.iloc[0]
dfplotT=dfplotT.drop([0])
dfplotT=dfplotT.reset_index()
dfplotT=dfplotT.drop(["index"])



















#%%