Pandas Reading Files

Real data usually lives in files, and Pandas can read them into a DataFrame with one line.

A Sample CSV File

A CSV (comma-separated values) file is plain text. Each line is a row, and commas separate the columns. Save the text below as orders.csv in the same folder as your Python file:

Example

text

order_id,city,product,qty,price
101,Karachi,Mobile Cover,2,450
102,Lahore,Charger,1,1200
103,Karachi,Earbuds,1,3500
104,Islamabad,Mobile Cover,3,450
105,Karachi,Charger,2,1200
106,Lahore,Earbuds,1,3500
107,Multan,Power Bank,1,4200

Reading a CSV File

Example

python

import pandas as pd

df = pd.read_csv("orders.csv")
print(df)

Output

   order_id       city       product  qty  price
0       101    Karachi  Mobile Cover    2    450
1       102     Lahore       Charger    1   1200
2       103    Karachi       Earbuds    1   3500
3       104  Islamabad  Mobile Cover    3    450
4       105    Karachi       Charger    2   1200
5       106     Lahore       Earbuds    1   3500
6       107     Multan    Power Bank    1   4200

Pandas uses the first line as column names automatically.

Looking at the First and Last Rows

Real files can have thousands of rows. head() and tail() show only a few:

Example

python

import pandas as pd

df = pd.read_csv("orders.csv")
print(df.head(3))
print(df.tail(2))

Output

   order_id     city       product  qty  price
0       101  Karachi  Mobile Cover    2    450
1       102   Lahore       Charger    1   1200
2       103  Karachi       Earbuds    1   3500
   order_id    city     product  qty  price
5       106  Lahore     Earbuds    1   3500
6       107  Multan  Power Bank    1   4200

Without a number, both show 5 rows.

info() and describe()

info() tells you the columns, their types and how many values are filled. describe() gives statistics for the number columns.

Example

python

import pandas as pd

df = pd.read_csv("orders.csv")
df.info()
print(df.describe())

Output

<class 'pandas.DataFrame'>
RangeIndex: 7 entries, 0 to 6
Data columns (total 5 columns):
 #   Column    Non-Null Count  Dtype
---  ------    --------------  -----
 0   order_id  7 non-null      int64
 1   city      7 non-null      str  
 2   product   7 non-null      str  
 3   qty       7 non-null      int64
 4   price     7 non-null      int64
dtypes: int64(3), str(2)
memory usage: 412.0 bytes
         order_id       qty        price
count    7.000000  7.000000     7.000000
mean   104.000000  1.571429  2071.428571
std      2.160247  0.786796  1601.524571
min    101.000000  1.000000   450.000000
25%    102.500000  1.000000   825.000000
50%    104.000000  1.000000  1200.000000
75%    105.500000  2.000000  3500.000000
max    107.000000  3.000000  4200.000000

Run head(), info() and describe() first on every new file. They quickly show you missing values, wrong types and strange numbers.

Useful read_csv Options

Option	What it does	Example
`sep`	A different separator	`sep=";"`
`usecols`	Read only some columns	`usecols=["city", "qty"]`
`index_col`	Use a column as the index	`index_col="order_id"`
`nrows`	Read only the first n rows	`nrows=100`
`encoding`	Text encoding of the file	`encoding="utf-8"`

Example

python

import pandas as pd

df = pd.read_csv("orders.csv", index_col="order_id", usecols=["order_id", "city", "qty"])
print(df.head(3))

Output

             city  qty
order_id              
101       Karachi    2
102        Lahore    1
103       Karachi    1

Other File Types

File type	Read	Write
CSV	`pd.read_csv("file.csv")`	`df.to_csv("file.csv", index=False)`
Excel	`pd.read_excel("file.xlsx")`	`df.to_excel("file.xlsx", index=False)`
JSON	`pd.read_json("file.json")`	`df.to_json("file.json")`

Excel files need one extra package: pip install openpyxl.

Saving a File

Example

python

import pandas as pd

df = pd.read_csv("orders.csv")
karachi = df[df["city"] == "Karachi"]
karachi.to_csv("karachi_orders.csv", index=False)
print("Saved", len(karachi), "rows")

Output

Saved 3 rows

index=False stops Pandas from writing the 0, 1, 2 index as an extra column.

Exercise

Which method shows the column types and how many values are not empty?

describe()head()info()shape