Python Assignment for Practical File Class XII…

1. Create a panda’s series from a dictionary of values and a ndarray.

2. Create a Data Frame quarterly sales where each row contains the item category, item name, and expenditure.
Group the rows by the category and print the total expenditure per category.
3. Create a data frame for examination result and display row labels, column labels data types of each column and
the dimensions.
4. Take data of your interest from an open source (e.g. [Link]), aggregate and summarize it. Then plot it using
different plotting functions of the Matplotlib library. (Line, Bar, Histogram, Pie Chart etc.)

5. Consider the following “[Link]” file containing the data as given below:
Roll Name Phy Chem Bio Maths IP
111 Ritu 90 88 92 90 99
129 Mridul 67 78 77 87 100
134 Divya 87 89 78 82 99
147 Biraj 67 82.5 76.5 78 99
150 Deepak 56.5 76.5 88 67 100
179 Sunil 76 66 77 87 100
a. Read the CSV file into a dataframe df .
b. Write the code to find the total_marks for each student and add it to the newly created dataframe.
c. Calculate the percentage obtained by each student under a new column “Average” in the
dataframe.
6. Consider the “[Link]” file and write the python code for the followings:

a. Read the file and print the first 10 and last 10 rows from a dataframe.
b. Find which car brand price is maximum
c. find All "audi" Cars informations?
d. Find each brand’s Highest price car?
e. Calculate total no. of cars per firm/brand?
f. Sort all cars by Price?
g. Find the average mileage of each car brand?
 7. The dataset for weather forecast is:
day:[‘01/01/201’,’01/02/2018’,’01/03/2018’,’01/04/2018’,’01/05/2018’,’01/01/2018’]
temp:[42,41,43,42,41,40]
windspeed:[6,7,2,4,7,2]
event:[‘Sunny’,’Rain’,’Sunny’,’Sunny’,’Rain’,’Sunny’]
Write a program to create a dataframe and perform all the aggregate and statistical functions on the basis of the
given dataset.

8. Write a Pandas program to convert all the string values to upper, lower cases in a given pandas series. Also find the
length of the string values.

9. Write a Pandas program to remove whitespaces, left sided whitespaces and right sided whitespaces of the string
values of a given pandas series.

10. Write a Pandas program to add leading zeros to the integer column in a pandas series and makes the length of the
field to 8 digit.

11. Write a Pandas program to add leading zeros to the character column in a pandas series and makes the length of the
field to 8 digit.

12. Write a Pandas program to capitalize all the string values of specified columns of a given DataFrame.

13. Write a Pandas program to count of occurrence of a specified substring in a DataFrame column.

14. Write a Pandas program to find the index of a given substring of a DataFrame column.

15. Write a Pandas program to find the index of a substring of DataFrame with beginning and end position.

16. Write a Pandas program to check whether alpha numeric values present in a given column of a DataFrame. Note:
isalnum() function returns True if all characters in the string are alphanumeric and there is at least one character, False
otherwise.

17. Write a Pandas program to check whether alphabetic values present in a given column of a DataFrame. Note:
isalpha() returns True if all characters in the string are alphabetic and there is at least one character, False otherwise.

18. Write a Pandas program to check whether only numeric values present in a given column of a DataFrame.

19. Write a Pandas program to check whether only lower case or upper case is present in a given column of a
DataFrame.

20. Write a Pandas program to check whether only proper case or title case is present in a given column of a DataFrame.

21. Write a Pandas program to check whether only space is present in a given column of a DataFrame.

22. Write a Pandas program to get the length of the string present of a given column in a DataFrame.
23. Write a Pandas program to get the length of the integer of a given column in a DataFrame.

24. Write a Pandas program to check if a specified column starts with a specified string in a DataFrame.

25. Write a Pandas program to swap the cases of a specified character column in a given DataFrame.

26. Write a Pandas program to convert a specified character column in upper/lower cases in a given DataFrame.

27. Write a Pandas program to convert a specified character column in title case in a given DataFrame.

28. Write a Pandas program to replace arbitrary values with other values in a given DataFrame.

29. Write a Pandas program to replace more than one value with other values in a given DataFrame.

30. Write a Pandas program to split a string of a column of a given DataFrame into multiple columns.

31. Write a Pandas program to extract email from a specified column of string type of a given DataFrame.

31. Write a Pandas program to extract hash attached word from twitter text from the specified column of a given
DataFrame.

33. Write a Pandas program to extract word mention someone in tweets using @ from the specified column of a given
DataFrame.

34. Write a Pandas program to extract only number from the specified column of a given DataFrame.

35. Write a Pandas program to extract only phone number from the specified column of a given DataFrame.

36. Write a Pandas program to extract year between 1800 to 2200 from the specified column of a given DataFrame.

37. Write a Pandas program to extract only non alphanumeric characters from the specified column of a given
DataFrame.

38. Write a Pandas program to extract only punctuations from the specified column of a given DataFrame.

39. Write a Pandas program to remove repetitive characters from the specified column of a given DataFrame.

40. Write a Pandas program to extract numbers greater than 940 from the specified column of a given DataFrame.

41. Write a Pandas program to extract numbers less than 100 from the specified column of a given DataFrame.

42. Write a Pandas program to check whether two given words present in a specified column of a given DataFrame.

