Reading and Writing data with Pandas

Pandas provides a variety of functions to read data from different file formats and to write data to those formats. Here’s a comprehensive overview of how to handle data input and output with Pandas.

Reading Data

1. Reading from CSV Files

import pandas as pd
# Reading a CSV file into a DataFrame
df = pd.read_csv('file.csv')
# Reading a CSV file with specific settings
df = pd.read_csv('file.csv', delimiter=',', header=0, names=['A', 'B', 'C'], usecols=['A', 'B'])

2. Reading from Excel Files

# Reading an Excel file into a DataFrame
df = pd.read_excel('file.xlsx', sheet_name='Sheet1')
# Reading multiple sheets
dfs = pd.read_excel('file.xlsx', sheet_name=['Sheet1', 'Sheet2'])

3. Reading from SQL Databases

import sqlite3
# Connecting to a SQLite database
conn = sqlite3.connect('database.db')
# Reading a SQL query into a DataFrame
df = pd.read_sql_query('SELECT * FROM table_name', conn)

4. Reading from JSON Files

# Reading a JSON file into a DataFrame
df = pd.read_json('file.json')
# Reading from a JSON string
data = '{"A": [1, 2, 3], "B": [4, 5, 6]}'
df = pd.read_json(data)

5. Reading from HTML Files

# Reading tables from an HTML file
dfs = pd.read_html('file.html')
# Reading from a URL
dfs = pd.read_html('http://example.com/file.html')

Writing Data

1. Writing to CSV Files

# Writing a DataFrame to a CSV file
df.to_csv('file.csv')
# Writing with specific settings
df.to_csv('file.csv', sep=',', index=False, columns=['A', 'B'])

2. Writing to Excel Files

# Writing a DataFrame to an Excel file
df.to_excel('file.xlsx', sheet_name='Sheet1')
# Writing multiple DataFrames to multiple sheets
with pd.ExcelWriter('file.xlsx') as writer:
   df1.to_excel(writer, sheet_name='Sheet1')
   df2.to_excel(writer, sheet_name='Sheet2')

3. Writing to SQL Databases

# Writing a DataFrame to a SQL table
df.to_sql('table_name', conn, if_exists='replace', index=False)

4. Writing to JSON Files

# Writing a DataFrame to a JSON file
df.to_json('file.json')
# Writing to a JSON string
json_data = df.to_json()

5. Writing to HTML Files

# Writing a DataFrame to an HTML file
df.to_html('file.html')