BATCH 18¶

Importation of libraries¶

[54]:

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

Reading the data¶

lib_transaction
exam_cell
subject_code

[56]:

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`..¶

[58]:

def assign_semester_lib(lib_data):
    lib_data=lib_data[lib_data['year'].isin([18])]
    lib_data.set_index(lib_data['issue_dt'],inplace=True)
    lib_sem1 = lib_data['2018-08-20':'2019-01-11']
    lib_sem1['semester'] = 1
    lib_sem2 = lib_data['2019-01-21':'2019-05-20']
    lib_sem2['semester'] = 2
    lib_sem3 = lib_data['2019-06-24':'2019-11-16']
    lib_sem3['semester'] = 3
    lib_frames = [lib_sem1,lib_sem2,lib_sem3]
    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…¶

[59]:

lib = assign_semester_lib(lib_data=lib_trans)

[60]:

lib

[60]:

	id	level	dept	year	section	access_no	title	issue_dt	due_dt	return_dt	doc	semester	book_id
issue_dt
2018-09-15	X6elhBUk	B	ME	18	1	G22740	TRANSFORMS AND PARTIAL DIFFERENTIAL EQUATIONS	2018-09-15	2018-09-29	2018-09-29	BOOK	1	200
2018-09-15	X6elhBUk	B	ME	18	1	G23614	TEXTBOOK OF FLUID MECHANICS AND HYDRAULIC MACH...	2018-09-15	2018-09-29	2018-09-29	BOOK	1	174
2018-10-09	X6elhBUk	B	ME	18	1	G30499	TRANSFORMS AND PARTIAL DIFFERENTIAL EQUATIONS	2018-10-09	2018-10-23	2018-10-25	BOOK	1	200
2018-10-27	X6elhBUk	B	ME	18	1	G22740	TRANSFORMS AND PARTIAL DIFFERENTIAL EQUATIONS	2018-10-27	2018-11-10	2018-11-09	BOOK	1	200
2018-10-27	X6elhBUk	B	ME	18	1	G23614	TEXTBOOK OF FLUID MECHANICS AND HYDRAULIC MACH...	2018-10-27	2018-11-10	2018-11-09	BOOK	1	174
...	...	...	...	...	...	...	...	...	...	...	...	...	...
2019-09-18	y7T1uqhG	B	ME	18	1	42309	DESIGN DATA : DATA BOOK OF ENGINEERS	2019-09-18	2019-10-02	1800-01-01	BOOK	3	37
2019-10-03	ZUD9PgdX	B	ME	18	1	42308	DESIGN DATA : DATA BOOK OF ENGINEERS	2019-10-03	2019-10-17	2019-10-12	BOOK	3	37
2019-10-24	ZUD9PgdX	B	ME	18	1	42525	DESIGN DATA : DATA BOOK OF ENGINEERS	2019-10-24	2019-11-07	2019-11-05	BOOK	3	37
2019-10-11	ODrgZGIQ	B	ME	18	0	42570	DESIGN DATA : DATA BOOK OF ENGINEERS	2019-10-11	2019-10-25	2019-10-16	BOOK	3	37
2019-11-06	OYejmAgo	B	ME	18	1	43224	FLUID MECHANICS : FUNDAMENTALS AND APPLICATIONS	2019-11-06	2019-11-20	2019-11-20	BOOK	3	77

590 rows × 13 columns

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

[61]:

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…¶

[62]:

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

[63]:

new_lib

[63]:

	id	level	dept	year	section	access_no	title	issue_dt	due_dt	return_dt	doc	semester	book_id	subjectcode	course_title
0	Kka2Da8t	B	ME	18	1	15196	ENGINEERING CHEMISTRY : CHEMISTRY OF ENGINEERI...	2018-09-26	2018-10-10	2018-10-10	BOOK	1	48	16CYT11	Applied Chemistry
1	Yib1Qxml	B	ME	18	0	4638	TEXTBOOK OF ENGINEERING CHEMISTRY	2018-09-26	2018-10-10	2018-10-09	BOOK	1	163	16CYT11	Applied Chemistry
2	Yib1Qxml	B	ME	18	0	15203	ENGINEERING CHEMISTRY : CHEMISTRY OF ENGINEERI...	2018-10-09	2018-10-23	2018-10-25	BOOK	1	49	16CYT11	Applied Chemistry
3	UeQadhuQ	B	ME	18	1	22697	ENGINEERING CHEMISTRY : CHEMISTRY OF ENGINEERI...	2018-09-26	2018-10-10	2018-10-09	BOOK	1	47	16CYT11	Applied Chemistry
4	UeQadhuQ	B	ME	18	1	38420	ENGINEERING CHEMISTRY	2018-10-09	2018-10-23	2018-10-22	BOOK	1	44	16CYT11	Applied Chemistry
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
428	BFeoXlUu	B	ME	18	1	32120	MECHATRONICS : A MULTIDISCIPLINARY APPROACH	2019-10-09	2019-10-23	2019-10-25	BOOK	3	120	16MET71	'Mechatronics
429	4yjyNeNE	B	ME	18	0	38180	MECHATRONICS	2019-10-22	2019-11-05	2019-11-02	BOOK	3	119	16MET71	'Mechatronics
430	4yjyNeNE	B	ME	18	0	38178	MECHATRONICS	2019-11-09	2019-11-23	2019-11-19	BOOK	3	119	16MET71	'Mechatronics
431	j8SWqd4P	B	ME	18	0	38175	MECHATRONICS	2019-10-22	2019-11-05	2019-11-05	BOOK	3	119	16MET71	'Mechatronics
432	j8SWqd4P	B	ME	18	0	38177	MECHATRONICS	2019-11-09	2019-11-23	2019-11-14	BOOK	3	119	16MET71	'Mechatronics

433 rows × 15 columns

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

[64]:

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

Variable for the above function:¶

[65]:

class_mean_score = mean_score(lib_data=new_lib)

[66]:

class_mean_score

[66]:

	section	semester	subjectcode	library_id_frequency
0	0	1	16AUT31	5
1	0	1	16AUT32	5
2	0	1	16CST46	1
3	0	1	16CYT11	11
4	0	1	16GET21	3
5	0	1	16MAT11	5
6	0	1	16MAT41	2
7	0	1	16MET31	3
8	0	1	16MET33	3
9	0	1	16MET52	1
10	0	1	16PHT11	19
11	0	2	16CST46	7
12	0	2	16EET45	2
13	0	2	16GET21	30
14	0	2	16GET22	44
15	0	2	16MAT11	1
16	0	2	16MET31	1
17	0	2	16MET33	10
18	0	2	16MET41	10
19	0	2	16PHT21	1
20	0	3	16AUT31	84
21	0	3	16AUT32	11
22	0	3	16CST46	3
23	0	3	16GET21	1
24	0	3	16MAT11	1
25	0	3	16MAT31	3
26	0	3	16MET31	3
27	0	3	16MET33	11
28	0	3	16MET51	1
29	0	3	16MET52	9
30	0	3	16MET54	2
31	0	3	16MET63	5
32	0	3	16MET71	4
33	0	3	16PHT21	2
34	1	1	16AUT31	2
35	1	1	16AUT32	6
36	1	1	16CYT11	25
37	1	1	16MAT11	6
38	1	1	16MAT31	5
39	1	1	16MET31	3
40	1	1	16MET33	1
41	1	1	16MET52	2
42	1	1	16PHT11	5
43	1	2	16EET45	3
44	1	2	16GET21	2
45	1	2	16GET22	3
46	1	2	16MAT41	2
47	1	2	16MET33	4
48	1	3	16AUT31	18
49	1	3	16AUT32	21
50	1	3	16CST46	2
51	1	3	16ECT56	2
52	1	3	16MET31	2
53	1	3	16MET32	1
54	1	3	16MET33	11
55	1	3	16MET52	2
56	1	3	16MET71	1

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

[67]:

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

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

[68]:

sns.barplot(class_mean_score['subjectcode'],class_mean_score['library_id_frequency'],hue='section',data=class_mean_score)

[68]:

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

`section` vs `library_id_frequency`¶

[69]:

sns.boxplot(class_mean_score['section'],class_mean_score['library_id_frequency'],data=class_mean_score)

[69]:

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

Correlation of `Library_id_frequency` in `semester`¶

[70]:

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

[70]:

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

BATCH 18¶

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’¶

subjectcode vs library_id_frequency for each section¶

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’¶

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

`section` vs `library_id_frequency`¶

Correlation of `Library_id_frequency` in `semester`¶