Creating sample Pandas DataFrame student from Excel, CSV or MySQL or to copy the code

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

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

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

Full code to copy and add all rows to student table in SQLite Database
Exercise 1 (basic )
Pandas Plotting graphs Filtering of Data

Numpy arrays Python & MySQL Python- Tutorials

Subscribe to our YouTube Channel here

Pandas sample student DataFrame

Using SQLAlchemy

Student table as Numpy array

Subscribe