« Pandas
To create your sample DataFrame, download the excel file or csv file and then use read_excel() or read_csv() to create DataFrame. OR copy the code below to create the same sample DataFrame. From Excel or CSV file to DataFrame code is available below.
⇓ Download sample ( Excel file ) student.xlsx ⇓ csv file: student.csv
⇓ Download sample ( JASON file ) student.json ⇓ .html file: student.html
Copy to clipboard ( DataFrame )
import pandas as pd
dt={'id': {0: 1,1: 2,2: 3,3: 4,4: 5,5: 6,6: 7,7: 8,8: 9,9: 10,10: 11,11: 12,
12: 13,13: 14,14: 15,15: 16,16: 17,17: 18,18: 19,19: 20,20: 21,21: 22,
22: 23,23: 24,24: 25,25: 26,26: 27,27: 28,28: 29,29: 30,30: 31,31: 32,32: 33,
33: 34,34: 35},
'name': {0: 'John Deo',1: 'Max Ruin',2: 'Arnold',3: 'Krish Star',4: 'John Mike',
5: 'Alex John',6: 'My John Rob',7: 'Asruid',8: 'Tes Qry',9: 'Big John',
10: 'Ronald',11: 'Recky',12: 'Kty',13: 'Bigy',14: 'Tade Row',15: 'Gimmy',
16: 'Tumyu',17: 'Honny',18: 'Tinny',19: 'Jackly',20: 'Babby John',21: 'Reggid',
22: 'Herod',23: 'Tiddy Now',24: 'Giff Tow',25: 'Crelea',26: 'Big Nose',
27: 'Rojj Base',28: 'Tess Played',29: 'Reppy Red',30: 'Marry Toeey',
31: 'Binn Rott',32: 'Kenn Rein',33: 'Gain Toe',34: 'Rows Noump'},
'class': {0: 'Four', 1: 'Three',2: 'Three',3: 'Four',4: 'Four', 5: 'Four',
6: 'Five',7: 'Five',8: 'Six',9: 'Four',10: 'Six',11: 'Six',12: 'Seven',
13: 'Seven',14: 'Four',15: 'Four',16: 'Six',17: 'Five',18: 'Nine',19: 'Nine',
20: 'Four',21: 'Seven',22: 'Eight',23: 'Seven',24: 'Seven',25: 'Seven',
26: 'Three',27: 'Seven',28: 'Seven',29: 'Six',30: 'Four',31: 'Seven',32: 'Six',
33: 'Seven',34: 'Six'},'mark': {0: 75,1: 85,2: 55,3: 60,4: 60,5: 55,6: 78,
7: 85,8: 78,9: 55,10: 89,11: 94,12: 88,13: 88,14: 88,15: 88,16: 54,17: 75,
18: 18,19: 65,20: 69,21: 55,22: 79,23: 78,24: 88,25: 79,26: 81,27: 86,28: 55,
29: 79,30: 88,31: 90,32: 96,33: 69,34: 88},
'gender': {0: 'female',1: 'male',2: 'male',3: 'female',4: 'female',5: 'male',
6: 'male',7: 'male',8: 'male',9: 'female',10: 'female',11: 'female',12: 'female',
13: 'female',14: 'male',15: 'male',16: 'male',17: 'male',18: 'male',19: 'female',
20: 'female',21: 'female',22: 'male',23: 'male',24: 'male',25: 'male',26: 'female',
27: 'female',28: 'male',29: 'female',30: 'male',31: 'female',32: 'female',
33: 'male',34: 'female'}}
df=pd.DataFrame(data=dt)
df.set_index('id',inplace=True) # using id as index
To read Excel or CSV file .
import pandas as pd
df= pd.read_excel('D:\\my_data\\student.xlsx') # excel file
df= pd.read_csv('D:\\my_data\\student.csv') # csv file
Here it is shown as the file path is my_data directory inside D drive. You can change the path based on file location in your system.
Using SQLAlchemy
We will collect records from our sample student table in MySQL database. Collect SQL dump of sample student table below.
Read more on MySQL with SQLAlchemy connection . you can add path if you want the file to be created with the string ( sample is given above )
import pandas as pd
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid :pw @localhost/my_db ")
sql="SELECT * FROM student LIMIT 0,10 "
df = pd.read_sql(sql,my_conn)
Student table as Numpy array
Copy to clipboard Numpy Array
import numpy as np
student=np.dtype([('id','i'),('name','S20'),('class','S10'),('mark','i4'),('Gender','S10')])
data=[(1, 'John Deo', 'Four', 75, 'female'),\
(2, 'Max Ruin', 'Three', 85, 'male'),\
(3, 'Arnold', 'Three', 55, 'male'),\
(4, 'Krish Star', 'Four', 60, 'female'),\
(5, 'John Mike', 'Four', 60, 'female'),\
(6, 'Alex John', 'Four', 55, 'male'),\
(7, 'My John Rob', 'Five', 78, 'male'),\
(8, 'Asruid', 'Five', 85, 'male'),\
(9, 'Tes Qry', 'Six', 78, 'male'),\
(10, 'Big John', 'Four', 55, 'female'),\
(11, 'Ronald', 'Six', 89, 'female'),\
(12, 'Recky', 'Six', 94, 'female'),\
(13, 'Kty', 'Seven', 88, 'female'),\
(14, 'Bigy', 'Seven', 88, 'female'),\
(15, 'Tade Row', 'Four', 88, 'male'),\
(16, 'Gimmy', 'Four', 88, 'male'),\
(17, 'Tumyu', 'Six', 54, 'male'),\
(18, 'Honny', 'Five', 75, 'male'),\
(19, 'Tinny', 'Nine', 18, 'male'),\
(20, 'Jackly', 'Nine', 65, 'female'),\
(21, 'Babby John', 'Four', 69, 'female'),\
(22, 'Reggid', 'Seven', 55, 'female'),\
(23, 'Herod', 'Eight', 79, 'male'),\
(24, 'Tiddy Now', 'Seven', 78, 'male'),\
(25, 'Giff Tow', 'Seven', 88, 'male'),\
(26, 'Crelea', 'Seven', 79, 'male'),\
(27, 'Big Nose', 'Three', 81, 'female'),\
(28, 'Rojj Base', 'Seven', 86, 'female'),\
(29, 'Tess Played', 'Seven', 55, 'male'),\
(30, 'Reppy Red', 'Six', 79, 'female'),\
(31, 'Marry Toeey', 'Four', 88, 'male'),\
(32, 'Binn Rott', 'Seven', 90, 'female'),\
(33, 'Kenn Rein', 'Six', 96, 'female'),\
(34, 'Gain Toe', 'Seven', 69, 'male'),\
(35, 'Rows Noump', 'Six', 88, 'female')]
student=np.array(data) # adding data
print(student)
Pandas sample DataFrame creating from Excel or CSV or from MySQL or copy the dictionary with data
VIDEO
Full MySQL student table with SQL Dump
Full code to copy and add all rows to student table in SQLite Database →
Exercise 1 (basic )
« Pandas
Plotting graphs
Filtering of Data
← Subscribe to our YouTube Channel here