++++

Data Science

May 2026×Notebook lesson

Notebook converted from Jupyter for blog publishing.

09-Inputs-and-Outputs

Driptanil DattaSoftware Developer

Inputs and Outputs

NOTE: Typically we will just be either reading csv files directly or using pandas-datareader to pull data from the web. Consider this lecture just a quick overview of what is possible with pandas (we won't be working with SQL or Excel files in this course)

Data Input and Output

This notebook is the reference code for getting input and output, pandas can read a variety of file types using its pd.read_ methods. Let's take a look at the most common data types:

import numpy as np
import pandas as pd

Check out the references here!

This is the best online resource for how to read/write to a variety of data sources!

https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html (opens in a new tab)

<table border="1" class="colwidths-given docutils"> <colgroup> <col width="12%" /> <col width="40%" /> <col width="24%" /> <col width="24%" /> </colgroup> <thead valign="bottom"> <tr class="row-odd"><th class="head">Format Type</th> <th class="head">Data Description</th> <th class="head">Reader</th> <th class="head">Writer</th> </tr> </thead> <tbody valign="top"> <tr class="row-even"><td>text</td> <td><a class="reference external" href="https://en.wikipedia.org/wiki/Comma-separated_values">CSV</a></td (opens in a new tab)> <td><a class="reference internal" href="#io-read-csv-table">read_csv</a></td> <td><a class="reference internal" href="#io-store-in-csv">to_csv</a></td> </tr> <tr class="row-odd"><td>text</td> <td><a class="reference external" href="https://www.json.org/">JSON</a></td (opens in a new tab)> <td><a class="reference internal" href="#io-json-reader">read_json</a></td> <td><a class="reference internal" href="#io-json-writer">to_json</a></td> </tr> <tr class="row-even"><td>text</td> <td><a class="reference external" href="https://en.wikipedia.org/wiki/HTML">HTML</a></td (opens in a new tab)> <td><a class="reference internal" href="#io-read-html">read_html</a></td> <td><a class="reference internal" href="#io-html">to_html</a></td> </tr> <tr class="row-odd"><td>text</td> <td>Local clipboard</td> <td><a class="reference internal" href="#io-clipboard">read_clipboard</a></td> <td><a class="reference internal" href="#io-clipboard">to_clipboard</a></td> </tr> <tr class="row-even"><td>binary</td> <td><a class="reference external" href="https://en.wikipedia.org/wiki/Microsoft_Excel">MS (opens in a new tab) Excel</a></td> <td><a class="reference internal" href="#io-excel-reader">read_excel</a></td> <td><a class="reference internal" href="#io-excel-writer">to_excel</a></td> </tr> <tr class="row-odd"><td>binary</td> <td><a class="reference external" href="http://www.opendocumentformat.org">OpenDocument</a></td (opens in a new tab)> <td><a class="reference internal" href="#io-ods">read_excel</a></td> <td> </td> </tr> <tr class="row-even"><td>binary</td> <td><a class="reference external" href="https://support.hdfgroup.org/HDF5/whatishdf5.html">HDF5 (opens in a new tab) Format</a></td> <td><a class="reference internal" href="#io-hdf5">read_hdf</a></td> <td><a class="reference internal" href="#io-hdf5">to_hdf</a></td> </tr> <tr class="row-odd"><td>binary</td> <td><a class="reference external" href="https://github.com/wesm/feather">Feather (opens in a new tab) Format</a></td> <td><a class="reference internal" href="#io-feather">read_feather</a></td> <td><a class="reference internal" href="#io-feather">to_feather</a></td> </tr> <tr class="row-even"><td>binary</td> <td><a class="reference external" href="https://parquet.apache.org/">Parquet (opens in a new tab) Format</a></td> <td><a class="reference internal" href="#io-parquet">read_parquet</a></td> <td><a class="reference internal" href="#io-parquet">to_parquet</a></td> </tr> <tr class="row-odd"><td>binary</td> <td><a class="reference external" href="https://msgpack.org/index.html">Msgpack</a></td (opens in a new tab)> <td><a class="reference internal" href="#io-msgpack">read_msgpack</a></td> <td><a class="reference internal" href="#io-msgpack">to_msgpack</a></td> </tr> <tr class="row-even"><td>binary</td> <td><a class="reference external" href="https://en.wikipedia.org/wiki/Stata">Stata</a></td (opens in a new tab)> <td><a class="reference internal" href="#io-stata-reader">read_stata</a></td> <td><a class="reference internal" href="#io-stata-writer">to_stata</a></td> </tr> <tr class="row-odd"><td>binary</td> <td><a class="reference external" href="https://en.wikipedia.org/wiki/SAS_(software)">SAS</a></td (opens in a new tab)> <td><a class="reference internal" href="#io-sas-reader">read_sas</a></td> <td> </td> </tr> <tr class="row-even"><td>binary</td> <td><a class="reference external" href="https://docs.python.org/3/library/pickle.html">Python (opens in a new tab) Pickle Format</a></td> <td><a class="reference internal" href="#io-pickle">read_pickle</a></td> <td><a class="reference internal" href="#io-pickle">to_pickle</a></td> </tr> <tr class="row-odd"><td>SQL</td> <td><a class="reference external" href="https://en.wikipedia.org/wiki/SQL">SQL</a></td (opens in a new tab)> <td><a class="reference internal" href="#io-sql">read_sql</a></td> <td><a class="reference internal" href="#io-sql">to_sql</a></td> </tr> <tr class="row-even"><td>SQL</td> <td><a class="reference external" href="https://en.wikipedia.org/wiki/BigQuery">Google (opens in a new tab) Big Query</a></td> <td><a class="reference internal" href="#io-bigquery">read_gbq</a></td> <td><a class="reference internal" href="#io-bigquery">to_gbq</a></td> </tr> </tbody> </table>

Reading in a CSV

Comma Separated Values files are text files that use commas as field delimeters.
Unless you're running the virtual environment included with the course, you may need to install xlrd and openpyxl.
In your terminal/command prompt run:

conda install xlrd conda install openpyxl

Then restart Jupyter Notebook. (or use pip install if you aren't using the Anaconda Distribution)

Understanding File Paths

You have two options when reading a file with pandas:

If your .py file or .ipynb notebook is located in the exact same folder location as the .csv file you want to read, simply pass in the file name as a string, for example:

df = pd.read_csv('some_file.csv')
Pass in the entire file path if you are located in a different directory. The file path must be 100% correct in order for this to work. For example:

df = pd.read_csv("C:\Users\myself\files\some_file.csv")

Print your current directory file path with pwd

pwd

RESULT

'C:\\Users\\Marcial\\Pierian-Data-Courses\\Machine-Learning-MasterClass\\03-Pandas'

List the files in your current directory with ls

ls

STDOUT

 Volume in drive C has no label.
 Volume Serial Number is 3652-BD2F

 Directory of C:\Users\Marcial\Pierian-Data-Courses\Machine-Learning-MasterClass\03-Pandas

NOTE! Common confusion point! Take note that all read input methods are called directly from pandas with pd.read_ , all output methods are called directly off the dataframe with df.to_

CSV Input

df = pd.read_csv('example.csv')

df

HTML

a
b
c
d
0

df = pd.read_csv('example.csv',index_col=0)

df

HTML

b
c
d
a
0

df = pd.read_csv('example.csv')

df

HTML

a
b
c
d
0

CSV Output

Set index=False if you do not want to save the index , otherwise it will add a new column to the .csv file that includes your index and call it "Unnamed: 0" if your index did not have a name. If you do want to save your index, simply set it to True (the default value).

df.to_csv('new_file.csv',index=False)

HTML

Pandas can read table tabs off of HTML. This only works if your firewall isn't blocking pandas from accessing the internet!

Unless you're running the virtual environment included with the course, you may need to install lxml, htmllib5, and BeautifulSoup4.
In your terminal/command prompt run:

conda install lxml

pip install lxml

Then restart Jupyter Notebook (you may need to restart your computer). (or use pip install if you aren't using the Anaconda Distribution)

read_html

HTML Input

Pandas read_html function will read tables off of a webpage and return a list of DataFrame objects. NOTE: This only works with well defined <table> objects in the html on the page, this can not magically read in tables that are images on a page.

tables = pd.read_html('https://en.wikipedia.org/wiki/World_population')

len(tables) #tables

RESULT

Not Useful Tables

Pandas found 26 tables on that page. Some are not useful:

tables[0]

HTML

0
1
0
NaN
An editor has expressed concern that this arti...

Tables that need formatting

Some will be misaligned, meaning you need to do extra work to fix the columns and rows:

tables[1]

HTML

World population (millions, UN estimates)[14]
#
Top ten most populous countries
2000
2015

world_pop = tables[1]

world_pop.columns

RESULT

MultiIndex([('World population (millions, UN estimates)[14]', ...),
            ('World population (millions, UN estimates)[14]', ...),
            ('World population (millions, UN estimates)[14]', ...),
            ('World population (millions, UN estimates)[14]', ...),
            ('World population (millions, UN estimates)[14]', ...)],

world_pop = world_pop['World population (millions, UN estimates)[14]'].drop('#',axis=1)

world_pop.columns

RESULT

Index(['Top ten most populous countries', '2000', '2015', '2030[A]'], dtype='object')

world_pop.columns = ['Countries', '2000', '2015', '2030 Est.']
world_pop = world_pop.drop(11,axis=0)

world_pop

HTML

Countries
2000
2015
2030 Est.
0

Tables that are intact

tables[6]

HTML

Rank
Country
Population
Area (km2)
Density (Pop. per km2)

Write to html Output

If you are working on a website and want to quickly output the .html file, you can use to_html

df.to_html('simple.html',index=False)

read_html is not perfect, but its quite powerful for such a simple method call!

Excel Files

Pandas can read in basic excel files (it will get errors if there are macros or extensive formulas relying on outside excel files), in general, pandas can only grab the raw information from an .excel file.

NOTE: Requires the openpyxl and xlrd library! Its provided for you in our environment, or simply install with:

pip install openpyxl pip install xlrd

Heavy excel users may want to check out this website: https://www.python-excel.org/ (opens in a new tab)

You can think of an excel file as a Workbook containin sheets, which for pandas means each sheet can be a DataFrame.

Excel file input with read_excel()

df = pd.read_excel('my_excel_file.xlsx',sheet_name='First_Sheet')

df

HTML

a
b
c
d
0

What if you don't know the sheet name? Or want to run a for loop for certain sheet names? Or want every sheet?

Several ways to do this: https://stackoverflow.com/questions/17977540/pandas-looking-up-the-list-of-sheets-in-an-excel-file (opens in a new tab)

# Returns a list of sheet_names
pd.ExcelFile('my_excel_file.xlsx').sheet_names

RESULT

['First_Sheet']

Grab all sheets

excel_sheets = pd.read_excel('my_excel_file.xlsx',sheet_name=None)

type(excel_sheets)

RESULT

dict

excel_sheets.keys()

RESULT

dict_keys(['First_Sheet'])

excel_sheets['First_Sheet']

HTML

a
b
c
d
0

Write to Excel File

df.to_excel('example.xlsx',sheet_name='First_Sheet',index=False)

SQL Connections

NOTE: Highly recommend you explore specific libraries for your specific SQL Engine. Simple search for your database+python in Google and the top results should hopefully include an API.

Let's review pandas capabilities by using SQLite, which comes built in with Python.

Example SQL Database (temporary in your RAM)

You will need to install sqlalchemy with:

pip install sqlalchemy

to follow along. To understand how to make a connection to your own database, make sure to review: https://docs.sqlalchemy.org/en/13/core/connections.html (opens in a new tab)

from sqlalchemy import create_engine

temp_db = create_engine('sqlite:///:memory:')

Write to Database

tables[6]

HTML

Rank
Country
Population
Area (km2)
Density (Pop. per km2)

pop = tables[6]

pop.to_sql(name='populations',con=temp_db)

Read from SQL Database

# Read in an entire table
pd.read_sql(sql='populations',con=temp_db)

HTML

index
Rank
Country
Population
Area (km2)

# Read in with a SQL Query
pd.read_sql_query(sql="SELECT Country FROM populations",con=temp_db)

HTML

Country
0
Singapore
1
Bangladesh

It is difficult to generalize pandas and SQL, due to a wide array of issues, including permissions,security, online access, varying SQL engines, etc... Use these ideas as a starting off point, and you will most likely need to do your own research for your own situation.

08 Time Methods 10 Pivot Tables