Pandas Series

Write a program to generate a series of marks of 10 students. Give grace

marks up to 3 marks of those who are having marks between 30 to 33 marks
and print the new list of the marks.

Solution:

import pandas as pd

std_marks = []

for i in range(1,11):

m = int(input("Enter the marks:"))

std_marks.append(m)

s = pd.Series(index=range(1201,1211),data=std_marks)

s[s==32]=s+1

s[s==31]=s+2

s[s==30]=s+3

print("New List is:")

print(s[s>=33])

Pandas Dataframe
I have taken question from pandas dataframe for 3 marks. Here

Consider the following data for :

BookID Subject BookTitle Class Publisher Price

 NCERT
Computer
B0001 Computer XII NCERT 270
Science
Science

Move fast
Computer with Dhanpat
B0002 XII 340
Science computer Rai
science

Computer Sample
B0003 X BPB 120
Applications Papers

NCERT
Informatics
B0004 Computer XII NCERT 270
Practices
Science

Artificial Artificial
B0005 IX KIPS 340
Intelligence Intelligence

CBSE
Informatics Oswal
B0006 Questions XII 299
Practices Books
Bank

1. Create a dataframe using

lists. 1
2. Display books for class
XII. 1
3. Display the books whose price is more than
250. 1
4. Plot these data on line
chart. 3

Code for 1,2, & 3.

import pandas as pd

import matplotlib.pyplot as mpp

#Answer 1
data={'BookID':['B0001','B0002','B0003','B0004','B0005','B0006'],\

'Subject':['Computer Science','Computer Science','Computer Appllications',\

'Informatics Practices','Artificial Intelligence','Informatics Practices'],\

'Class':['XII','XII','X','XII','IX','XII'],\

'Publisher':['NCERT','Dhanpat Rai','BPB','NCERT','KIPS','Oswal books'],\

'Price':[270,340,120,270,340,299]}

books=pd.DataFrame(data)

#Asnwer 2

print("Class XII Books:")

print(books[books['Class']=='XII'].to_string(header=False,index=False))

print("***********************************************************")

#Asnwer 3

print("Books having price more than 250")

print(books[books['Price']>250].to_string(header=False,index=False))

#Answer 4

books.plot(x='Subject',y='Price',kind='bar')

mpp.show()

MySQL
Consider the following table and write answers for given questions below:
 RollNo Name Class DOB Gender City Marks

1 Naman XII 1995-05-09 M Anand 453

2 Nandini X 1997-04-08 F Baroda 551

3 Nakshatra X 1997-03-02 F Baroda 553

4 Shailesh XI 1995-04-07 M Surat 458

5 Trisha XII 1996-04-01 F Anand 430

6 Manisha XII 1995-02-05 F Anand 530

7 Hetvee XII 1995-08-17 F Junagadh 555

8 Neel X 1997-10-19 M Godhara 559

9 Mayur XII 1996-12-04 M Surat 570

10 Dolin XII 1994-11-02 M Anand 585

Write SQL statements for the following based on table Garments:

1. Create above table in MySQL.

2. Insert records.
3. To display the detail of class XII students in descending order of their marks.
4. Display all the details of students in ascending order of name.
5. Find the maximum marks of the student for each class.
6. Count the students class wise is display only those number who is more than
2.
7. Display unique cities from the table.

Solution:

1 Create table:
create table students

(rollno int(4) primary key,

name varchar(20) not null,

class varchar(4),

dob date,

gender char(1),

city varchar(20),

marks float);

2 Insert records:

insert into students values

(1, 'Naman', 'XII','1995/05/09','M','Anand',453)

Insert all records simimlarly.

3 display the detail of class XII students in descending order of their marks

select * from students order by marks desc;

4 Display all the details of students in ascending order of name

select * from students order by name asc

5 Find the maximum marks of the student for each class

select class,max(marks) from students group by class

6 Count the students class wise is display only those number who is more
than 2

select class,count(*) from students group by class having count(*)>2;

7 Display unique cities from the table

select distinct city from students;

Python Program
Q 1 Write a program in python to create the following dataframe named
“country” storing the following details:

cname City billamt Tran_date

