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
Download sample XML file: student.xml

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'}}
# Creating a Pandas DataFrame
df = pd.DataFrame(data=dt)

# Setting the 'id' column as the DataFrame index
df.set_index('id', inplace=True)

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,text
my_conn = create_engine("mysql+mysqldb://userid:pw@localhost/my_db")
my_conn=my_conn.connect()
sql="SELECT * FROM student LIMIT 0,10 "
df = pd.read_sql(text(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

Sample CSV file with duplicate rows and missing vlaues for testing

Using Notepad or any other editor save this file by giving .csv file extension. (my_file_name.csv )

ID,Name,Class,Mark,Gender
1,John,Fifth,75,Male
2,Jane,Fourth,85,Female
3,Mike,Third,95,Male
4,Lisa,Second,90,Female
5,Tom,Fifth,70,Male
6,Sara,Third,80,Female
7,Paul,Fourth,,Male
8,Anna,Second,88,Female
9,Kate,First,65,Female
10,Jim,Third,78,Male
11,Ella,Fifth,,Female
12,Jack,Fourth,92,Male
13,Mary,First,60,Female
14,Steve,Fifth,83,Male
15,Mia,Third,77,Female
16,Leo,Fifth,71,Male
17,Chloe,Fourth,86,Female
18,Luke,Third,89,Male
19,Grace,Second,91,Female
20,Ryan,Fifth,74,Male
1,John,Fifth,75,Male
2,Jane,Fourth,85,Female
23,Tom,Fifth,70,Male
24,Anna,Second,88,Female
25,Lisa,Second,,Female
26,Kate,First,65,Female
27,Jim,Third,,Male
28,Ella,Fifth,87,Female
29,Paul,Fourth,82,Male
30,Sara,Third,80,Female
31,Mary,First,60,Female
32,Leo,Fifth,71,Male
33,Mia,Third,77,Female
34,Steve,Fifth,83,Male
35,Grace,Second,91,Female
36,Ryan,Fifth,,Male
37,Luke,Third,89,Male
38,Chloe,Fourth,86,Female
39,Ella,Fifth,87,Female
40,Jack,Fourth,92,Male

Excel to SQlite Database

import pandas as pd
import sqlite3

# Load Excel file
excel_file = 'E:\\excel_sqlite\\student.xlsx'  # Replace with your path and Excel file name
df = pd.read_excel(excel_file)

# Create SQLite connection
conn = sqlite3.connect('E:\\excel_sqlite\\my_db.db')  # Output SQLite database file
cursor = conn.cursor()

# Write DataFrame to SQLite table
table_name = 'student'  # Name of the table in SQLite database
df.to_sql(table_name, conn, if_exists='replace', index=False)

# Confirm and close
print(f"Data from '{excel_file}' -> table '{table_name}' saved to 'PO_data.db'")
conn.close()

SQLite database

Downloading files by using wget at Colab 🔝

We can collect files by using URL. These files will be available during runtime ( session ) only at your Colab platform.

# download my_db.db SQLite Database from plus2net.com
!wget https://www.plus2net.com/python/download/my_db.db
# !wget https://www.plus2net.com/python/download/student.xlsx # Excel file download
# !wget https://www.plus2net.com/python/download/student.csv # CSV file download

💡 Try it yourself — no coding required!

You can explore all the SQL query solutions interactively using the Google Colab Data Agent by simply typing your questions ( List of Queries for Student table is here ) in plain English.

Open Colab Notebook on GitHub

Query Excel, CSV & SQLite with Plain English Using Google Colab Data Agent | No Code SQL Demo

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

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.

Subscribe to our YouTube Channel here

Pandas sample student DataFrame