#using nested dictinary

import pandas as pd
l = [{'name': 'arun', 'age': 21}, {'name': 'bala', 'age': 23}, {'name': 'charan',
'age': 22}]
d=pd.DataFrame(l,index=['s1','s2','s3'],columns=['name','age'])
print(d)

#using nested
import pandas as pd
D={'Arun':65,'Bala':91,'Charan':74,'Dinesh':80,'usha':85}
S=pd.Series(D)

#updating existing value of series object

import pandas as pd
d={'dhanya':80000,'vindhya':89000,'anu':79000,'spoorthy':87000}
a=pd.Series(d)
print('employee salary before updating:')
print(a)
print('\n')
b=input("do you want to update the salary of employee(Y/N)?")
if b=='Y':
name=input('enter the name of the employee:')
if name in a:
salary=float(input('enter the new salary:'))
a[name]=salary
print('\n salary updated sucessfully')
print('\n salary after updating sucessfully:')
print(a)
else:
print('\n employee not found')
else:
print('thank you')

#program using head and tail

import pandas as pd
marks=[78,89,77,34,67,73,98,35,65,80]
s=pd.Series(marks,index=['s1','s2','s3','s4','s5','s6','s7','s8','s9','s10'])
print('the first 5 students marks are:')
print(s.head())
print('\n')
print('the last 5 students marks are')
print(s.tail())

#program to filter the data of a dataframe

import pandas as pd
d={'stu_name':['anu','bala','arun','charan','manvi'],'degree':
['mba','mca','me','msc','mca'],'percentage':[90,96,84,75,63]}
df=pd.DataFrame(d,index=['s1','s2','s3','s4','s5'])
print(df.loc[df['percentage']>85])

#iterows() and iteritems()

import pandas as pd
d={'stu_name':['anu','bala','arun','charan','manvi'],'degree':
['mba','mca','me','msc','mca'],'percentage':[90,96,84,75,63]}
df=pd.DataFrame(d,index=['s1','s2','s3','s4','s5'])
for(row_index,row_values)in df.iterrows():
print(row_index)
print(row_values)
 print()
for(column_name,column_values)in df.items():
print(column_name)
print(column_values)
print()

#importing and exporting csv file

import pandas as pd
emp={'name':['anu','ram','raj','manu','rajan'],'age':[26,45,34,42,37],'state':
['tn','ap','tn','ka','ap'],'salary':[26000,63120,89564,78120,96321]}
df=pd.DataFrame(emp,index=['e1','e2','e3','e4','e5'])
df.to_csv(r"C:\Users\acer\Desktop\dhanya.py\employees.csv")
data=pd.read_csv(r"C:\Users\acer\Desktop\dhanya.py\employees.csv")
print(data)
#note: df.to_csv(r"C:\Users\acer\Desktop\dhanya.py\employees.csv",index_col=0) if
we give like this in the output unnmaed word will not come

#attributes of dataframe
import pandas as pd
d={'stu_name':['anu','bala','arun','charan','manvi'],'degree':
['mba','mca','me','msc','mca'],'percentage':[90,96,84,75,63]}
df=pd.DataFrame(d,index=['s1','s2','s3','s4','s5'])
print('\nIndex of the DataFrame, df.index:')
print(df.index)
print('\nColumns of the DataFrame, df.columns:')
print(df.columns)
print('\nAxes of the DataFrame, df.axes:')
print(df.axes)
print('\nData types of the DataFrame, df.dtypes:')
print(df.dtypes)
print('\nShape of the DataFrame, df.shape:')
print(df.shape)
print('\nDimension of the DataFrame, df.ndim:')
print(df.ndim)
print('\nTranspose of the DataFrame, df.T:')
print(df.T)

#accesing values of rows and columns of a dtaframe(deleting row of mp and inserting

new column)
import pandas as pd
d={'toys':[7951,8963,7469,5685],'books':[8541,6325,4562,2356],'uniform':
[456,236,459,546],'shoes':[561,895,444,656]}
df=pd.DataFrame(d,index=['ap','od','mp','up'])
df['bags']=[6954,6952,4862,3569]
print('after adding new column')
print(df)
print('\n')
s=df.drop('mp')
print('after deleting mp details')
print(s)

