Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 40 ยท Data and Science

DataFrames.jl

Work with tables in Julia using DataFrames.jl: build, select, filter, sort, groupby and combine, join, handle missing data, and compare with pandas.

Most data work starts with a table: rows are observations, columns are named variables of different types. In Julia the standard table type is the DataFrame from the DataFrames.jl package. If you know pandas in Python or data frames in R, the ideas will feel familiar. The syntax differs in a few places that are worth learning on purpose.

In this lesson you will create a DataFrame, pick out columns and rows, filter and sort, add new columns, summarize with groupby and combine, join two tables, deal with missing values, and read a CSV file. DataFrames.jl is a package, not part of Julia itself, and Julia is not installed on this site. So the Julia code is read-only, and the runnable examples use pandas in Python to show the same operations. Each pandas line is paired with its Julia counterpart in a comment.

Creating a DataFrame

Install the package once with ] add DataFrames (see Packages and Environments), then load it. Columns are given as keyword arguments:

JULIA
using DataFrames

df = DataFrame(
    name  = ["Ana", "Ben", "Cleo", "Dev", "Eli", "Fay"],
    team  = ["red", "blue", "red", "blue", "red", "green"],
    score = [90, 72, 85, 64, 78, 88],
)

nrow(df)      # 6
ncol(df)      # 3
names(df)     # ["name", "team", "score"]
size(df)      # (6, 3)

Printed in the REPL, the table shows each column's type under its name:

Text
6ร—3 DataFrame
 Row โ”‚ name    team    score
     โ”‚ String  String  Int64
โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
   1 โ”‚ Ana     red        90
   2 โ”‚ Ben     blue       72
   3 โ”‚ Cleo    red        85
   4 โ”‚ Dev     blue       64
   5 โ”‚ Eli     red        78
   6 โ”‚ Fay     green      88

Each column is an ordinary Julia vector with a single element type, which is a big reason DataFrames are fast: a column of Int64 is stored as plain integers, not as boxed objects. You can add rows later with push!(df, ("Gus", "blue", 70)), and pass a tuple or a dictionary.

A DataFrame in four ideas

Typed columns Each column is a vector of one type.
Named columns Pick them by Symbol, like :score.
Same length Every column has one value per row.
missing allowed Gaps are values, not errors.
1 / 2

Selecting columns

There are three common ways to get at a column, and they differ in whether they copy:

JULIA
df.score            # the column itself, no copy: a Vector{Int64}
df[!, :score]       # same, no copy
df[:, :score]       # a copy of the column
df[:, [:name, :score]]   # a new DataFrame with two columns

df.score is the shortest and is what you will write all the time. Because it does not copy, changing the vector changes the table. The ! in df[!, :score] means "take the column as is", and : means "copy it". Select several columns, or compute new ones, with select, which always returns a new DataFrame:

JULIA
select(df, :name, :score)
select(df, :name, :score => (s -> s ./ 100) => :fraction)

The pattern source => function => destination appears throughout DataFrames.jl. It reads "take this column, apply this function, call the result that". The function receives the entire column as a vector, which is why the example divides with the broadcasting dot (see Broadcasting and the Dot Syntax).

Filtering and sorting rows

Row selection uses a boolean vector built with broadcasting:

JULIA
df[df.score .> 80, :]                      # rows where score is above 80
df[(df.team .== "red") .& (df.score .> 80), :]

filter(:score => >(80), df)                # same idea, written as a column predicate
subset(df, :score => s -> s .> 80)         # also keeps only matching rows

In df[rows, columns] the first slot picks rows and the second picks columns, and : means all of them. With filter(:score => >(80), df) the predicate is applied to each value in the column, so no dot is needed, and >(80) is a function that means "greater than 80". With subset the function receives the whole column and must return booleans, so it needs the dot.

Sort with sort, and keep the leading rows with first:

JULIA
sort(df, :score, rev=true)
first(sort(df, :score, rev=true), 3)       # the top three
sort(df, [:team, :score])                  # by team, then by score

Adding and changing columns

transform is like select but keeps all existing columns. Assigning to a new column name also works:

JULIA
transform(df, :score => (s -> s .>= 80) => :passed)

df.bonus = df.score .* 0.1       # add a column in place
df.score[2] = 75                 # change one cell in place

Functions ending in !, such as transform! and select!, change the DataFrame in place, following the Julia convention for mutating functions.

Split, apply, combine

Summaries by group are the heart of data analysis. First groupby splits the table by one or more columns, then combine computes something for every group and puts the results in a new table:

JULIA
using Statistics

by_team = groupby(df, :team)

combine(by_team, nrow => :players, :score => mean => :average)

The result has one row per team:

