Creating DataFrames and reading CSVs

The idea

Most of your data won't be typed into Python by hand; it arrives as files. The most common format by far is CSV, and pandas reads it with one function: pd.read_csv(). That one function has dozens of options because real-world CSVs are messy: semicolons instead of commas, title rows above the header, Indonesian number formats like 1.250.000, "N/A" in empty cells.

This module covers the handful of options that solve 95% of those problems, plus the other ways to build a DataFrame from Python data. This project contains Toko Kita's sales.csv (one row per product in an order) so you can follow along.

Reading a CSV

The shop's sales system exports every order line to a file called sales.csv. Each line of the file is one row, with the values separated by commas, and the first line holds the column names. One function call turns the whole file into a DataFrame:

import pandas as pd

sales = pd.read_csv("sales.csv")
print(sales.shape)
print(sales[["order_id", "customer", "product", "revenue"]].head(3))

(71, 12)
   order_id       customer            product  revenue
0         1  Kartika Rahma     Desk Organizer    89000
1         2     Citra Dewi  Arabica Gayo 250g   285000
2         3      Eka Putri   Pour-over Kettle   960000

• pd.read_csv("sales.csv") reads the file into a DataFrame. The first line becomes the column names.
• .shape tells you it has 71 rows and 12 columns.
• sales[[...]] with a list of column names picks several columns (more on selecting in module 4), and .head(3) shows the first three rows.

pandas also guessed each column's type: numbers became int64, text became object. You'll check and fix types in the next module.

Building DataFrames from Python data

You already know the dictionary of lists form from module 1. Two more shapes come up constantly.

A list of dictionaries (one per row) is exactly what JSON APIs give you:

rows = [
    {"sku": "P003", "name": "Arabica Gayo 250g", "price": 95000},
    {"sku": "P006", "name": "Ceramic Mug", "price": 55000},
    {"sku": "P008", "name": "Gel Pen Set"},
]
products = pd.DataFrame(rows)
print(products)

    sku               name    price
0  P003  Arabica Gayo 250g  95000.0
1  P006        Ceramic Mug  55000.0
2  P008        Gel Pen Set      NaN

The third row had no price, so pandas filled in NaN ("not a number"), pandas' marker for a missing value. Because NaN is technically a float, the whole column became floats (95000.0). You'll handle missing data properly in module 8.

A list of lists (rows without names) needs the column names passed separately:

orders = pd.DataFrame(
    [["NG-001", "Ayu", 285000], ["NG-002", "Budi", 960000]],
    columns=["order_id", "customer", "total"],
)
print(orders)

  order_id customer   total
0   NG-001      Ayu  285000
1   NG-002     Budi  960000

The read_csv options you'll actually use

Here's a messy export, as you might get from an Indonesian accounting system. It's in this project as export.csv:

Toko Kita - Laporan Penjualan
Diekspor: 05/10/2024
tanggal;produk;jumlah;total
01/07/2024;Kopi Arabica;2;190.000
02/07/2024;Teh Tarik;1;18.000
02/07/2024;Roti Bakar;N/A;45.000

Reading it with no options produces garbage, because pandas expects commas and a header on the first line. These options fix it:

export = pd.read_csv(
    "export.csv",
    sep=";",
    skiprows=2,
    thousands=".",
    na_values=["N/A"],
)
print(export)
print(export.dtypes)

      tanggal        produk  jumlah   total
0  01/07/2024  Kopi Arabica     2.0  190000
1  02/07/2024     Teh Tarik     1.0   18000
2  02/07/2024    Roti Bakar     NaN   45000
tanggal     object
produk      object
jumlah     float64
total        int64
dtype: object

Choosing columns and the index while reading

A real export often has far more columns than you need, and sometimes millions of rows. You don't have to load everything and throw most of it away afterwards. read_csv can pick the columns you want, use one of them as the index, and stop after a few rows, which is handy for a quick peek at a huge file:

small = pd.read_csv("sales.csv", usecols=["order_id", "product", "revenue"], index_col="order_id", nrows=4)
print(small)

                    product  revenue
order_id                            
1            Desk Organizer    89000
2         Arabica Gayo 250g   285000
3          Pour-over Kettle   960000
4            Scented Candle    75000

index_col replaced the 0, 1, 2… labels with the order IDs. Notice the index can repeat: an order with two products would have two rows labelled with the same order_id. An index doesn't have to be unique, which surprises people coming from databases.

Reading from text and URLs

read_csv accepts more than file paths. A URL works on your own computer (pd.read_csv("https://.../data.csv")), and for text already in memory, wrap it in io.StringIO, which makes a string behave like a file:

import io

text = "city,orders\nJakarta,18\nBandung,6\n"
cities = pd.read_csv(io.StringIO(text))
print(cities)

      city  orders
0  Jakarta      18
1  Bandung       6

Why this matters for clean code

Put all the knowledge about a file's quirks in one read_csv call, right where the data enters your program, and give the result a meaningful name. Then everything after works with clean columns and real numbers. Scattering fixes (replacing dots here, skipping rows there) across your analysis makes it fragile and hard to rerun next month. One well-documented loading step is the foundation of a reproducible analysis.

Recap

• pd.read_csv(path) loads a CSV; .shape and .head() give a first look.
• pd.DataFrame(list_of_dicts) builds one row per dict; missing keys become NaN.
• pd.DataFrame(list_of_lists, columns=[...]) needs column names.
• Key options: sep, skiprows, thousands, decimal, na_values, usecols, nrows, header/names, index_col, encoding.
• io.StringIO(text) lets you read CSV text from a string.
• Look at the raw file first; handle its quirks in one loading step.