1001 Shruti Ahmedabad 9500 2010-10-01

1002 Tushar Baroda 5300 2010-01-04

1003 Jay Surat 4550 2009-03-01

1004 Sameer Ahmedabad 4000 2009-04-01

1005 Mayank Surat 8500 2008-08-05

1006 Meena Baroda 4300 2008-08-06

1007 Dhairya Ahmedabad 3000 2009-10-10

Considering the above dataframe answer the following queries by writing appropriate
command in python pandas.
i. Add a new column named discount which is 10% of their bill amount.
ii. Add a row with row index 1008 as Rohan,Bharuch,6000,2011-04-01.
iii. Now plot a bar chart depicting the customer name on x-axis and their
corresponding
bill amount on y-axis, with appropriate Graph title, x-axis title, y-axis title, gridlines
and color etc.

Solution:

import pandas as pd

import matplotlib.pyplot as plt

d={'cname':['Shruti','Tushar','Jay','Sameer','Mayank','Meena','Dhairya'],

'city':['Ahmedabad','Baroda','Surat','Ahmedabad','Surat','Baroda','Ahmedabad'],

'billamt':[9500,5300,4550,4000,8500,4300,3000],

'tran_date':['2010-10-01','2010-01-04','2009-03-01','2009-04-01','2008-08-05','2008-08-06','2009-10-10']

customer=pd.DataFrame(d)

#Answer 1

customer['discount']=customer['billamt']*0.10

print(customer)

#Answer 2

customer.loc[1008]=['Rohan','Bharuch',6000,'2011-04-01',600]

print(customer)

#Answer 3

plt.bar(customer['cname'],customer['billamt'],color=['r','g','b','c','m','y','k'])

plt.title("Report",color='Red')

plt.xlabel("Customer Names",color='Blue')
plt.ylabel("Bill Amount",color='Magenta')

plt.show()

MySQL
Q – 2 Create below table “staff” and insert all records.

Sid sname designation salary dojoin

2010-11- Residential
1001 Sagar PGT 87000
02 Section

2010-04-
1002 Ankit Clerk 24000 Office
01

2009-01-
1003 Dhwani Clerk 22000 Office
05

2009-07-
1004 Jenil PRT 34000 Primary
25

2008-07-
1005 Roshni PGT 73000 Senior
17

2008-04-
1006 Mital TGT 41000 Middle
08

Lab 2009-11-
1007 Gagan 24000 Office
Assistant 23

Answer the following sql queries:

i. Display the difference of maximum and minimum salary of each section.

select max(salary)-min(salary) from staff group by section;

ii. Display the staff name, designation and date of joining who joins in the month of
July and April.

select sname,designation,dojoin from staff where monthname(dojoin) in ('April','July');

iii. Display the records of staff in their descending order of salary.

select * from staff order by salary desc;

iv. Show first 3 character of staff name.

select left(sname,3) from staff;

v. Display the records of staff who is working since last 12 years

select * from staff where year(now())-year(dojoin)=12;

vi. Display power of length of staff name

select power(length(sname),2) from staff;

Q – 1 Write a program in python to create the following dataframe named

“country” storing the following details:

Country Population BirthRate UpdateDate

0 China 1379750000 12.00 2016-08-11

1 India 1330780000 21.76 2016-08-11

2 United States 324882000 13.21 2016-08-11

3 Indonesia 260581000 18.84 2016-01-07

4 Brazil 206918000 18.43 2016-08-11

5 Pakistan 194754000 27.62 2016-08-11

Considering the above dataframe answer the following question by writing
appropriatecommand in python pandas:

1. Display complete data for China and India.

2. Display Country, Population and BirthRate of Brazil and Pakistan.
3. Create a CSV File from the above data frame.
4. Now plot a bar chart depicting the Country on x-axis and their corresponding
Population on y-axis, with appropriate Graph title, x-axis title, y-axis title,
gridlines an color etc.

Solution:

import pandas as pd

import matplotlib.pyplot as plt

d={'country':['China','India','US','Indonasia','Brazil','Pakistan'],

'population':[1379750000,1330780000,324882000,260581000,206918000,194754000],

'birthrate':[12.00,21.76,13.21,18.84,18.43,27.62],

'updatedate':['2010-10-01','2010-01-04','2009-03-01','2009-04-01','2008-08-05','2010-04-05']

country=pd.DataFrame(d)

#Answer 1

print("Answer 1")

print(country[country['country']=='China'].to_string(header=False,index=False))

print(country[country['country']=='India'].to_string(header=False,index=False))
#Answer 2

print("Answer 2")

c=country.loc[:,['country','population','birthrate']]

print(c[c['country']=='Brazil'].to_string(header=False,index=False))

print(c[c['country']=='Pakistan'].to_string(header=False,index=False))

#Answer 3

plt.bar(country['country'],country['population'],color=['r','g','b','c','m','y','k'])

plt.title("Population Report")

plt.xlabel("Country")

plt.ylabel("Population")

plt.grid()

plt.show()

country.to_csv('country.csv')

MySQL
Q – 2 Consider the table “Charity” and write SQL queries for the tasks that
follow:
 Table – Charity

item_id Itemname Price Qty Pdate

1 Shoes 7500 5 2022/11/30

2 Socks 475 3 2022/08/25

3 Jeans 3500 5 2022/10/19

4 T-Shirts 1400 4 2022/11/30

i) Display the name of week day when socks purchased.

select dayname(pdate) from charity;

ii) Display remainder after dividing price by qty

