Prev Next

Writing to Files

Exporting data to external files is a critical step in data workflows, enabling the storage and sharing of processed datasets. Pandas makes it easy to write DataFrames to various file formats, such as CSV, Excel, JSON, and SQL. This tutorial demonstrates how to export data to these formats effectively.

Writing to CSV

The to_csv() method is used to write a DataFrame to a CSV file. By default, it includes the index column, but this can be disabled using the index parameter. Here’s an example: 

import pandas as pd

# Create a sample DataFrame
data = {
    "Name": ["Karthick", "Durai", "Praveen"],
    "Age": [25, 30, 22],
    "City": ["Chennai", "Coimbatore", "Madurai"]
}

df = pd.DataFrame(data)

# Write DataFrame to CSV
df.to_csv("output.csv", index=False)
print("Data written to output.csv")

Output: File saved as output.csv in the current directory.

Explanation: The to_csv() method writes the DataFrame to a CSV file named output.csv. Setting index=False excludes the index column from the file.

Writing to Excel

The to_excel() method writes a DataFrame to an Excel file. Ensure the openpyxl library is installed for this functionality. Here’s an example: 

# Write DataFrame to Excel
df.to_excel("output.xlsx", sheet_name="Sheet1", index=False)
print("Data written to output.xlsx")

Output: File saved as output.xlsx with the sheet name Sheet1.

Explanation: The to_excel() method writes the DataFrame to an Excel file. The sheet_name parameter specifies the name of the worksheet, and index=False excludes the index column.

Writing to JSON

The to_json() method exports a DataFrame to a JSON file. You can specify the JSON format using the orient parameter. Here’s an example: 

# Write DataFrame to JSON
df.to_json("output.json", orient="records")
print("Data written to output.json")

Output: File saved as output.json with the records orientation.

Explanation: The to_json() method writes the DataFrame to a JSON file in the specified orientation. In this example, the records orientation exports data as a list of dictionaries.

Writing to SQL

The to_sql() method writes a DataFrame to a SQL database table. Ensure a database connection and an appropriate library (e.g., sqlite3) are available. Here’s an example: 

import sqlite3

# Create a database connection
conn = sqlite3.connect("example.db")

# Write DataFrame to SQL table
df.to_sql("people", conn, if_exists="replace", index=False)
print("Data written to SQL table 'people'")

Output: Data inserted into the SQL table people.

Explanation: The to_sql() method writes the DataFrame to a SQL table. The if_exists="replace" parameter ensures the table is replaced if it already exists, and index=False excludes the index column.

Key Takeaways

  • CSV Export: Use to_csv() to save data in a widely used format.
  • Excel Export: Use to_excel() for exporting data to Excel files.
  • JSON Export: Use to_json() for flexible and structured data storage.
  • SQL Export: Use to_sql() to insert data into a SQL database.
  • Flexible Parameters: Customize file formats and structures using various method parameters.
Prev Next