Text
3ร—3 DataFrame
 Row โ”‚ team    players  average
     โ”‚ String  Int64    Float64
โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
   1 โ”‚ red           3  84.3333
   2 โ”‚ blue          2  68.0
   3 โ”‚ green         1  88.0

Here nrow counts rows in each group, and :score => mean => :average takes each group's score column, averages it and names the output. You can ask for several summaries at once by listing them. Teams appear in the order they first show up in the data, unlike some tools that sort the keys. To pick one group, use by_team[("red",)] or loop for g in by_team. Each g is a view of that group's rows.

Joining tables

Suppose the team colors belong to coaches kept in another table. innerjoin keeps only keys found in both tables, leftjoin keeps every row of the left table, and outerjoin keeps everything:

JULIA
coaches = DataFrame(team = ["red", "blue", "gold"], coach = ["Mia", "Noor", "Omar"])

innerjoin(df, coaches, on = :team)   # no green or gold rows
leftjoin(df, coaches, on = :team)    # all players; Fay's coach is missing

In the left join, Fay's team green has no match, so her coach is missing. If the key columns have different names, use on = :a => :b.

Missing values

Julia has a built-in value for "no data", called missing. Columns that can hold it get a union type such as Union{Missing, Int64}. Most operations propagate it: 1 + missing is missing. Functions take care of it explicitly:

JULIA
scores = [90, missing, 85]

sum(scores)                    # missing
sum(skipmissing(scores))       # 175
coalesce.(scores, 0)           # [90, 0, 85]

dropmissing(df_with_gaps)      # remove rows with any missing value
ismissing(x)                   # test one value; never use x == missing

The last rule matters: x == missing is itself missing, not true or false, so it cannot be used in an if. Always test with ismissing.

Reading and writing CSV

As the lesson on Files and I/O mentioned, the CSV package reads straight into a DataFrame and guesses each column's type:

JULIA
using CSV, DataFrames

df = CSV.read("scores.csv", DataFrame)
describe(df)                       # a summary table: mean, min, max, missing counts...
CSV.write("top.csv", first(sort(df, :score, rev=true), 3))

describe(df) is the quickest way to see what you loaded. Check the column types it reports, because one stray text value in a numeric column makes the whole column a String.

pandas side by side

The two libraries cover the same ground, with different ideas about the details:

Task DataFrames.jl pandas
Column df.score df["score"] or df.score
Filter rows df[df.score .> 80, :] df[df["score"] > 80]
Add column transform(df, ...) df.assign(...)
Group and summarize combine(groupby(df, :team), ...) df.groupby("team").agg(...)
Join innerjoin(a, b, on = :k) a.merge(b, on="k")
Missing value missing NaN or None
Index none; rows are numbered 1, 2, 3 a row index
Speed of user code loops are fast loops are slow

Two differences stand out. A Julia DataFrame has no row index, so there is nothing like loc and no index to align by. And because Julia compiles your code, writing a plain for loop over rows is a legitimate, fast option, which in pandas you would avoid.

The same pipeline in pandas

This example builds the same table and walks through the operations above. Each line has the DataFrames.jl call in a comment.

Python Select, filter, group and summarize (pandas)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Python Joins and missing values (pandas)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The pandas version skips missing values by default when summing, while Julia's sum(scores) returns missing unless you ask for skipmissing. Julia makes you say so, and you cannot lose a gap by accident.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does df[!, :score] return?

    ! means take the column as is, with no copy. df[:, :score] returns a copy. df.score is the same as df[!, :score].
  2. 2. Which call gives one row per team with the number of rows in each?

    groupby splits the table by team and combine summarizes each group; nrow counts the rows in it.
  3. 3. What is 1 + missing?

    Arithmetic with missing propagates it. Use skipmissing, coalesce or dropmissing to decide what to do with gaps.
  4. 4. What happens to a left-table row with no match in leftjoin?

    A left join keeps every row of the left table; where the right table has no match, its columns hold missing.
  5. 5. How should you test whether a value is missing?

    x == missing returns missing, not a boolean, so it cannot drive an if. ismissing returns true or false.

Summary

  • A DataFrame is a table of named, typed columns; df.col gets a column without copying and df[:, :col] copies.
  • Filter rows with broadcasting (df[df.score .> 80, :]) or filter and subset; sort with sort.
  • source => function => name is the pattern used by select, transform and combine.
  • groupby plus combine is split, apply, combine; innerjoin, leftjoin and outerjoin merge tables.
  • missing propagates through arithmetic; use skipmissing, coalesce, dropmissing and ismissing.
  • Compared with pandas there is no row index, and plain loops over rows are fast.

Next: see your results as pictures in Plotting.