How to Read and Write Data with Pandas: CSV, TSV, Excel, JSON, and SQL

Introduction

In this article we walk through the most common ways to import and export data using pandas. By the end you will be able to move data between pandas DataFrames and CSV, TSV, Excel, JSON files, as well as relational databases such as PostgreSQL.

1. Working with CSV Files

• Reading – pd.read_csv('data/file.csv', index_col='respondent') loads a CSV located in the same folder as the notebook. Use an absolute path if the file lives elsewhere.
• Writing – After filtering (e.g., india_df = df[df['country'] == 'India']) export with india_df.to_csv('data/modified.csv'). The resulting file contains the header row followed by the filtered rows.

2. Tab‑Delimited (TSV) Files

• TSV files are identical to CSV but use tabs as separators.
• Read – pd.read_csv('data/file.tsv', sep='\t').
• Write – df.to_csv('data/file.tsv', sep='\t'). Change the extension to .tsv for clarity.

3. Excel Files

• Install the required libraries: bash pip install xlwt openpyxl pandas
• Writing – df.to_excel('data/modified.xlsx', index=False) creates an .xlsx workbook. You can also write to the older .xls format with xlwt.
• Reading – pd.read_excel('data/modified.xlsx', index_col='respondent') restores the DataFrame. Use the sheet_name argument to target a specific worksheet.
• Excel also supports writing to multiple sheets, custom start rows/columns, etc. Refer to the pandas documentation for the full list of parameters.

4. JSON Files

• Default export – df.to_json('data/file.json') creates a dictionary‑style JSON where each column is a key.
• Record‑oriented export – df.to_json('data/records.json', orient='records', lines=True) writes one JSON object per line, which is easier to read line‑by‑line.
• Reading – Match the orientation used when writing: python df = pd.read_json('data/records.json', orient='records', lines=True) Adjust orient and lines as needed for your specific file.

5. Relational Databases (SQL)

• Install the ORM and driver: bash pip install sqlalchemy psycopg2-binary
• Create an engine – python from sqlalchemy import create_engine engine = create_engine('postgresql://db_user:db_pass@localhost:5432/sample_db')
• Write to a table – df.to_sql('sample_table', con=engine, if_exists='replace', index=False) creates the table if it does not exist. Use if_exists='append' to add rows or if_exists='fail' to raise an error when the table already exists.
• Read a whole table – pd.read_sql('sample_table', con=engine, index_col='respondent').
• Read with a custom query – python query = "SELECT * FROM sample_table WHERE country = 'India'" df = pd.read_sql_query(query, con=engine, index_col='respondent') This is useful for large databases where you only need a subset of rows.

6. Loading Data Directly from URLs

Pandas can ingest remote files without downloading them first. Example for JSON:

url = 'https://raw.githubusercontent.com/user/repo/master/posts.json'
posts_df = pd.read_json(url)

Replace read_json with read_csv, read_excel, etc., depending on the file type.

7. Sponsor Mention

The tutorial is sponsored by Brilliant.org, an interactive learning platform that offers guided lessons on data science, statistics, and Python programming.

Conclusion

You now have a toolbox for moving data in and out of pandas across the most common formats—CSV/TSV, Excel, JSON, and SQL databases—as well as remote URLs. Mastering these I/O operations will let you focus on analysis rather than data‑format headaches.

With pandas you can seamlessly read from and write to CSV, TSV, Excel, JSON, and SQL databases, turning data‑format challenges into a routine part of your workflow.