BATCH 17¶

Importation of libraries¶

[36]:

import pandas as pd
import seaborn as sns
import numpy as np
import re
sns.set(rc={'figure.figsize':(19.7,16.27)})

Reading the data¶

lib_transaction
exam_cell
subject_code

[2]:

lib_trans=pd.read_excel('issue_records.xlsx')
exam_cell = pd.read_excel('exam_cell_data.xlsx',index_col=0)
sub_code = pd.read_csv('mech.csv')

This function Represents Assigning the semester details to the `lib_tans`..¶

[3]:

def assign_semester_lib(lib_data):
    lib_data=lib_data[lib_data['year'].isin([17])]
    lib_data.set_index(lib_data['issue_dt'],inplace=True)
    lib_sem1 = lib_data['2017-08-22':'2017-12-17']
    lib_sem1['semester'] = 1
    lib_sem2 = lib_data['2018-01-29':'2018-06-01']
    lib_sem2['semester'] = 2
    lib_sem3 = lib_data['2018-06-25':'2017-11-19']
    lib_sem3['semester'] = 3
    lib_sem4 = lib_data['2018-12-09':'2019-05-06']
    lib_sem4['semester'] = 4
    lib_sem5= lib_data['2019-06-19':'2019-11-15']
    lib_sem5['semester'] = 5
    lib_frames = [lib_sem1,lib_sem2,lib_sem3,lib_sem4,lib_sem5]
    lib_transaction = pd.concat(lib_frames)
    lib_transaction['book_id'] = lib_transaction.groupby('title').ngroup()
    lib_transaction.drop(columns=['Unnamed: 0'],inplace=True)
    return lib_transaction

the function is assigned to the `lib` variable…¶

[4]:

lib = assign_semester_lib(lib_data=lib_trans)

[5]:

lib

[5]:

	id	level	dept	year	section	access_no	title	issue_dt	due_dt	return_dt	doc	semester	book_id
issue_dt
2017-09-08	KqEEn7Kg	B	ME	17	1	13910	TEXTBOOK OF FLUID MECHANICS AND HYDRAULIC MACH...	2017-09-08	2017-09-22	2017-09-27	BOOK	1	267
2017-09-08	KqEEn7Kg	B	ME	17	1	41582	THERMODYNAMICS	2017-09-08	2017-09-22	2017-09-27	BOOK	1	284
2017-10-04	KqEEn7Kg	B	ME	17	1	28149	THEORY OF MACHINES	2017-10-04	2017-10-18	2017-11-30	BOOK	1	280
2017-10-04	KqEEn7Kg	B	ME	17	1	33564	TEXTBOOK OF FLUID MECHANICS AND HYDRAULIC MACH...	2017-10-04	2017-10-18	2017-11-23	BOOK	1	267
2017-10-04	KqEEn7Kg	B	ME	17	1	41582	THERMODYNAMICS	2017-10-04	2017-10-18	2017-11-23	BOOK	1	284
...	...	...	...	...	...	...	...	...	...	...	...	...	...
2019-09-18	a9c1Vjuz	B	ME	17	0	5272	STEAM TABLES WITH MOLLIER DIAGRAM	2019-09-18	2019-10-02	2019-09-19	BOOK	5	250
2019-10-23	a9c1Vjuz	B	ME	17	0	42563	DESIGN DATA : DATA BOOK OF ENGINEERS	2019-10-23	2019-11-06	2019-11-05	BOOK	5	52
2019-10-03	WfA1aF9V	B	ME	17	0	42447	DESIGN DATA : DATA BOOK OF ENGINEERS	2019-10-03	2019-10-17	2019-10-09	BOOK	5	52
2019-10-09	WfA1aF9V	B	ME	17	0	13277	SSB INTERVIEWS AND TESTS FOR COMMISIONS IN IND...	2019-10-09	2019-10-23	2019-10-22	BOOK	5	247
2019-10-23	gO5Nm6PK	B	ME	17	0	42508	DESIGN DATA : DATA BOOK OF ENGINEERS	2019-10-23	2019-11-06	2019-11-05	BOOK	5	52

