Informatics practices file
PYTHON PANDAS
CODES
Q.1-Write a program to create a Series object using an
ndarray that has 5 elements in the range 24 to 64.
Ans- import pandas as pd
import numpy as np
s6=[Link]([Link](24,64,5))
print(s6)
Q.2-Create and display a DataFrame from a 2D dictionary,
Sales, which stores the quarter-wise sales as inner
dictionary for two years, as shown below :
Sales = {'yr1': { 'Qtr1': 34500, 'Qtr2': 56000, 'Qtr3':
47000, 'Qtr4': 49000}, 'yr2': {'Qtr1': 44900, 'Qtr2':
46100, 'Qtr3': 57000, 'Qtr4': 59000}}
Ans-import pandas as pd
Sales = {'yr1': { 'Qtr1': 34500, 'Qtr2': 56000, 'Qtr3':
47000, 'Qtr4': 49000}, 'yr2': {'Qtr1': 44900, 'Qtr2':
46100, 'Qtr3': 57000, 'Qtr4': 59000}}
dfsales=[Link](Sales)
1 | Page
Informatics practices file
print(dfsales)
Q.3-Write a program to create a dataframe from a 2D list.
Specify own index labels.
Ans- import pandas as pd
list2=[[25,45,60],[34,67,89],[88,90,56]]
df2=[Link](list2,index=['row1','row2','row2'])
print(df2)
Q4-White a program to create a DataFrame from a 2D
array as shown below:
101 113 124
130 140 200
115 216 217
Ans- import pandas as pd
import numpy as np
arr2=[Link]([[101,113,124],[130,140,200],[115,216,217]])
dtf3=[Link](arr2)
2 | Page
Informatics practices file
print(dtf3)
Q5-Write a program to create a DataFrame to store
weight, age and names of 3 people. Print the Dataframe
and its transpose.
Ans- import pandas as pd
df=[Link]({'Weight':[42,75,66],
'Name':['Arnav','Charles','Guru'],
'Age':[15,22,35]})
print('Original DataFrame')
print(df)
print('Transpose:')
print(df.T)
3 | Page
Informatics practices file
Q.6- From the series Ser1 of areas that stores areas
of states in km², find out the areas that are more
than 50000 km²
Ans- import pandas as pd
Ser1=[Link]([34567,890,450,67892,34677,78902,256
711,678291,
637632,25723,2367,11789,345,256517])
print(Ser1[Ser1>50000])
Q.7-Consider the following dataframe saleDf:
Write a program to add a column namely orders having
values 6000,6700,6200 and 6000 respectively for the
zones A,B,C and D. The program should also add a new
row for a new zone [Link] some dummy values in
this row.
Ans- import pandas as pd
saleDf = [Link]({
4 | Page
Informatics practices file
'zoneE': [10000, 20000, 30000, 40000],
'Orders': [0, 0, 0, 0]
})
saleDf['Orders'] = [6000, 6700, 6200, 6000]
[Link][:, 'zoneE'] = [50000, 45000, 5000, 7000]
print(saleDf)
Q.8- Given a Series that stores the area of some states in
km² Write code to find out the biggest and smallest three
ared from the given Series. Given series has been created
like this:
[Link]([34567, 898, 450, 67892, 34677, 78982,
256711, 678291, 637632, 25723, 2367, 11789, 345,
256517])
Ans- import pandas as pd
Ser1 = [Link]([34567, 890, 450, 67892, 34677, 78902,
256711, 678291, 637632, 25723, 2367, 11789, 345, 256517])
print("Top 3 biggest areas are:")
print(Ser1.sort_values().tail(3))
print("3 smallest areas are:")
print(Ser1.sort_values().head(3))
5 | Page
Informatics practices file
Q.9- Create the given DataFrame ‘health’
Write suitable Python statements for the following:
(1) Remove the row containing details of disease named
Tuberculosis.
(ii) Add a new disease named 'Malaria' caused by
'Protozoa'.
(iii) Display the last 2 rows.
Ans- import pandas as pd
health = [Link]({
'Diseasename': ['Common cold', 'Chickenpox',
'Cholera', 'Tuberculosis'],
'Agent': ['Virus', 'Virus', 'Bacteria', 'Bacteria']
})
6 | Page
Informatics practices file
health = health[health['Diseasename'] != 'Tuberculosis']
[Link][4] = ['Malaria', 'Protozoa']
print([Link](2))
Q.10-Given a DataFrame df:
Write a program to display only the weight of first and
third rows.
Ans- import pandas as pd
df = [Link]({
'Age': [15, 22, 35],
'Name': ['Arnav', 'Charles', 'Guru'],
'Weight': [42, 75, 66]
})
print([Link][[0, 2], [2]])
7 | Page
Informatics practices file
Q.11- Create the following DataFrame Sales containing
year wise sales figures for five salespersons in INR the
use the years as column labels, and salesperson names
as row labels.
Ans-
Q.12- Use the DataFrame created in Question 9 above to
do the following:
(a) Display the row labels of Sales.
(b) Display the column labels of Sales.
(c) Display the data types of each column of Sales.
(d) Display the dimensions, shape, size and values of
Sales.
8 | Page
Informatics practices file
(e) Display the last two rows of Sales.
(f)Display the first two columns of Sales.
(g) Create a dictionary using the following data. Use this
dictionary to create a DataFrame Sales2.
(h)Check if sales 2 is empty or it contains data
Ans- (a)import pandas as od
[Link]
(b) import pandas as pd
[Link]
(c) import pandas as pd
print([Link])
9 | Page
Informatics practices file
(d) import pandas as pd
[Link],[Link],[Link],[Link]
(e) import pandas as pd
[Link][3:,]
(f) import pandas as pd
[Link][:,:2]
(g) import pandas as pd
10 | P a g e
Informatics practices file
d1={2018:[160000,110000,500000,340000,900000]}
Sales2=[Link](d1,index=['Madhu','Kusum','Kinsh
uk','Ankit','Shruti'])
Sales2
(h) import pandas as pd
[Link]
PLOTTING WITH PYPLOT
CODES
Q.13-Given a series nfib that contains reversed
Fibonacci numbers with Fibonacci numbers as
shown below:
[0,-1,-1,-2,-3,-5,-8,-13,-21,-
34,0,1,1,2,3,5,8,13,21,34]
11 | P a g e
Informatics practices file
Ans= import [Link] as plt
import numpy as np
nfib= [0,-1,-1,-2,-3,-5,-8,-13, -21, -34, 0, 1, 1, 2, 3, 5, 8,
13, 21, 34]
[Link](range(-10, 10), nfib, 'mo', markersize=5,
markeredgecolor='k', linestyle='solid')
[Link](True)
[Link]()
Q.14- Write Python code to draw the following bar graph
representing the total sales in each [Link] the
title,label for X-axis and Y-axis
Use the following data for plotting the graph:
sales = [450, 300, 500, 650]
qtr = ["QTR1", "QTR2", "QTR3", "QTR4"]
12 | P a g e
Informatics practices file
Ans- import [Link] as plt
sales = [450, 300, 500, 650]
qtr = ["QTR1", "QTR2", "QTR3", "QTR4"]
[Link](qtr, sales)
[Link]("Quarter")
[Link]("Sales")
[Link] ("Sales each quarter")
[Link]()
MY SQL CODES
Tables used:
Table empl :
13 | P a g e
Informatics practices file
Table SalaryGrade:
Table dept:
14 | P a g e
Informatics practices file
Table SALESMAN:
ORDERS Table:
15 | P a g e
Informatics practices file
CUSTOMERS Table:
ORDERITEMS Table:
PRODUCTS Table:
16 | P a g e
Informatics practices file
17 | P a g e
Informatics practices file
Q1: Write a query to join two tables empl and dept
on the basis of field deptno.
SELECT*FROM empl, dept
WHERE [Link] =[Link];
Q2: Display details like department number,
department name, employee number, employee
name,job and salary. And order the rows by
employee number with department number.
SELECT [Link], DNAME, EMPNO, ENAME, JOB, SAL
FROM empl, dept WHERE [Link]= [Link]
ORDER BY [Link], EMPNO;
18 | P a g e
Informatics practices file
Q3: Refer Q2 .Do this only for SALES department.
SELECT [Link], DNAME, EMPNO,ENAME, JOB, SAL
FROM empl e, dept d
WHERE [Link]= [Link]
AND DNAME= “SALES”
ORDER BY [Link], EMPNO ;
Q4: Display details like department number,
department name, employee number, employee
name, job and salary. And order the rows by
employee number with department number. These
details should be only for employees earning
atleast 1500 rupees and of SALES department.
SELECT [Link], DNAME, EMPNO, ENAME, JOB, SAL
FROM empl,dept
WHERE [Link]= [Link]
19 | P a g e
Informatics practices file
AND DNAME=’SALES’ AND SAL>=1500
ORDER BY [Link], EMPNO ;
Q5. Refer to table Empl, dept and salarygrade.
Display the employee details in the following
format:
Ename Department Job Sal Grade
SELECT ENAME, DNAME, EMPNO, JOB, SAL, Grade
FROM empl,dept, salarygrade
WHERE [Link]= [Link]
AND SAL BETWEEN LOSAL AND HISAL
ORDER BY [Link], EMPNO ;
20 | P a g e
Informatics practices file
Q6: Display employee details for Analysis in the
following format:
Ename Job Sal Grade
SELECT ENAME, JOB, SAL, GRADE
FROM empl, salarygrade
WHERE SAL BETWEEN LOSAL AND HISAL
AND JOB= “ANALYST”;
21 | P a g e
Informatics practices file
Q7: Consider the Orders and Customers tables
given above. Write and SQL query to list all
orders(OrderId) with customer information.
(name,city and country)
SELECT OrderId, TotalAmount, Name, City, Country
FROM ORDERS, CUSTOMERS
WHERE [Link]=[Link];
Q8: Consider the Orders and Customers tables
given above. Write an SQL query to list all
customer details (name, phone) along with order
date.
SELECT Name, City, Country, Orderdate FROM ORDERS,
CUSTOMERS
WHERE [Link]=[Link];
22 | P a g e
Informatics practices file
Q9: Consider the Orders and OrderItems tables
given above. Write an SQL query to list order
details along with product ids and quantities.
SELECT [Link], OrderDate, ProductId, Quantity
FROM ORDERS, ORDERITEMS
WHERE [Link]=[Link];
Q10: Consider the Orders and OrderItems tables
given above. Write a query to list all the orders
with product names, quantities and prices,
arranged OrderId wise.
23 | P a g e
Informatics practices file
SELECT [Link], [Link], [Link], [Link]
FROM ORDERS O, PRODUCTS P, ORDERITEMS I
WHERE [Link]= [Link]
AND [Link]=[Link]
ORDER BY [Link];
24 | P a g e
Informatics practices file
Q11: Write an SQL query to union cities from tables
venue1 and venue2.
SELECT City FROM venue1
UNION
SELECT City FROM venue2;
Q12: Write an SQL query to union cities from
venue2 and venue1.
SELECT City FROM venue2
UNION
SELECT City FROM venue1;
25 | P a g e
Informatics practices file
Q13: Write an SQL query to union cities from tables
venue1 and venue2 containing all the rows.
SELECT City FROM venue1
UNION ALL
SELECT City FROM venue2;
Q14: Write an SQL query to get cities only in table
venue1 and not in table venue2.
SELECT [Link] FROM venue1 v1
LEFT JOIN venue2 v2 ON [Link]=[Link]
WHERE [Link] IS NULL;
26 | P a g e
Informatics practices file
Q15: Write an SQL query to get cities common to
tables venue1 and venue2.
SELECT DISTINCT [Link] FROM venue1 v1
INNER JOIN venue2 v2
ON [Link]=[Link] ;
Consider table SALESMAN for the following
questions.
Q16: Display salesman name and bonus after
rounding off to zero decimal places.
27 | P a g e
Informatics practices file
SELECT SNAME, ROUND( BONUS,0) FROM SALESMAN;
Q17: Display the position of occurence of the string
"ta" in salesman name.
SELECT INSTR( SNAME, “ta”) FROM SALESMAN;
Q18: Display the four characters from salesman
name starting from second character.
SELECT MID(SNAME, 2,4) FROM SALESMAN;
28 | P a g e
Informatics practices file
Q19: Display the month name for the date of join of
salesman.
SELECT MONTHNAME(DATE_OF_JOIN) FROM
SALESMAN;
29 | P a g e
Informatics practices file
Q20: Display the name of the weekday for the date
of the join of salesman.
SELECT DAYNAME(DATE_OF_JOIN) FROM SALESMAN;
30 | P a g e