import pandas as pd
dfp = pd.read_excel(r'Absenteeism_at_work.xls')
dfp.head(5)

dfJson = pd.read_json(r'test.json')
dfJson.head(5)

dfURL = pd.read_csv(r'https://archive.ics.uci.edu/ml/machine-learning-databases/abalone/abalone.data', names =['Sex',
                           'Length','Diameter', 'Height','Whole weight', 'Shucked weight','Viscera weight', 'Shell weight', 'Rings'])
dfURL.head(5)

from pandasql import sqldf
dfpsql = pd.read_excel(r'Absenteeism_at_work.xls')

Query_string = """ select * from dfpsql limit 5 """
sqldf(Query_string, globals())

dfp[(dfp['Age'] >=30) & (dfp['Age'] <=45)]

Query_string = """ select * from dfpsql where age>=30 and age<=45 """
sqldf(Query_string, globals())

dfp['ID'].unique()

Query_string = """ select distinct ID from dfpsql;"""
sqldf(Query_string, globals())

dfp[dfp.Age.isin([20,30,40])]

Query_string = """ select * from dfpsql where Age in(20,30,40);"""
sqldf(Query_string, globals())

dfp[~dfp.Age.isin([20,30,40])]

Query_string = """ select * from dfpsql where Age not in(20,30,40);"""
sqldf(Query_string, globals())

dfp.sort_values(by = ['Age','Service_time'], ascending= True)

Query_string = """ select * from dfpsql order by Age,Service_time;"""
sqldf(Query_string, globals())

#Desending
dfp.sort_values(by = ['Age','Service_time'], ascending= False)

Query_string = """ select * from dfpsql order by Age Desc,Service_time Desc;"""
sqldf(Query_string, globals())

dfp.agg({'Transportation_expense': ['count','min', 'max', 'mean']})

Query_string = """ select count(Transportation_expense) as count, min(Transportation_expense) as min, max(Transportation_expense) as max, avg(Transportation_expense) as mean from dfp;"""
sqldf(Query_string, globals())

dfp.groupby('ID')['Service_time'].sum()

Query_string = """ select ID , sum(Service_time) as Sum_Service_time from dfp
group by ID;"""
sqldf(Query_string, globals())

dfp.groupby('Reason_for_absence').agg({'Age': ['mean','min','max']})

Query_string = """ select Reason_for_absence , avg(Age) as mean, min(Age) as min, max(Age) as max from dfp
group by Reason_for_absence;"""
sqldf(Query_string, globals())

import pandas as pd
data1 = {
        'Empid': [1011, 1012, 1013, 1014, 1015],
        'Name': ['John', 'Rahul', 'Rick', 'Morty', 'Tim'], 
        'Designation': ['Manager', 'Research Engineer', ' Research Engineer', 'VP', 'Delivery Manager'],
        'Date_of_joining': ['01-Jan-2000', '23-sep-2006', '11-Jan-2012', '21-Jan-1991', '12-Jan-1990']}
Emp_df = pd.DataFrame(data1, columns = ['Empid', 'Name', 'Designation','Date_of_joining'])
Emp_df

data2 = {
        'Empid': [1011, 1017, 1013, 1019, 1015],
        'Deptartment': ['Management', 'Research', 'Research', 'Management', 'Delivery'], 
        'Total_Experience': [18, 10, 10, 28, 22]}
Dept_df = pd.DataFrame(data2, columns = ['Empid', 'Deptartment', 'Total_Experience'])
Dept_df

# Inner Join
pd.merge(Emp_df, Dept_df, left_on='Empid',right_on='Empid', how='inner')

# Inner Join
Query_string = """ select * from Emp_df a INNER JOIN Dept_df b ON  a.Empid = b.Empid;"""
sqldf(Query_string, globals())

# Left Join
pd.merge(Emp_df, Dept_df,left_on='Empid',right_on='Empid', how='left')

# Left Join
Query_string = """ select * from Emp_df a LEFT JOIN Dept_df b ON  a.Empid = b.Empid;"""
sqldf(Query_string, globals())

# Right Join
pd.merge(Emp_df, Dept_df,left_on='Empid',right_on='Empid', how='right')

# Right Join
Query_string = """ select a.Empid,Name,Designation,Date_of_joining,Deptartment,Total_Experience from Dept_df a LEFT JOIN Emp_df b ON  a.Empid = b.Empid;"""
sqldf(Query_string, globals())

# Outer Join
pd.merge(Emp_df, Dept_df,left_on='Empid',right_on='Empid', how='outer')

# OUTER join 
Query_string = """ select * from Emp_df a left OUTER JOIN Dept_df b ON  a.Empid = b.Empid;"""
sqldf(Query_string, globals())

con = sqlite3.connect('school.sqlite')
cur = con.cursor()

q = 'select * from faculty'
cur.execute(q)

for row in cur.fetchall():
    print(row)

x('select * from faculty Limit 6')

q = 'select count(title) from faculty'
x(q)

q = 'select count(distinct title) from faculty'
x(q)

q = 'select count(distinct title) as ranks from faculty'
x(q)

x('select avg(salary) from faculty')

x('select * from student_schedules')

x('select * from class_sections')

x('select * from courses')

#available tables are buildings, class_rooms, class_sections, course_categories, courses, departments, 
#faculty, faculty_categories, faculty_courses, faculty_sections, student_schedules, students
q = 'select * from class_sections natural join courses where categoryid = "MAT"'
x(q)

q = 'select * from class_sections natural join courses'
x(q)

q = 'select avg(credits), avg(duration) from class_sections natural join courses where categoryid = "MAT"'
x(q)

q = 'select avg(credits), avg(duration) from class_sections natural join courses where categoryid = "ART"'
x(q)

q='select categoryid, avg(credits) as mean_credits from class_sections natural join courses group by categoryid'
df=x(q)
df

sns.catplot(y='CategoryID', x='mean_credits', data=df, kind='bar')

q = 'select * from faculty_courses'
df = x(q)
df