#accesing values of rows and columns

import pandas as pd
d={'toys':[7951,8963,7469,5685],'books':[8541,6325,4562,2356],'uniform':
[456,236,459,546],'shoes':[561,895,444,656]}
df=pd.DataFrame(d,index=['ap','od','mp','up'])
print(df)
print('\n')
print(df['toys'])
print('\n')
s=df.loc[['ap','od']]
print(s)
print('\n')
a=df.loc['mp':'up',['books','uniform']]
print(a)
print('\n')
b=df.loc['ap':'mp','toys':'uniform']
print(b)

#plotting bar chart

import pandas as pd
import matplotlib.pyplot as plt
DF=pd.DataFrame({'subject':['eng','phy','math','chem','ip'],'sub_avg':
[78,85,78,92,98]},index=['s1','s2','s3','s4','s5'])
DF.to_csv(r"C:\Users\acer\Documents\csv.py\matplotlib.csv",index=False)
data=pd.read_csv(r"C:\Users\acer\Documents\csv.py\matplotlib.csv")
plt.xlabel('subjects')
plt.ylabel('Average')
plt.title('subject average analysis')
plt.bar(data['subject'],data['sub_avg'],width=0.25,color=['c','r','g','b','m'])
plt.show()

#line chart
import matplotlib.pyplot as plt
weeks=[1,2,3,4]
onion=[45,25,32,80]
brinjal=[16,18,45,50]
plt.title('price analysis')
plt.xlabel('weeks')
plt.ylabel('prices')
plt.plot(weeks,onion,marker='d',markersize=10,color='r',label='onion')
plt.plot(weeks,brinjal,color='g',label='brinjal')
plt.legend()
plt.show()

#histogram
import matplotlib.pyplot as plt
marks=[40,60,56,20,35,70,60,89,90,33]
plt.title('maths marks histogram of class x11')
plt.xlabel('marks ranges')
plt.ylabel('no of students')
plt.hist(marks,bins=[0,33,45,60,100],edgecolor='red')
plt.show()

