Reading CSV data · Pandas

Most real data arrives as a file. pd.read_csv turns a CSV file, the most common format of all, into a DataFrame.

Reading a CSV file

Python

import pandas as pd

with open("orders.csv", "w") as f:
    f.write("""order_id,date,customer,product,quantity,price
1001,2024-06-01,Asha,Keyboard,1,89.99
1002,2024-06-03,Leo,Monitor,2,279.00
1003,2024-06-03,Mei,Mouse,3,24.50
1004,2024-06-10,Asha,Webcam,1,59.00
1005,2024-06-12,Raj,Keyboard,2,89.99
""")

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

The first line becomes the column names, and pandas works out each column's type from its values.

Check what you loaded

Python

import pandas as pd

with open("orders.csv", "w") as f:
    f.write("""order_id,date,customer,product,quantity,price
1001,2024-06-01,Asha,Keyboard,1,89.99
1002,2024-06-03,Leo,Monitor,2,279.00
1003,2024-06-03,Mei,Mouse,3,24.50
""")

orders = pd.read_csv("orders.csv")
print(orders.head(2))      # the first rows
print(orders.dtypes)       # the type of each column
orders.info()              # types, non-missing counts and memory in one report

Always look before you analyse. Numbers should be int64 or float64. Text columns show as object, or str in newer versions of pandas. A column of numbers that comes out as text usually means a stray value such as "n/a" somewhere in it.

Useful options

Option	Does
`sep=";"`	uses a different separator
`index_col="order_id"`	uses a column as the index
`usecols=["date", "price"]`	loads only some columns
`parse_dates=["date"]`	reads a column as dates
`na_values=["n/a", "-"]`	treats these strings as missing
`nrows=1000`	reads only the first rows, handy for huge files
`dtype={"code": str}`	forces a column's type
`header=None, names=[...]`	for files with no header row

Python

import pandas as pd

with open("readings.csv", "w") as f:
    f.write("""station;date;temp_c;rain_mm
A;2024-07-01;31.2;0
A;2024-07-02;n/a;12.5
B;2024-07-01;28.9;-
B;2024-07-02;27.4;3.1
""")

df = pd.read_csv("readings.csv", sep=";", parse_dates=["date"], na_values=["n/a", "-"])
print(df)
print(df["date"].dt.day_name())
print(round(df["temp_c"].mean(), 2))

sep=";" splits on semicolons, a common format in countries that write decimals with a comma. na_values turns n/a and - into proper missing values, so mean simply skips them. And parse_dates makes real dates, so .dt can tell you the day of the week.

Keeping codes as text

Python

import pandas as pd

with open("customers.csv", "w") as f:
    f.write("""id,name,code,phone
1,Asha,00731,07700900123
2,Leo,10442,07700900456
""")

guessed = pd.read_csv("customers.csv")
kept = pd.read_csv("customers.csv", dtype={"code": str, "phone": str})
print(guessed)
print(kept)

Product codes, postal codes and phone numbers look like numbers but aren't: nobody adds two phone numbers together. Read as numbers, they lose their leading zeros. dtype=str keeps them exactly as written.

Saving

Python

import pandas as pd

results = pd.DataFrame({"name": ["Asha", "Leo", "Mei"], "score": [92, 78, 85]})
results.to_csv("results.csv", index=False)
print(open("results.csv").read())

index=False leaves the index out of the file. Without it, the row numbers would be saved as an extra, unnamed first column.

Other formats

Python

pd.read_excel("report.xlsx", sheet_name="2024")    # needs the openpyxl package
pd.read_json("data.json")
pd.read_parquet("big.parquet")                      # a fast, compressed format for large data
df.to_excel("out.xlsx", index=False)