Creating DataFrames in Python

A DataFrame is a two-dimensional data structure in pandas, similar to a table in a database or a worksheet in Excel. It consists of rows and columns.

This notebook demonstrates various ways to create a pandas DataFrame.

Example 1: Creating a pandas DataFrame from a dictionary

Here, the dictionary (whose values are lists) contains the actual data that will become the DataFrame.

1. Import pandas

   • Imports the pandas library and gives it the shorter name pd.

2. Create the dictionary

   • Each key becomes a DataFrame column, and its associated list becomes the values in that column.

     • 'name' → name column
     • 'level' → level column
     • 'sas' → sas column
     • 'r' → r column
     • 'python' → python column

3. Create the DataFrame

   • pd.DataFrame() converts the dictionary into a DataFrame and stores it in df.

4. Display the DataFrame

import pandas as pd
details = {
    'name' : ['Dexiu', 'Doug', 'Fred', 'Yichi'],
    'level' : ['Graduate', 'Graduate', 'Undergraduate', 'Undergraduate'],
    'sas': ['yes', 'no', 'no', 'no'],
    'r': ['yes', 'yes', 'no', 'yes'],
    'python': ['yes', 'no', 'no', 'yes']
}
df = pd.DataFrame(details)
print(df)

    name          level  sas    r python
0  Dexiu       Graduate  yes  yes    yes
1   Doug       Graduate   no  yes     no
2   Fred  Undergraduate   no   no     no
3  Yichi  Undergraduate   no  yes    yes

The numbers 0, 1, 2, and 3 on the left are the DataFrame index.

A useful way to remember the structure is:

Dictionary keys → column names
Dictionary list values → column values
List positions → DataFrame rows

Example 2: Creating a DataFrame and applying map()

Here, the DataFrame is created first with coded values. A second dictionary is then used to translate those values. The dictionary serves as a mapping, not as the source of the DataFrame. The .map() method is used to replace numeric codes with meaningful labels.

1. Import pandas
2. Create the DataFrame
3. Create a dictionary for value labels
4. Use .map() to replace the values

.map() is particularly useful when you have coded categorical data, such as 1 = Male and 2 = Female, and want to replace the codes with descriptive labels.

import pandas as pd

# Create a DataFrame
df = pd.DataFrame({'gender': [1, 2]})

# Print the original DataFrame
print(df)

# Create a dictionary of value labels
dic = {1: 'Male', 2: 'Female'}

# Use .map() to transform the original values
df['gender'] = df['gender'].map(dic)

# Print the transformed DataFrame
print(df)

   gender
0       1
1       2
   gender
0    Male
1  Female

Example 3: Creating a DataFrame by reading a CSV file

The code reads a CSV file into a pandas DataFrame and displays it.

• import pandas as pd — imports the pandas library.
• pd.read_csv() — reads the CSV file and creates a DataFrame.
• r'c:\Data\class.csv' — specifies the Windows file path as a raw string.
• df — stores the DataFrame.
• print(df) — displays the DataFrame.

import os
os.getcwd()

import pandas as pd

df = pd.read_csv(
    r'c:\Data\class.csv'
)

print(df)

       name sex  age  height  weight
0    Alfred   M   14    69.0   112.5
1     Alice   F   13    56.5    84.0
2   Barbara   F   13    65.3    98.0
3     Carol   F   14    62.8   102.5
4     Henry   M   14    63.5   102.5
5     James   M   12    57.3    83.0
6      Jane   F   12    59.8    84.5
7     Janet   F   15    62.5   112.5
8   Jeffrey   M   13    62.5    84.0
9      John   M   12    59.0    99.5
10    Joyce   F   11    51.3    50.5
11     Judy   F   14    64.3    90.0
12   Louise   F   12    56.3    77.0
13     Mary   F   15    66.5   112.0
14   Philip   M   16    72.0   150.0
15   Robert   M   12    64.8   128.0
16   Ronald   M   15    67.0   133.0
17   Thomas   M   11    57.5    85.0
18  William   M   15    66.5   112.0

Example 4: Extracting the HTML table into a DataFrame

This code downloads the Simple Wikipedia webpage, extracts all its HTML tables into a list of pandas DataFrames, and displays the first 4 columns and first 4 rows of the first table as a DataFrame.¶

import pandas as pd
import requests
from io import StringIO

url = "https://simple.wikipedia.org/wiki/List_of_U.S._states"

headers = {"User-Agent": "Mozilla/5.0"}

html = requests.get(url, headers=headers).text

mylist = pd.read_html(StringIO(html))

print(f"Number of tables found: {len(mylist)}")

df = mylist[0]

df.iloc[:4, :4]

Number of tables found: 1

	Flag, name and postal abbreviation [1]		Cities
	Flag, name and postal abbreviation [1]	Flag, name and postal abbreviation [1].1	Capital	Largest (by population)[5]
0	Alabama	AL	Montgomery	Huntsville
1	Alaska	AK	Juneau	Anchorage
2	Arizona	AZ	Phoenix	Phoenix
3	Arkansas	AR	Little Rock	Little Rock

For a simple pandas exercise, you can use the Iris dataset from the UCI Machine Learning Repository and you can read it directly with pandas:

import pandas as pd
url = "https://archive.ics.uci.edu/ml/machine-learning-databases/iris/iris.data"
df = pd.read_csv(url)
print(df)

     5.1  3.5  1.4  0.2     Iris-setosa
0    4.9  3.0  1.4  0.2     Iris-setosa
1    4.7  3.2  1.3  0.2     Iris-setosa
2    4.6  3.1  1.5  0.2     Iris-setosa
3    5.0  3.6  1.4  0.2     Iris-setosa
4    5.4  3.9  1.7  0.4     Iris-setosa
..   ...  ...  ...  ...             ...
144  6.7  3.0  5.2  2.3  Iris-virginica
145  6.3  2.5  5.0  1.9  Iris-virginica
146  6.5  3.0  5.2  2.0  Iris-virginica
147  6.2  3.4  5.4  2.3  Iris-virginica
148  5.9  3.0  5.1  1.8  Iris-virginica

[149 rows x 5 columns]

The code below downloads a Wikipedia webpage, extracts its HTML tables with pandas, and displays the first 4 columns and first 4 rows of the first table as a DataFrame.

import pandas as pd
import requests
from io import StringIO

url = "https://en.wikipedia.org/wiki/List_of_U.S._states_and_territories_by_population"


headers = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) "
                  "AppleWebKit/537.36 (KHTML, like Gecko) "
                  "Chrome/151.0.0.0 Safari/537.36"
}

response = requests.get(url, headers=headers)

print("HTTP status code:", response.status_code)

mylist = pd.read_html(StringIO(response.text))

print(f"Number of tables found: {len(mylist)}")

df = mylist[0]

df.iloc[:4, :4]

HTTP status code: 200
Number of tables found: 6

	Unnamed: 0_level_0	State or territory	Census population[8][9][a]
	Unnamed: 0_level_1	State or territory	July 1, 2025 (est.)	April 1, 2020
0	1	California	39355309.0	39538223
1	2	Texas	31709821.0	29145505
2	3	Florida	23462518.0	21538187
3	4	New York	20002427.0	20201249

The next cell demonstrates how to download an Excel file from a web server and load its contents into a pandas DataFrame.

• requests.get() downloads the Excel file.
• response.content contains the downloaded file as binary data.
• BytesIO() makes the binary data behave like a file.
• pd.read_excel() reads the Excel file into a pandas DataFrame.
• df.shape gives you the dimensions of the DataFrame, that is, the row and column counts, with commas
• displays the first 6 columns and first 5 rows of the first table as a DataFrame.

import pandas as pd
import requests
from io import BytesIO
url = "https://www.dhs.gov/sites/default/files/publications/claims-2014.xls"
headers = {"User-Agent": "Mozilla/5.0"}
response = requests.get(url, headers=headers)
df = pd.read_excel(BytesIO(response.content))

print("Dimensions [rows, columns]:", f"{df.shape[0]:,}", f"{df.shape[1]:,}")
df.iloc[:5, :6]

Dimensions [rows, columns]: 8,855 11

	Claim Number	Date Received	Incident Date	Airport Code	Airport Name	Airline Name
0	2013081805991	2014-01-13	2012-12-21 00:00:00	HPN	Westchester County, White Plains	USAir
1	2014080215586	2014-07-17	2014-06-30 18:38:00	MCO	Orlando International Airport	Delta Air Lines
2	2014010710583	2014-01-07	2013-12-27 22:00:00	SJU	Luis Munoz Marin International	Jet Blue
3	2014010910683	2014-01-07	2014-01-02 00:00:00	IAD	Washington Dulles International	UAL
4	2014011310783	2014-01-09	2014-01-07 00:00:00	SAT	San Antonio International	Southwest Airlines

Example 5: Converting a SAS Dataset to a panda DataFrame

This code demonstrates how to connect Python to SAS, access a SAS dataset, convert it to a pandas DataFrame, and generate descriptive statistics.

import pandas as pd
import saspy

sas = saspy.SASsession(cfgname='winlocal')

sas_class = sas.sasdata('class', libref='sashelp')

pc2 = sas.sasdata2dataframe('class', libref='sashelp')

pc2.describe()

SAS Connection established. Subprocess id is 48700

	Age	Height	Weight
count	19.000000	19.000000	19.000000
mean	13.315789	62.336842	100.026316
std	1.492672	5.127075	22.773933
min	11.000000	51.300000	50.500000
25%	12.000000	58.250000	84.250000
50%	13.000000	62.800000	99.500000
75%	14.500000	65.900000	112.250000
max	16.000000	72.000000	150.000000