#multiple bar chart

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
Data={'Subject':['Eng','A/c','Eco','Bst','IP'],
'ClassA':[85,78,92,80,98],
'ClassB':[90,65,88,75,87],
'ClassC':[75,82,60,90,94]}
DF=pd.DataFrame(Data)
DF.to_csv("C:\\Users\\Acer11\\Desktop\\class 12 b\\class 12 practicels\\stu.csv")
data=pd.read_csv("C:\\Users\\Acer11\\Desktop\\class 12 b\\class 12 practicels\\
stu.csv")
plt.xlabel('Subject')
plt.ylabel('Average')
plt.title('Subject wise average for Class A,B,C')
x=np.arange(5)
plt.bar(DF['Subject'],DF['ClassA'],width=0.15,label='ClassA')
plt.bar(x+0.15,DF['ClassB'],width=0.15,label='ClassB')
plt.bar(x+0.30,DF['ClassC'],width=0.15,label='ClassC')
plt.legend()
plt.show()

mysql> show databases;

+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| practical |
+--------------------+
4 rows in set (0.01 sec)

mysql> use mysql;

Database changed
mysql> create database employees;
Query OK, 1 row affected (0.01 sec)

mysql> use employees;

Database changed
mysql> create table info(id int primary key,
-> name varchar(20),
-> gender varchar(3),
-> age int,
-> dept varchar(15),
-> doj date,
-> salary int,
-> city varchar(10));
Query OK, 0 rows affected (0.03 sec)

mysql> desc info;

+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| empid | int | NO | PRI | NULL | |
| name | varchar(15) | YES | | NULL | |
| gender | varchar(3) | YES | | NULL | |
| age | int | YES | | NULL | |
| dept | varchar(15) | YES | | NULL | |
| doj | date | YES | | NULL | |
| salary | int | YES | | NULL | |
| city | varchar(10) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> show tables;

+----------------+
| Tables_in_emps |
+----------------+
| employees |
| info |
+----------------+

mysql> insert into info values(1,'praveen','m',25,'sales','1986-06-

08',20000,'chennai'),
(2,'arun','m',29,'marketing','1989-09-26',22000,'chennai'),
(3,'usha','f',27,'finance','1994-8-9',25000,'banglore'),
(4,'bala','m',28,'sales','1990-3-23',27000,'null'),
(5,'ram','f',31,'marketing','1990-4-23',27000,'mumbai'),
(6,'nisha','f',26,'null','1991-2-24',18000,'banglore'),
(7,'manoj','m',32,'finance','1982-5-6',30000,'goa');
Query OK, 7 rows affected (0.02 sec)
Records: 7 Duplicates: 0 Warnings: 0

mysql> select*from info;

+-------+---------+--------+------+-----------+------------+--------+----------+
| empid | name | gender | age | dept | doj | salary | city |
+-------+---------+--------+------+-----------+------------+--------+----------+
| 1 | praveen | m | 25 | sales | 1986-06-08 | 20000 | chennai |
| 2 | arun | m | 29 | marketing | 1989-09-26 | 22000 | chennai |
| 3 | usha | f | 27 | finance | 1994-08-09 | 25000 | banglore |
| 4 | bala | m | 28 | sales | 1990-03-23 | 27000 | null |
| 5 | ram | f | 31 | marketing | 1990-04-23 | 27000 | mumbai |
| 6 | nisha | f | 26 | null | 1991-02-24 | 18000 | banglore |
| 7 | manoj | m | 32 | finance | 1982-05-06 | 30000 | goa |
+-------+---------+--------+------+-----------+------------+--------+----------+
7 rows in set (0.00 sec)

mysql> update info set gender='m' where empid=5;

Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select*from info;

+-------+---------+--------+------+-----------+------------+--------+----------+
| empid | name | gender | age | dept | doj | salary | city |
+-------+---------+--------+------+-----------+------------+--------+----------+
| 1 | praveen | m | 25 | sales | 1986-06-08 | 20000 | chennai |
| 2 | arun | m | 29 | marketing | 1989-09-26 | 22000 | chennai |
| 3 | usha | f | 27 | finance | 1994-08-09 | 25000 | banglore |
| 4 | bala | m | 28 | sales | 1990-03-23 | 27000 | null |
| 5 | ram | m | 31 | marketing | 1990-04-23 | 27000 | mumbai |
| 6 | nisha | f | 26 | null | 1991-02-24 | 18000 | banglore |
| 7 | manoj | m | 32 | finance | 1982-05-06 | 30000 | goa |
+-------+---------+--------+------+-----------+------------+--------+----------+
7 rows in set (0.00 sec)

mysql> select name from info order by name desc;

+---------+
| name |
+---------+
| usha |
| ram |
| praveen |
| nisha |
| manoj |
| bala |
| arun |
+---------+
7 rows in set (0.00 sec)
mysql> select name from info order by name asc;
+---------+
| name |
+---------+
| arun |
| bala |
| manoj |
| nisha |
| praveen |
| ram |
| usha |
+---------+
7 rows in set (0.00 sec)

mysql> select sum(salary) from info;

+-------------+
| sum(salary) |
+-------------+
| 169000 |
+-------------+
1 row in set (0.00 sec)

mysql> mysql> select max(salary), min(salary) from info;

+-------------+-------------+
| max(salary) | min(salary) |
+-------------+-------------+
| 30000 | 18000 |
+-------------+-------------+
1 row in set (0.01 sec)

mysql> select count(salary)from info where salary>25000;

+---------------+
| count(salary) |
+---------------+
| 3 |
+---------------+
1 row in set (0.00 sec)

mysql> select dept ,sum(salary) from info group by dept;

+-----------+-------------+
| dept | sum(salary) |
+-----------+-------------+
| sales | 47000 |
| marketing | 49000 |
| finance | 55000 |
| null | 18000 |
+-----------+-------------+
4 rows in set (0.00 sec)

mysql> select dept from info group by dept having count(*)>=2;

+-----------+
| dept |
+-----------+
| sales |
| marketing |
| finance |
+-----------+
3 rows in set (0.00 sec)
mysql> select name,city from info;
+---------+----------+
| name | city |
+---------+----------+
| praveen | chennai |
| arun | chennai |
| usha | banglore |
| bala | null |
| ram | mumbai |
| nisha | banglore |
| manoj | goa |
+---------+----------+
7 rows in set (0.00 sec)

mysql> select*from info where city='chennai';

+----+---------+--------+------+-----------+------------+--------+---------+
| id | name | gender | age | dept | doj | salary | city |
+----+---------+--------+------+-----------+------------+--------+---------+
| 1 | praveen | m | 25 | sales | 1986-06-08 | 20000 | chennai |
| 2 | arun | m | 29 | marketing | 1989-09-26 | 22000 | chennai |
+----+---------+--------+------+-----------+------------+--------+---------+
2 rows in set (0.00 sec)

mysql> select name ,salary from info where salary>15000 and gender<>'m';
+-------+--------+
| name | salary |
+-------+--------+
| usha | 25000 |
| nisha | 18000 |
+-------+--------+
update info set salary =salary=(salary*0.10)where city='chennai' and gender='m';
Query OK, 2 rows affected (0.01 sec)

select salary from info;

+--------+
| salary |
+--------+
| 0 |
| 0 |
| 25000 |
| 27000 |
| 27000 |
| 18000 |
| 30000 |
+--------+

delete from info where empid=6;

Query OK, 1 row affected (0.02 sec)

mysql> select empid from info;

+-------+
| empid |
+-------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 7 |
+-------+
6 rows in set (0.00 sec)

use stu;
Database changed
mysql> desc st;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| rollno | int | NO | PRI | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | varchar(2) | YES | | NULL | |
| age | int | YES | | NULL | |
| dept | varchar(10) | YES | | NULL | |
| doa | date | YES | | NULL | |
| percentage | float | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

mysql> select*from st;

+--------+--------+--------+------+-------+------------+------------+
| rollno | name | gender | age | dept | doa | percentage |
+--------+--------+--------+------+-------+------------+------------+
| 1 | arun | m | 16 | cs | 1997-01-01 | 97.8 |
| 2 | ankith | m | 17 | ip | 1998-06-24 | 95.2 |
| 3 | anu | f | 15 | hindi | 1996-07-02 | 81 |
| 4 | bala | m | 17 | ip | 1999-07-01 | 75.5 |
+--------+--------+--------+------+-------+------------+------------+
4 rows in set (0.01 sec)

mysql> insert into st values(5,'charan','m',16,'hindi','1997-06-27',92.13);

Query OK, 1 row affected (0.02 sec)

mysql> insert into st values(6,'deepa','f',16,'history','1997-06-27',60.54),

(7,'dinesh','m',15,'cs','1996-08-25',82);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select*from st;

+--------+--------+--------+------+---------+------------+------------+
| rollno | name | gender | age | dept | doa | percentage |
+--------+--------+--------+------+---------+------------+------------+
| 1 | arun | m | 16 | cs | 1997-01-01 | 97.8 |
| 2 | ankith | m | 17 | ip | 1998-06-24 | 95.2 |
| 3 | anu | f | 15 | hindi | 1996-07-02 | 81 |
| 4 | bala | m | 17 | ip | 1999-07-01 | 75.5 |
| 5 | charan | m | 16 | hindi | 1997-06-27 | 92.13 |
| 6 | deepa | f | 16 | history | 1997-06-27 | 60.54 |
| 7 | dinesh | m | 15 | cs | 1996-08-25 | 82 |
+--------+--------+--------+------+---------+------------+------------+
7 rows in set (0.00 sec)

select power(age,2)from st where doa like'%-08-%';

+--------------+
| power(age,2) |
+--------------+
| 225 |
+--------------+
1 row in set (0.00 sec)

select mod(percentage,3)from st;

+--------------------+
| mod(percentage,3) |
+--------------------+
| 1.8000030517578125 |
| 2.1999969482421875 |
| 0 |
| 0.5 |
| 2.1299972534179688 |
| 0.5400009155273438 |
| 1 |
+--------------------+

select name,round(percentage,0)from st;

+--------+---------------------+
| name | round(percentage,0) |
+--------+---------------------+
| arun | 98 |
| ankith | 95 |
| anu | 81 |
| bala | 76 |
| charan | 92 |
| deepa | 61 |
| dinesh | 82 |
+--------+---------------------+
7 rows in set (0.01 sec)

select name,round(mod(percentage,3),2)from st;

+--------+----------------------------+
| name | round(mod(percentage,3),2) |
+--------+----------------------------+
| arun | 1.8 |
| ankith | 2.2 |
| anu | 0 |
| bala | 0.5 |
| charan | 2.13 |
| deepa | 0.54 |
| dinesh | 1 |
+--------+----------------------------+
7 rows in set (0.00 sec)

select lower(dept)as dept_lowercase from st;

+----------------+
| dept_lowercase |
+----------------+
| cs |
| ip |
| hindi |
| ip |
| hindi |
| history |
| cs |
+----------------+

select dept,name,length(dept)as department_name_length from st;

+---------+--------+------------------------+
| dept | name | department_name_length |
+---------+--------+------------------------+
| cs | arun | 2 |
| ip | ankith | 2 |
| hindi | anu | 5 |
| ip | bala | 2 |
| hindi | charan | 5 |
| history | deepa | 7 |
| cs | dinesh | 2 |
+---------+--------+------------------------+

select substring(dept,1,2)as first_two_charcters from st;

+---------------------+
| first_two_charcters |
+---------------------+
| cs |
| ip |
| hi |
| ip |
| hi |
| hi |
| cs |
+---------------------+

select right(dept,2)as last_two_charcters from st;

+--------------------+
| last_two_charcters |
+--------------------+
| cs |
| ip |
| di |
| ip |
| di |
| ry |
| cs |
+--------------------+
7 rows in set (0.00 sec)

mysql> select substring(name,3,5)as extracte_charcters_of_name from st;

+----------------------------+
| extracte_charcters_of_name |
+----------------------------+
| un |
| kith |
| u |
| la |
| aran |
| epa |
| nesh |
+----------------------------+
7 rows in set (0.00 sec)

select name,doa from st;

+--------+------------+
| name | doa |
+--------+------------+
| arun | 1997-01-01 |
| ankith | 1998-06-24 |
| anu | 1996-07-02 |
| bala | 1999-07-01 |
| charan | 1997-06-27 |
| deepa | 1997-06-27 |
| dinesh | 1996-08-25 |
+--------+------------+

mysql> select name,dayname(doa)as admission_day from st;

+--------+---------------+
| name | admission_day |
+--------+---------------+
| arun | Wednesday |
| ankith | Wednesday |
| anu | Tuesday |
| bala | Thursday |
| charan | Friday |
| deepa | Friday |
| dinesh | Sunday |
+--------+---------------+

select name,year(doa)as joining_year from st where dept='ip';

+--------+--------------+
| name | joining_year |
+--------+--------------+
| ankith | 1998 |
| bala | 1999 |
+--------+--------------+

select name,date_format(doa,'%b')as month_of_admission from st;

+--------+--------------------+
| name | month_of_admission |
+--------+--------------------+
| arun | Jan |
| ankith | Jun |
| anu | Jul |
| bala | Jul |
| charan | Jun |
| deepa | Jun |
| dinesh | Aug |
+--------+--------------------+

select name,date_format(doa,'%M')as month_of_admission from st;

+--------+--------------------+
| name | month_of_admission |
+--------+--------------------+
| arun | January |
| ankith | June |
| anu | July |
| bala | July |
| charan | June |
| deepa | June |
| dinesh | August |
+--------+--------------------+
7 rows in set (0.00 sec)

select name from st where month (doa)=6;

+--------+
| name |
+--------+
| ankith |
| charan |
| deepa |
+--------+