import pandas as pd
import MySQLdb
import pandas.io.sql as psql
import matplotlib.pyplot as plt; plt.rcdefaults()
import numpy as np
import matplotlib.pyplot as plt
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.ticker as ticker
import matplotlib.animation as animation
from IPython.display import HTML

host="192.168.20.246"
port=3306,
user="root"
passwd="avani@0987"
db="python_db"
charset='utf8'

db=MySQLdb.connect(host=host, user=user, passwd=passwd, db=db)
 
# create the query
query = "select * from logfiles2"
 
# execute the query and assign it to a pandas dataframe
df = psql.read_sql(query, con=db)
# close the database connection
db.close()

df.head()

groups = df.groupby(["company_id"])["Request_Method"].count()
groups.plot.(color="green",figsize=(16,6))

plt.title("Company_id vs Request_Method ")
plt.xlabel("Company_id")
plt.ylabel("Request_Method")
plt.legend()
plt.show()

groups1 = df.groupby(["company_id"])["HTTP_methods"].count()
groups1.plot.barh(color="green",figsize=(16,6))

plt.title("Company_id vs Request_Method ")
plt.xlabel("Company_id")
plt.ylabel("Request_Method")
plt.legend()
plt.show()

pd.crosstab(df['company_id'],df['HTTP_methods']).plot.bar()

data = df[df['Request_Method'] == 'logout']

finaldata = data.groupby(["company_id"])["Request_Method"].count()




total = len(data['Request_Method'])



finaldata.plot.barh(color="blue",figsize=(18,6),facecolor='red')

plt.title("Company_id and user_id vs logouts ")
plt.xlabel("logout")
plt.ylabel("Company_id & user_id")
plt.legend()
# plt.show()
for i, v in enumerate(finaldata):
    plt.text(v + 3, i + .25, str(v), color='blue')

logout = data.groupby(["company_id","user_id"])["Request_Method"].count()

total = len(df['Request_Method'])

logout.plot.barh(color="blue",figsize=(18,6),facecolor='green')

plt.title("Company_id and user_id vs logouts ")
plt.xlabel("logout")
plt.ylabel("Company_id & user_id")
plt.legend()
# plt.show()
for i, v in enumerate(logout):
    plt.text(v + 3, i + .25, str(v), color='blue')
    

logout = data.groupby(["company_id","user_id"])["Request_Method"].count().plot(kind='bar',figsize=(18,6))

import jovian

jovian.commit()