DataFrames.jl Basics

Analogy categoryAnalogies

Introducing DataFrames.jl

DataFrames.jl is Julia's primary package for working with tabular data — think of a table with named columns, similar to a spreadsheet, a SQL table, or Python's pandas DataFrame — where each column is stored internally as its own typed vector, allowing operations on a single column to run at full compiled speed without boxing. Because Julia's type system flows naturally into DataFrames.jl, columns retain concrete element types like Int64, Float64, or String rather than a generic 'object' type, which is part of why DataFrames.jl operations tend to be significantly faster than dynamically-typed equivalents once your code is written to respect column types.

🏏

Cricket analogy: A scorebook with a dedicated column for runs, another for balls faced, and another for player names, each column tracked separately and consistently, is exactly how a DataFrame stores data — the runs column stays numeric throughout, so summing it never requires checking each entry's type.

Creating and Inspecting DataFrames

A DataFrame can be constructed directly from named vectors passed as keyword arguments, from a vector of NamedTuples, or by reading a file with CSV.jl via CSV.read(path, DataFrame); once created, first(df, 5) or last(df, 5) show a preview, names(df) lists column names, describe(df) gives per-column summary statistics like mean, min, max, and missing counts, and size(df) returns the (rows, columns) tuple. Missing data is represented with Julia's built-in missing value rather than NaN or a sentinel, and columns containing it get an element type like Union{Missing, Float64}, which forces you to explicitly handle missingness with functions like skipmissing() or coalesce() before most numeric aggregations will work.

🏏

Cricket analogy: A curator previewing the first five entries of a massive historical scorecard archive before diving in, rather than reading every match, mirrors first(df, 5) giving a quick preview of a DataFrame without loading the analyst's full attention onto every row.

julia

using DataFrames, CSV

# Construct directly from named vectors
df = DataFrame(name = ["Alice", "Bob", "Carol"],
               age  = [29, 34, 41],
               city = ["Chennai", "Pune", missing])

names(df)          # ["name", "age", "city"]
size(df)            # (3, 3)
first(df, 2)         # preview first two rows
describe(df)         # per-column stats, including missing counts

# Reading from CSV directly into a DataFrame
sales = CSV.read("sales.csv", DataFrame)
mean(skipmissing(df.age))   # 34.666..., ignoring any missing ages

Selecting, Filtering, and Transforming Data

Individual columns are accessed with dot syntax (df.age) or bracket indexing (df[:, :age]), and DataFrames.jl's convention is that df.age or df[!, :age] returns the actual underlying column vector without copying (mutations affect the original), while df[:, :age] returns a fresh copy — a distinction that matters a great deal in memory-sensitive code. Row filtering is done with subset(df, :age => x -> x .> 30) or the shorthand filter(row -> row.age > 30, df), and column transformation uses select or transform with a Pair syntax like :age => (x -> x .+ 1) => :age_next_year, which reads naturally as 'take this column, apply this function, name the result this'.

🏏

Cricket analogy: Handing a scorer the actual master scorebook to mark up live lets edits reflect immediately in the official record, while handing a photocopy to a journalist lets them scribble notes without touching the original — df[!, :col] is the master scorebook, df[:, :col] is the photocopy.

df[!, :col] and df.col return the underlying column vector by reference, so df.age .+= 1 actually mutates the DataFrame's data in place — this is fast and often desired, but assigning that reference elsewhere (x = df.age) means later mutating x will silently mutate df too. Use df[:, :col] (with a colon, not an exclamation mark) whenever you need an independent copy.

Grouping and Aggregating with groupby and combine

The split-apply-combine pattern is implemented with groupby(df, :category), which produces a GroupedDataFrame partitioning rows by the distinct values in one or more key columns without copying data, followed by combine(gdf, :value => mean => :avg_value) which applies an aggregation function to each group and stitches the results back into a single summary DataFrame. This two-step pattern — groupby then combine — is the idiomatic replacement for what SQL's GROUP BY or pandas' .groupby().agg() would do, and combine can apply multiple named aggregations at once by passing several column => function => newname pairs.

🏏

Cricket analogy: Sorting an entire season's ball-by-ball data into separate piles by bowler, then computing each bowler's average economy rate from their own pile, is exactly the split-apply-combine pattern — groupby does the sorting into piles, combine computes and stitches the per-bowler averages back into one table.

julia

using DataFrames, Statistics

df = DataFrame(region = ["North", "South", "North", "South", "North"],
               revenue = [120.0, 95.0, 140.0, 80.0, 110.0])

gdf = groupby(df, :region)
summary = combine(gdf, :revenue => mean => :avg_revenue,
                        :revenue => sum  => :total_revenue)

# summary now has one row per region with avg_revenue and total_revenue columns

# Joining two tables on a shared key column
customers = DataFrame(id = [1, 2, 3], name = ["Alice", "Bob", "Carol"])
orders    = DataFrame(id = [1, 1, 2], amount = [50.0, 25.0, 75.0])
innerjoin(customers, orders, on = :id)   # rows only for customers with orders

Joining DataFrames

DataFrames.jl provides SQL-style join functions — innerjoin, leftjoin, rightjoin, outerjoin, semijoin, and antijoin — all taking an on keyword specifying the shared key column(s) to match rows between two tables. innerjoin(a, b, on=:id) keeps only rows where the key exists in both tables, leftjoin(a, b, on=:id) keeps every row from a and fills unmatched columns from b with missing, and when column names overlap but aren't the join key, DataFrames.jl automatically renames them with _1/_2 suffixes (or you can specify makeunique=true) to avoid silent collisions.

🏏

Cricket analogy: Matching a list of registered players against a list of match-day availability, keeping only names that appear on both lists, mirrors innerjoin keeping only rows whose key matches in both tables, while keeping the full squad list and marking absentees as unavailable mirrors leftjoin filling unmatched rows with missing.

Join cheat sheet: innerjoin keeps only matched rows; leftjoin keeps all rows from the first table; rightjoin keeps all rows from the second table; outerjoin keeps all rows from both; semijoin filters the first table to rows that have a match (like innerjoin but keeps only the first table's columns); antijoin filters the first table to rows with NO match — useful for finding 'customers with no orders' style queries.

DataFrames.jl stores tabular data with each column as its own typed vector, giving compiled-speed operations rather than generic 'object' columns.
Construct DataFrames from named vectors, NamedTuples, or CSV.read(path, DataFrame); inspect with first(), describe(), names(), and size().
Missing data uses Julia's built-in missing value; handle it explicitly with skipmissing() or coalesce() before aggregating.
df.col / df[!, :col] returns the actual column by reference (fast, mutates in place); df[:, :col] returns an independent copy.
subset() and filter() select rows; select() and transform() with Pair syntax (:col => f => :newname) create or modify columns.
groupby() followed by combine() implements the split-apply-combine pattern, Julia's equivalent of SQL's GROUP BY.
innerjoin, leftjoin, rightjoin, outerjoin, semijoin, and antijoin provide SQL-style table joins keyed on the on argument.