PRACTICAL File for Informatics Practices
INDEX
Part -1 Data Handling using Pandas
1. Create the following Series and do the specified operations:
EngAlph, having 26 elements with the alphabets as values and default index
values.
2. Create the following Series and do the specified operations:
Vowels, having 5 elements with index labels ‘a’,‘e’, ‘i’, ‘o’ and ‘u’ and all the
five values set to zero. Check if it is an empty series.
3. Using the Series created in Question 1, write commands for the following:
a) Find the dimensions, size and values of the Series EngAlph, Vowels, Friends,
MTseries, MonthDays.
b) Display the alphabets ‘e’ to ‘p’ from the Series EngAlph.
c) Display the first 10 values in the Series EngAlph.
d) Display the last 10 values in the Series EngAlph.
4. Create a series with these numbers: 21, 51, 71, 31, 12. Exchange all these values of
series by shifting each of them one to one position before and by shifting the first
value to last position.
5. Create a series of 10 numbers starting with 41 and with the increment of 3. Now add
7 all odd values and subtract 3 in even values. Reprint the updated series.
6. Create the following Data Frame Sales containing year wise sales figures for five
sales persons in INR. Use the years as column labels, and sales person names as
row labels.
2014 2015 2016 2017
Madhu 100.5 12000 20000 50000
Kusum 150.8 18000 50000 60000
Kinshuk 200.9 22000 100000 80000
Ankit 30000 30000 100000 80000
Shruti 40000 45000 125000 90000
7. Use the DataFrame created in Que-6 above to do 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.
(e) Display the last two rows of sales.
(f) Display the first two columns of sales.
8. Create a dictionary using the following data. Use this dictionary to create a
DataFrame sales2.
2019
Madhu 160000
Kusum 110000
Kinshuk 500000
Ankit 340000
Shruti 900000
9. Use the DataFrames created in question 7 and 8 above to do the following:
(a) Append the DataFrame sales2 to the dataframe sales.
(b) Change the dataframe sales such that it becomes year 2018.
(c) Display the sales made by Madhu and Ankit in the year 2017 and 2018.
(d) Display the sales made by all sales persons in the year 2017.
(e) Display the sales made by shruti 2016.
(f) Add data to sales for salesman sumeet where the sales made are [196.2 ,
37800, 52000, 78438 ,38852] in the years [2014 , 2015 , 2016 , 2017 , 2018]
respectively.
(g) Delete the data for the year 2014 from the dataframe sales.
(h) Delete the data fir sales man Kinshuk from the dataframe sales.
(i) Change the name of the salesperson Ankit to Vivaan and Madhu to Shailesh.
(j)Update the sale made by shailesh in 2018 to 100000.
10. Consider DataFrame df as shown below :
weig heig runsscor
name age ht ht ed
0 mayur 15 51 5.1 55
1 anil 16 48 5.2 25
2 viraj 17 49 5.1 71
3 viraj 17 51 5.3 53
mahes
4 h 16 48 5.1 51
5 viraj 17 59 5.3 50
a) Write command to calculate minimum value for each of the row from
subset of dataframe that contains age, weight, height, runsscored
b) Write command to calculate mean for last 3 rows.
11. Write a program to create a dataframe salesman using the series sales_person
which stored saleman names and quantity of sales of previous month.
12. Write the values of dataframe sales(created in Que-7,8,9) to a comma separated
file [Link] on the disk. Do not write the row labels and column labels.
Read the data in the file [Link] into a dataframe salesretrieved and
display it. Now update the row labels and column labels of sales retrieve d to be
the same as the of sales.
Part -2 Data Visualization
13. Write code to Plot the following data on line chart:
Runs in Overs 10 20
MI 110 224
RCB 85 210
Write a title for the chart "RUNS PER OVER".
Write the appropriate titles of both the axes.
Write code to Display legends.
Display red color for the line for MI.
Use the line style – dashed for RCB.
Display diamond style markers on both data points
14. Plot following data on bar graph and customize the graph as you wish:
English: 56,78,90,34
Science: 65,77,54,32
Maths: 45,67,43,41
15. Use above marks data and subplot Science and Maths marks.
16. Consider a Dataframe Mkdf as shown belo
name age PB Board
0 Karan 17 4 25
1 Amit 19 24 94
2 Ani 18 31 89
3 Pankaj 17 8 67
write a program to plot preboard marks and board marks from above
dataframe on the same scatter chart.
17. Generate random numbers from 1 to 70 and plot it on the histogram. Change
the outline color to black and the bar color should be yellow. Display the above
data in cumulative mode on histogram.
Part – 3 Database Query using MySQL
1. Create the following table named "Charity" and write SQL queries for the tasks
that follow:
Table: Charity
1 Bindra Jaspreet 5B, Gomti Nagar Lucknow 3500.50
2 Rana Monica 21 A, Bandra Mumbai 2768.00
3 Singh Jatinder 8, Punjabi Bagh Delhi 2000.50
4 Arora Satinder K/1, Shere Punjab Colony Mumbai 1900.00
5 Krishnan Vineeta A-75,Adarsh Nagar
(Contribution is in Rs.)
I. Display all first names in lowercase
II. Display all last names of people of Mumbai city in uppercase
III. Display Person Id along with First 3 characters of his/her name.
IV. Display first name concatenated with last name for all the employees.
V. Display length of address along with Person Id
VI. Display last 2 characters of City and Person ID.
VII. Display Last Names and First names of people who have "at" in the second or third
position in their first names.
VIII. Display the position of 'a' in Last name in every row.
IX. Display Last Name and First name of people who have "a" as the last character in their
First names.
X. Display the first name and last name concatenated after removing the leading and trailing
blanks.
XI. Display Person Id, last names and contribution rounded to the nearest rupee of all the
persons.
XII. Display Person Id, last name and contribution with decimal digits truncated of all the
persons.
XIII. Display Last name, contribution and a third column which has contribution divided by
10. Round it to two decimal points.
2. Consider the table "Grocer" and write SQL queries for the tasks that follow:
Table: Grocer
1 Rice 52.50 80 2010-02-01
2 Wheat 25.40 50 2010-03-09
3 Corn 50.80 100 2010-03-11
4 Semolina 28.90 50 2010-01-15
(Unit Price is per kg price)
I. Display Item name, unit price along with Date of purchase for all the Items.
II. Display Item name along with Month (in number) when it was purchased for all the items.
III. Display Item name along with year in which it was purchased for all the items.
IV. Display Item Id, Date of Purchase and day name of week (e.g. Monday) on which it was
purchased for all the items.
V. Display names of all the items that were purchased on Mondays or Tuesdays.
VI. Display the day name of the week on which Rice was purchased.
VII. Display the Item name and unit price truncated to integer value (no decimal digits)of all
the items.
VIII. Display current date