1068 rows × 13 columns

This function Represents Assigning the semester details to the `lib_tans`..¶

[18]:

def assign_subjectcode(data,sub_code):
    data.set_index(data['issue_dt'],inplace=True)
    chemistry = data[data['title'].str.contains('CHEMISTRY')==True]
    chemistry['subjectcode'] = '16CYT11'
    physics = data[data['title'].str.contains('PHYSICS')==True]
    physics['subjectcode'] = '16PHT11'
    maths1 = data[data['title'].str.contains('ENGINEERING MATHEMATICS')==True]
    maths1['subjectcode'] = '16MAT11'
    maths2 = data[data['title'].str.contains('ENGINEERING MATHEMATICS II')==True]
    maths2['subjectcode'] = '16MAT21'
    Communication_skills1 = data[data['title'].str.contains('COMMUNICATION')==True]
    Communication_skills1['subjectcode'] = '16ENT11'
    Communication_skills2 = data[data['title'].str.contains('COMMUNICATION')==True]
    Communication_skills2['subjectcode'] = '16ENT21'
    material_science = data[data['title'].str.contains('MATERIAL SCIENCE')==True]
    material_science['subjectcode'] =  '16PHT21'
    engineering_mechanics = data[data['title'].str.contains('ENGINEERING MECHANICS')          ==True]
    engineering_mechanics['subjectcode'] = '16GET21'
    metrology = data[data['title'].str.contains('METROLOGY')==True]
    metrology['subjectcode'] = '16GET22'
    tpde = data[data['title'].str.contains('DIFFERENTIAL')==True]
    tpde['subjectcode'] = '16MAT31'
    thermodynamics = data[data['title'].str.contains('THERMODYNAMICS')==True]
    thermodynamics['subjectcode'] = '16AUT31'
    fluid_mech = data[data['title'].str.contains('FLUID MECHANICS')==True]
    fluid_mech['subjectcode'] = '16AUT32'
    theory_machines1 = data[data['title'].str.contains('THEORY OF MACHINES')==True]
    theory_machines1['subjectcode'] = '16MET33'
    theory_machines2 = data[data['title'].str.contains('THEORY OF MACHINES II')==True]
    theory_machines2['subjectcode'] = '16MET43'
    metallurgy = data[data['title'].str.contains('METALLURGY')==True]
    metallurgy['subjectcode'] = '16MET31'
    mfj = data[data['title'].str.contains('MANUFACTURING PROCESS')==True]
    mfj['subjectcode'] = '16MET32'
    m4 = data[data['title'].str.contains('NUMERICAL METHODS')==True]
    m4['subjectcode'] = '16MAT41'
    c_prog = data[data['title'].str.contains('PROGRAMMING')==True]
    c_prog['subjectcode'] = '16CST46'
    som = data[data['title'].str.contains('STRENGTH OF MATERIALS')==True]
    som['subjectcode'] = '16MET41'
    mcp = data[data['title'].str.contains('METAL CUTTING')==True]
    mcp['subjectcode'] = '16MET42'
    edc = data[data['title'].str.contains('ELECTRICAL DRIVES')==True]
    edc['subjectcode'] = '16EET45'
    dom = data[data['title'].str.contains('DESIGN OF MACHINE ELEMENTS')==True]
    dom['subjectcode'] = '16MET51'
    design_data = data[data['title'].str.contains('DESIGN DATA')==True]
    design_data['subjectcode'] = '16MET51-16MET61'
    thermal = data[data['title'].str.contains('THERMAL')==True]
    thermal['subjectcode'] = '16MET52'
    auto = data[data['title'].str.contains('AUTOMOBILE')==True]
    auto['subjectcode'] = '16MET54'
    micro_cont = data[data['title'].str.contains('MICROCONTROLLER')==True]
    micro_cont['subjectcode'] = '16ECT56'
    fea = data[data['title'].str.contains('FINITE')==True]
    fea['subjectcode'] = '16MET61'
    transmission = data[data['title'].str.contains('DESIGN OF TRANSMISSION')==True]
    transmission['subjectcode'] = '16MET62'
    hmt = data[data['title'].str.contains('HEAT')==True]
    hmt['subjectcode'] = '16MET63'
    powerplant = data[data['title'].str.contains('POWER PLANT')==True]
    powerplant['subjectcode'] = '16MET64'
    mechatronics = data[data['title'].str.contains('MECHATRONICS')==True]
    mechatronics['subjectcode'] = '16MET71'
    evs = data[data['title'].str.contains('ENVIRONMENTAL')==True]
    evs['subjectcode'] = '16CET73'
    frames = [chemistry,physics,material_science,engineering_mechanics,metrology,tpde,maths1,maths2,Communication_skills1,Communication_skills2,theory_machines1,theory_machines2,      thermodynamics,fluid_mech,metallurgy,mfj,m4,c_prog,som,mcp,edc,dom,thermal,auto,        micro_cont,fea,transmission,hmt,powerplant,mechatronics,evs]
    new_data = pd.concat(frames)
    sub_code.rename(columns={'Course code':'subjectcode','title':'course_title'},           inplace=True)
    lib_merged = pd.merge(new_data,sub_code,on='subjectcode',how='inner')
    design_data['subjectcode'] = '16MET51-16MET61'
    design_data['course_title'] = 'Design data Book'
    return lib_merged

