Skip to main content

Importing and Exporting Data in pandas

Data import and export are fundamental tasks in any data analysis workflow. Pandas makes it easy to read data from various file formats and export DataFrames to different formats. In this article, we'll explore how to work with common file types, such as CSV, Excel, and more.

1. Reading Data from CSV Files​

CSV (Comma-Separated Values) files are one of the most common data formats. Pandas provides the pd.read_csv() function to load data from CSV files into DataFrames.

1.1 Basic CSV Import​

To import a CSV file, simply pass the file path to pd.read_csv().

from io import StringIO

import pandas as pd

# Reading CSV text (a file path works the same way)
csv_text = StringIO("Name,Score\nAda,10\nLin,12\n")
df = pd.read_csv(csv_text)
print("DataFrame from CSV:\n", df.head())

1.2 Handling Delimiters​

If your CSV file uses a different delimiter (e.g., semicolon), you can specify it using the sep parameter.

# Reading CSV text with a semicolon delimiter
semicolon_text = StringIO("Name;Score\nAda;10\nLin;12\n")
df = pd.read_csv(semicolon_text, sep=';')
print("DataFrame with semicolon delimiter:\n", df.head())

1.3 Specifying Column Names​

If your CSV file does not have a header, or if you want to assign your own column names, use the names parameter.

# Reading CSV text without headers
no_header_text = StringIO("1,2,3\n4,5,6\n")
df = pd.read_csv(no_header_text, names=['Column1', 'Column2', 'Column3'])
print("DataFrame with specified column names:\n", df.head())

1.4 Handling Missing Data​

You can handle missing data during import by specifying values to be treated as NaN using the na_values parameter.

# Reading CSV text and treating specific values as missing
missing_text = StringIO("Name,Score\nAda,10\nLin,Missing\n")
df = pd.read_csv(missing_text, na_values=['NA', 'Missing'])
print("DataFrame with missing data handled:\n", df.head())

2. Reading Data from Excel Files​

Excel files are another common format for data storage. Pandas provides the pd.read_excel() function to read data from Excel files.

2.1 Basic Excel Import​

To import an Excel file, pass the file path to pd.read_excel().

# Reading an Excel file
df = pd.read_excel('data.xlsx')
print("DataFrame from Excel:\n", df.head())

2.2 Reading Specific Sheets​

If your Excel file contains multiple sheets, you can specify which sheet to read using the sheet_name parameter.

# Reading a specific sheet from an Excel file
df = pd.read_excel('data.xlsx', sheet_name='Sheet2')
print("DataFrame from specific sheet:\n", df.head())

2.3 Reading Multiple Sheets​

You can read multiple sheets at once by passing a list of sheet names. This will return a dictionary of DataFrames.

# Reading multiple sheets from an Excel file
dfs = pd.read_excel('data.xlsx', sheet_name=['Sheet1', 'Sheet2'])
print("DataFrames from multiple sheets:\n", dfs['Sheet1'].head(), "\n", dfs['Sheet2'].head())

3. Writing Data to Files​

Pandas allows you to export DataFrames to various formats, including CSV and Excel.

3.1 Writing Data to CSV​

You can export a DataFrame to a CSV file using the to_csv() method.

# Writing a DataFrame to an in-memory CSV buffer
csv_output = StringIO()
df.to_csv(csv_output, index=False)
assert csv_output.getvalue().startswith("Name,Score\n")
print("DataFrame exported to CSV text")

3.2 Writing Data to Excel​

Similarly, you can export a DataFrame to an Excel file using the to_excel() method.

# Writing a DataFrame to an Excel file
df.to_excel('output.xlsx', index=False)
print("DataFrame exported to output.xlsx")

3.3 Handling Missing Data During Export​

You can handle missing data during export by specifying a placeholder value using the na_rep parameter.

# Writing a DataFrame to CSV text with a placeholder for missing values
missing_output = StringIO()
df.to_csv(missing_output, na_rep='Unknown', index=False)
assert "Unknown" in missing_output.getvalue()
print("DataFrame with missing data handled during export")

4. Working with Other File Formats​

Pandas also supports reading and writing data in other formats such as JSON, HTML, and SQL.

4.1 JSON Files​

You can read JSON files using pd.read_json() and write to JSON using to_json().

# Reading JSON text
json_text = StringIO('[{"Name":"Ada","Score":10},{"Name":"Lin","Score":12}]')
df_json = pd.read_json(json_text)
print("DataFrame from JSON:\n", df_json.head())

# Writing a DataFrame to JSON text
json_output = df_json.to_json(orient='records')
assert '"Name":"Ada"' in json_output
print("DataFrame exported to JSON text")

4.2 HTML Tables​

Pandas can read HTML tables directly from a webpage using pd.read_html().

# Reading an inline HTML table; this requires an optional HTML parser
html = StringIO('<table><tr><th>Value</th></tr><tr><td>1</td></tr></table>')
dfs_html = pd.read_html(html)
print("DataFrames from HTML:\n", dfs_html[0].head())

4.3 SQL Databases​

Pandas integrates with SQL databases, allowing you to read from and write to databases using read_sql() and to_sql().

import sqlite3

# Creating an isolated in-memory SQLite database
conn = sqlite3.connect(':memory:')

source = pd.DataFrame({'Name': ['Ada', 'Lin'], 'Score': [10, 12]})
source.to_sql('scores', conn, index=False)

# Reading data from a SQL database
df_sql = pd.read_sql_query('SELECT * FROM scores', conn)
print("DataFrame from SQL database:\n", df_sql.head())

# Writing a DataFrame to a SQL database
df_sql.to_sql('scores_copy', conn, if_exists='replace', index=False)
print("DataFrame exported to SQL database")
conn.close()

Version Notes and Verified Behavior​

Text formats do not preserve every pandas dtype, index, or missing-value distinction, so define and validate a schema after import. Excel and HTML readers require optional parser engines, and the corresponding examples above are marked manual because those packages and external workbooks are outside this batch's runtime. Use parameterized SQL supplied by the database driver for user input, and never load an untrusted pickle file because deserialization can execute arbitrary code.

round_trip_buffer = StringIO()
expected = pd.DataFrame({'id': [1, 2], 'active': [True, False]})
expected.to_csv(round_trip_buffer, index=False)
round_trip_buffer.seek(0)
actual = pd.read_csv(round_trip_buffer, dtype={'id': 'int64', 'active': 'bool'})

pd.testing.assert_frame_equal(actual, expected)

Official references: I/O tools, read_csv, and read_sql_query.

5. Conclusion​

Importing and exporting data efficiently is crucial for data analysis workflows. Pandas provides versatile functions for handling various file formats, making it easier to integrate data from different sources. By mastering these techniques, you can streamline your data import/export processes and ensure that your data is always ready for analysis. In the next article, we'll dive into data aggregation and grouping techniques in pandas.