select mod(price,qty) from charity;

iii) Display the discount amount by 10% in two decimal places.

select round(price*0.10,2) from charity;

iv) Display the records of items purchased in the month 11.

select * from charity where month(pdate)=11

v) Display maximum qty purchased from the table.

select max(qty) from charity;

vi) Display itemname, price and qty in the descending order of price

select itemname, price, qty from charity order by price desc;

vii) Display item_id, itemname and position of s in each itemname.

select item_id,itemname, instr(itemname,'s') from charity;

Python Program
Q – 1 Write a program in python to create the following dataframe named
“employee” storing
the following details:

EName Post Salary Dt_join

101 Anil Manager 65000 2018-03-02

102 Akshay Clerk 33000 2018-05-01

103 Ajay Manager 75000 2018-09-15

104 Varun Analyst 66000 2018-04-11

105 Siddharth Developer 60000 2018-10-12

106 Rajesh Clerk 35000 2018-06-12

Considering the above dataframe answer the following queries by writing appropriate
command in python pandas.
i. Display Name, Post and Salary for all employees earning more than 60000 .
ii. Add a new row of your choice data.
iii. Transfer these data from dataframe to csv named employees.csv.
iv. Now plot a multi-line chart depicting the Employee Name on x-axis and their
corresponding Salary on y-axis, with appropriate Graph title, x-axis title, y-axis title,
legends and color etc.

Solution:

import pandas as pd

import matplotlib.pyplot as plt

d={'Ename':['Anil','Akshay','Ajay','Varun','Siddharth','Rajesh'],

'Post':['Manager','Clerk','Manager','Analyst','Developer','Clerk'],
 'Salary':[65000,33000,75000,66000,60000,35000],

'Dt_join':['2018-03-02','2018-05-01','2018-09-15','2018-04-11','2018-10-12','2018-06-12']}

employee=pd.DataFrame(d,index=[101,102,103,104,105,106])

#Answer 1

print("Answer 1")

em=employee.loc[:,['Ename','Post','Salary']]

print(em[em.Salary>60000].to_string(header=False,index=False))

#Answer 2

print("Answer 2")

employee.loc[employee.index[-1]+1]=['Ranveer','Analyst',65000,'2020-01-06']

print(employee.iloc[-1])

#Answer 3

employee.to_csv('employees.csv')
#Answer 4

plt.plot(employee['Ename'],employee['Salary'],color='r')

plt.title("Employee Analysis",color='blue')

plt.xlabel("Employee Names",color='red')

plt.ylabel("Salary",color='magenta')

plt.legend(['Salary'])

plt.show()

MySQL
Q – 2 Create the table “employee” and write sql queries for the following:

EName Post Salary Dt_join

101 Anil Manager 65000 2018-03-02

102 Akshay Clerk 33000 2018-05-01

103 Ajay Manager 75000 2018-09-15

104 Varun Analyst 66000 2018-04-11

105 Siddharth Developer 60000 2018-10-12

106 Rajesh Clerk 35000 2018-06-12

Write queries for the following:

a) Count and display employees post wise.
select post,count(*) from employee group by post;

b) Display 3 characters from 2nd place from the column ename.

select mid(ename,2,3) from employee;

c) Display last 2 characters of post column.

select right(post,2) from employee;

d) Display ename in lower letters

select lower(ename) from employee;

e) Display most senior employee

select min(Dt_join) from employee;

Write output of the following:

a) Select max(dt_join) from employee;

Ans.: max(dt_join)

———————-

2018-10-12

b) Select instr(ename,’a’) from employee

instr(ename,a)

—————-

2
c) Select power(length(post),2) from employee;

Ans.: power(length(post),2)

———————————

49

25

49

49

64

25
d) Select left(ename,4) from employee;

Ans.: left(ename,4)

anil

aksh

ajay

varu

sidd

raje

Python Program
Q – 1 Write a Python code for following questions:

medicineID Medicinename Price Manufacturer

0 5147 Paracetamol 15 Dwarkesh Pharma

 1 5274 D-Cold 20 Apollo Pharmacy

2 4296 Vicks VapoRub 45 Procter & Gamlbe

3 4175 Vicks Action 500 15 Procter & Gamble

4 4385 Soframycin 85 Sanofi

a) create above Data Frame “ved_medicines”

b) Display medicines and its price.
c) Display last 4 medicines
d) Display records of medicines whose price is more 20
e) Draw a bar chart which represent medicine name on x-axis and its price on y-axis.

Solution:

import pandas as pd

import matplotlib.pyplot as plt

d={'medicineid':[5147,5274,4296,4175,4385],

'medicinename':['Paracetamol','D-Cold','Vicks Vaporub','Vicks Action 500','Soframycin'],

'price':[15,20,45,15,85],

'manufacturer':['Dwarkesh Pharma','Apollo Pharmacy','Procter & Gamble','Procter & Gamble','Sanofi']

ved_medicines=pd.DataFrame(d)

#Answer 1

print("Answer 1")
print(ved_medicines.loc[:,['medicinename','price']].to_string(header=False,index=False))

#Answer 2

print("Answer 2")

print(ved_medicines.tail(4))

#Answer 3

print(ved_medicines[ved_medicines.price>20])

#Answer 4

plt.bar(ved_medicines['medicinename'],ved_medicines['price'],color=['r','g','b','c','y'])

plt.title("Medicine Report",color='blue')

plt.xlabel("Medicine Names",color='red')

plt.ylabel("Price",color='magenta')

plt.legend(['Price'])

plt.show()
MySQL
medicineID Medicinename Price Manufacturer

5147 Paracetamol 15 Dwarkesh Pharma

5274 D-Cold 20 Apollo Pharmacy

4296 Vicks VapoRub 45 Procter & Gamlbe

4175 Vicks Action 500 15 Procter & Gamble

4385 Soframycin 85 Sanofi

Observe the above medicine table and write queries for following:
a) Display the unique manufacturer from medicine

select distinct(manufacturer) from medicines;

b) Display manufacturer and total price for each manufacturer

select manufacturer,sum(price) from medicines group by manufacturer;

c) Display manufacturer and price in the ascending order of price

select manufacturer,price from medicines order by price;

d) Display first 4 characters of medicinename which medicine id contains 5 as last

digit

select left(medicinename,4) from medines where medicineid like '%5';

e) Display all medicine names in Capital letter

select upper(mdeicinename) from manufacturer;

Write output of the following queries:

a) Select length(right(medicinename,4)) from medicine;
Ans.: length(right(medicinename,4))

————————————-

4
b) Select instr(price,5) from medicine;

Ans.: instr(price,5)

————————–

2
c) Select round(medicineID/2,-1) from medicine;

Ans.: round(medicineID/2,-1)

————————–

2590

2640

2150

2090

2190

d) Select mid(medicinename,2,4) from medicine

Ans.: mid(medicinename,2,4)
arac

-Col

icks

icks

ofra