the function is assigned to the `new_lib` variable…¶

[19]:

new_lib = assign_subjectcode(data=lib,sub_code=sub_code)

[20]:

new_lib

[20]:

	id	level	dept	year	section	access_no	title	issue_dt	due_dt	return_dt	doc	semester	book_id	subjectcode	course_title
0	NsFib7hs	B	ME	17	0	15197	ENGINEERING CHEMISTRY : CHEMISTRY OF ENGINEERI...	2017-09-22	2017-10-06	2017-10-04	BOOK	1	72	16CYT11	Applied Chemistry
1	DWkoOSkt	B	ME	17	1	15197	ENGINEERING CHEMISTRY : CHEMISTRY OF ENGINEERI...	2017-10-04	2017-10-18	2017-10-23	BOOK	1	72	16CYT11	Applied Chemistry
2	DWkoOSkt	B	ME	17	1	5492	APPLIED CHEMISTRY	2017-11-02	2017-11-16	2017-11-15	BOOK	1	13	16CYT11	Applied Chemistry
3	5WYXlijl	B	ME	17	1	3317	ENGINEERING CHEMISTRY : CHEMISTRY OF ENGINEERI...	2017-10-03	2017-10-17	2017-10-12	BOOK	1	71	16CYT11	Applied Chemistry
4	5WYXlijl	B	ME	17	1	4768	ENGINEERING CHEMISTRY (CHEMISTRY OF ENGINEERIN...	2017-11-14	2017-11-28	2017-12-04	BOOK	1	69	16CYT11	Applied Chemistry
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
481	B9byVoMX	B	ME	17	0	32120	MECHATRONICS : A MULTIDISCIPLINARY APPROACH	2019-11-07	2019-11-21	2019-11-09	BOOK	5	189	16MET71	'Mechatronics
482	IZUXluMd	B	ME	17	1	17602	MECHATRONICS: ELECTRONIC CONTROL SYSTEMS IN ME...	2019-06-28	2019-07-12	2019-07-13	BOOK	5	191	16MET71	'Mechatronics
483	IZUXluMd	B	ME	17	1	42250	MECHATRONICS : INTEGRATED MECHANICAL ELECTRONI...	2019-06-28	2019-07-12	2019-07-13	BOOK	5	190	16MET71	'Mechatronics
484	HRkNv2LQ	B	ME	17	0	38181	MECHATRONICS	2019-10-22	2019-11-05	2019-11-02	BOOK	5	188	16MET71	'Mechatronics
485	giOtHG6u	B	ME	17	0	17598	MECHATRONICS: ELECTRONIC CONTROL SYSTEMS IN ME...	2019-09-30	2019-10-14	2019-10-01	BOOK	5	191	16MET71	'Mechatronics

486 rows × 15 columns

Library usage by `section` and `semster` and `subject_code` in the below function:¶

[23]:

def mean_score(lib_data):
    class_mean_per_sem = pd.DataFrame()
    class_mean_per_sem['library_id_frequency'] = lib_data.groupby(['section','semester','subjectcode'])['id'].count()
    class_mean_per_sem.reset_index(inplace=True)
    return class_mean_per_sem

Variable for the above function:¶

[24]:

class_mean_score = mean_score(lib_data=new_lib)

[25]:

class_mean_score

[25]:

	section	semester	subjectcode	library_id_frequency
0	0	1	16AUT31	5
1	0	1	16AUT32	8
2	0	1	16CYT11	12
3	0	1	16GET21	1
4	0	1	16MAT11	6
...	...	...	...	...
74	1	5	16MET41	1
75	1	5	16MET51	15
76	1	5	16MET52	14
77	1	5	16MET54	4
78	1	5	16MET71	4

79 rows × 4 columns

Replacing the `class_mean_score`[‘section] to 0:’A’ and 1:’B’¶

[26]:

class_mean_score['section'].replace(to_replace=0,value='A',inplace=True)
class_mean_score['section'].replace(to_replace=1,value='B',inplace=True)

[27]:

class_mean_score

[27]:

	section	semester	subjectcode	library_id_frequency
0	A	1	16AUT31	5
1	A	1	16AUT32	8
2	A	1	16CYT11	12
3	A	1	16GET21	1
4	A	1	16MAT11	6
...	...	...	...	...
74	B	5	16MET41	1
75	B	5	16MET51	15
76	B	5	16MET52	14
77	B	5	16MET54	4
78	B	5	16MET71	4

79 rows × 4 columns

Importing `matplotlib` library¶

[28]:

import matplotlib.pyplot as plt

`subjectcode` vs `library_id_frequency` for each `section`¶

[38]:

ax = sns.barplot(class_mean_score['subjectcode'],class_mean_score['library_id_frequency'],hue='section',data=class_mean_score)
ax.figure.savefig('batch17.png')

`semester` library transaction details:¶

[39]:

sns.barplot(x=class_mean_score['semester'],y=class_mean_score['library_id_frequency'],hue='section',data=class_mean_score)

[39]:

<matplotlib.axes._subplots.AxesSubplot at 0x7f9b97f4ac90>

`section` vs `library_id_frequency`¶

[32]:

plt.barh(class_mean_score['section'],class_mean_score['library_id_frequency'])

[32]:

<BarContainer object of 79 artists>

Correlation of `Library_id_frequency` in `semester`¶

[33]:

ax = sns.heatmap(data=class_mean_score.corr(method='pearson'),annot=True)

[33]:

<matplotlib.axes._subplots.AxesSubplot at 0x7f9b9b418390>

BATCH 17¶

Importation of libraries¶

Reading the data¶

This function Represents Assigning the semester details to the lib_tans..¶

the function is assigned to the lib variable…¶

This function Represents Assigning the semester details to the lib_tans..¶

the function is assigned to the new_lib variable…¶

Library usage by section and semster and subject_code in the below function:¶

Variable for the above function:¶

Replacing the class_mean_score[‘section] to 0:’A’ and 1:’B’¶

Importing matplotlib library¶

subjectcode vs library_id_frequency for each section¶

semester library transaction details:¶

section vs library_id_frequency¶

Correlation of Library_id_frequency in semester¶

This function Represents Assigning the semester details to the `lib_tans`..¶

the function is assigned to the `lib` variable…¶

This function Represents Assigning the semester details to the `lib_tans`..¶

the function is assigned to the `new_lib` variable…¶

Library usage by `section` and `semster` and `subject_code` in the below function:¶

Replacing the `class_mean_score`[‘section] to 0:’A’ and 1:’B’¶

Importing `matplotlib` library¶

`subjectcode` vs `library_id_frequency` for each `section`¶

`semester` library transaction details:¶

`section` vs `library_id_frequency`¶

Correlation of `Library_id_frequency` in `semester`¶