43. Write a Pandas program to extract date (format: mm-dd-yyyy) from a given column of a given DataFrame.

44. Write a Pandas program to extract only words from a given column of a given DataFrame.

45. Write a Pandas program to extract the sentences where a specific word is present in a given column of a given
DataFrame.
46. Write a Pandas program to extract the unique sentences from a given column of a given DataFrame.

47. Write a Pandas program to extract words starting with capital words from a given column of a given DataFrame.

48. Write a Pandas program to remove the html tags within the specified column of a given DataFrame.

49. Consider a database LOANS with the following table:

Write the SQL commands for the task 1 to 35 and write the output for the SQL commands 36 to 50:

Create database and use it:

1. Create the database LOANS
2. Use the database LOANS
Create Table and Insert Records:
3. Create Table Loan_Accounts and insert tuples in it.
Simple Select
4. Display the details of all the loans.
5. Display the AccNo, Cust_Name, and Loan_Amount of all the loans.
Conditional Select using Where Clause
6. Display the details of all the loans with less than 40 instalments.
7. Display the AccNo and Loan_Amount of all the loans started before 01-04-2009.
8. Display the Int_Rate of all the loans started after 01-04-2009.
Using NULL
9. Display the details of all the loans whose rate of interest is NULL.
10. Display the details of all the loans whose rate of interest is not NULL.
Using DISTINCT Clause
11. Display the amounts of various loans from the table Loan_Accounts. A loan amount should appear only once.
12. Display the number of instalments of various loans from the table Loan_Accounts. An instalment should
appear only once..
Using Logical Operators (NOT, AND, OR)
13. Display the details of all the loans started after 31-12-2008 for which the number of
instalments are more than 36.
14. Display the Cust_Name and Loan_Amount for all the loans which do not have number of instalments 36.
15. Display the Cust_Name and Loan_Amount for all the loans for which the loan amount is less than 500000 or
int_rate is more than 12.
16. Display the details of all the loans which started in the year 2009.
17. Display the details of all the loans whose Loan_Amount is in the range 400000 to 500000.
18. Display the details of all the loans whose rate of interest is in the range 11% to 12%.
Using IN Operator
19. Display the Cust_Name and Loan_Amount for all the loans for which the number of
 instalments are 24, 36, or 48. (Using IN operator)
Using BETWEEN Operator
20. Display the details of all the loans whose Loan_Amount is in the range 400000 to
500000. (Using BETWEEN operator)
21. Display the details of all the loans whose rate of interest is in the range 11% to 12%.
(Using BETWEEN operator)
Using LIKE Operator
22. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the
Cust_Name ends with 'Sharma'.
23. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the
Cust_Name ends with 'a'.
24. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the
Cust_Name contains 'a'
25. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the
Cust_Name does not contain 'P'.
26. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the
Cust_Name contains 'a' as the second last character.
Using ORDER BY clause
27. Display the details of all the loans in the ascending order of their Loan_Amount.
28. Display the details of all the loans in the descending order of their Start_Date.
29. Display the details of all the loans in the ascending order of their Loan_Amount and
within Loan_Amount in the descending order of their Start_Date.
Using UPDATE, DELETE, ALTER TABLE
30. Put the interest rate 11.50% for all the loans for which interest rate is NULL.
31. Increase the interest rate by 0.5% for all the loans for which the loan amount is more than 400000.
32. For each loan replace Interest with (Loan_Amount*Int_Rate*Instalments) 12*100.
33. Delete the records of all the loans whose start date is before 2007.
34. Delete the records of all the loans of 'K.P. Jain'
35. Add another column Category of type CHAR(1) in the Loan table.
Find the Output of the following queries
36. SELECT cust_name, LENGTH(Cust_Name), LCASE(Cust_Name), UCASE(Cust_Name)
FROM Loan_Accounts WHERE Int_Rate < 11.00;
37. SELECT LEFT(Cust_Name, 3), Right(Cust_Name, 3), SUBSTR(Cust_Name, 1, 3) FROM
Loan_Accounts WHERE Int_Rate > 10.00;
38. SELECT RIGHT(Cust_Name, 3), SUBSTR(Cust_Name, 5) FROM Loan_Accounts;
39. SELECT DAYNAME(Start_Date) FROM Loan_Accounts;
40. SELECT ROUND(Int_Rate*110/100, 2) FROM Loan_Account WHERE Int_Rate > 10;
Write the output produced by the following SQL commands:
41. SELECT POW(4,3), POW(3,4);
42. SELECT ROUND(543.5694,2), ROUND(543.5694), ROUND(543.5694,-1);
43. SELECT TRUNCATE(543.5694,2), TRUNCATE(543.5694,-1);
44. SELECT LENGTH("Prof. M. L. Sharma");
45. SELECT CONCAT("INTELLIGENCE", "ARTIFICIAL") "FULL NAME";
46. SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAY(CURDATE());
47. SELECT DAYOFYEAR(CURDATE()), DAYOFMONTH(CURDATE()),
DAYNAME(CURDATE());
48. SELECT LEFT("Unicode",3), RIGHT("Unicode",4);
49. SELECT INSTR("UNICODE","CO"), INSTR("UNICODE","CD");
50. SELECT MID("Informatics",3,4), SUBSTR("Practices",3);

******