Python Pandas - Reading Tabular Data

The Pandas library in Python provides a wide variety of functions to read tabular data from different sources, including CSV, Excel, SQL databases, JSON files, and more. Depending on your data format, you can choose an appropriate function (read_table(), read_csv(), read_excel(), etc.) to load the data into a PandasDataFrame for further analysis.

In this tutorial, we will learn about commonly used Pandas methods for reading tabular data, including examples for each format. Whether you're working with CSV files, Excel spreadsheets, or other delimited text files, Pandas makes it easy to load and analyze your tabular data in Pandas.

Common Methods in Pandas for Reading Tabular Data

Pandas provides several functions to load tabular data into Pandas objects. You can choose an appropriate method depending on the file format. The most commonly used methods are −

• read_table(): Used for reading tab-separated values (TSV) or other delimited text files.

• read_csv(): Used for reading CSV (comma-separated values) files.

• read_excel(): Used for reading Excel files (.xls, .xlsx).

• read_sql(): Used for reading data from a SQL database.

• read_json(): Used for reading JSON files.

• read_html(): Used for reading HTML tables.

Reading Tab-Delimited Files

Theread_table() function is used to read tab-separated values (TSV) or other delimited files. This method assumes that the data provided in the text file is separated by tabs (\t) by default.

Example

This example demonstrates reading tabular data from a tab-delimited text file to Pandas DataFrame using theread_table() method. In this example, we explicitly specified a column name to set it as the DataFrame index using theindex_col parameter.

import pandas as pd# Import StringIO to load a file-like objectfrom io import StringIO# Create a tab-delimited datadata = """Sr.no\tName\tGender\tAge1\tBraund\tmale\t222\tCumings\tfemale\t383\tHeikkinen\tfemale\t264\tFutrelle\tfemale\t35"""# Use StringIO to convert the string data into a file-like objectobj = StringIO(data)# Reading tab-delimited datadf = pd.read_table(obj, index_col="Sr.no")print("DataFrame from Tab-Delimited File:")print(df)

Following is an output of the above code −

DataFrame from Tab-Delimited File:

Reading CSV Files

The most common format for tabular data is CSV (comma-separated values). Pandas provides theread_csv() method to read tabular data from CSV files into a DataFrame. This method provides various customization options through its parameters, likeskiprows to skip rows,dtype to specify column data types, andna_values to treat specific values as NaN, and more.

Example

This example shows loading CSV data into Pandas DataFrame using theread_csv() method.

import pandas as pdurl ="https://raw.githubusercontent.com/Opensourcefordatascience/Data-sets/master/blood_pressure.csv"# Read a CSV file into a DataFramedf = pd.read_csv(url)# Display the first few rowsprint("Loaded CSV Tabular Data:")print(df.head())

When we run the above program, it produces the following result −

Loaded CSV Tabular Data:

Reading Excel Files

Excel is one of the most popular formats for storing tabular data. Pandas provides theread_excel() function to load data from Excel files (.xls, .xlsx).

Example

This example shows how to read tabular data from an excel file to Pandas DataFrame using theread_excel() method.

import pandas as pd# Read an Excel file df = pd.read_excel('data.xlsx')# Print the DataFrameprint("DataFrame from Excel File:")print(df)

While executing the above code we get the following output −

DataFrame from Excel File:

Reading Data from SQL Databases

Pandas provides theread_sql() method to load SQL table, execute SQL queries and load the result into a DataFrame.

Example

This example shows creating an in-memory SQL table from a DataFrame and loading back to new DataFrame using theto_sql() andread_sql() methods respectively.

import pandas as pdfrom sqlite3 import connect# Create a database connectionconn = connect(':memory:')# Create a sample DataFramedf = pd.DataFrame({"Col_1": list("abc"),"Col_2": list(range(1, 4)),"Col_3": pd.date_range("20240101", periods=3)})# Save the DataFrame in a SQL table with 'test_data' namedf.to_sql(name='test_data', con=conn)# Read SQL table into a DataFrameresult = pd.read_sql('SELECT Col_1, Col_2, Col_3 FROM test_data', conn)# Display the retrieved dataprint("DataFrame from SQL Table:")print(result.head())

When we run the above program, it produces the following result −

DataFrame from SQL Table:

Reading JSON Files

JSON (JavaScript Object Notation) is a popular data interchange format. You can useread_json() to read data from JSON files into a DataFrame.

Example

This example shows reading JSON file data into Pandas DataFrame using theread_json() method.

import pandas as pd# Create a sample DataFramedf = pd.DataFrame({"Col_1": list("abc"),"Col_2": list(range(1, 4)),"Col_3": pd.date_range("20240101", periods=3)})# Write DataFrame to a JSON filedf.to_json("output_json_file.json")# Read JSON data into a DataFrameresult = pd.read_json("output_json_file.json")# Display the retrieved dataprint('DataFrame from JSON file:')print(result.head())

When we run the above program, it produces the following result −

DataFrame from JSON file:

Reading HTML Tables

If you need to read tables directly from an HTML file or webpage,read_html() can scrape and parse HTML tables into a Pandas DataFrames.

Example

This example demonstrates reading tabular data from a HTML file using theread_html() method.

import pandas as pd# Create a HTML stringhtml_str = """<table>   <tr><th>Col_1</th><th>Col_2</th><th>Col_3</th></tr>   <tr><td>a</td><td>b</td><td>c</td></tr>   <tr><td>x</td><td>y</td><td>z</td></tr></table>"""# Save to a temporary file and readwith open("temp.html", "w") as f:    f.write(html_str)df = pd.read_html("temp.html")[0]# Display the outputprint("DataFrame from HTML File:")print(df)

Following is an output of the above code −

DataFrame from HTML File: