A SQL to Julia DataFrames dictionary using DataFrames and DataFramesMeta. In general, I use DataFramesMeta since it abstracts away some lower level nuance and it makes for a tidier workflow when I’m constructing queries.
Overview
Querying in DataFrames and DataFramesMeta
| SQL Clause | DataFrames Equivalent | DataFramesMeta Equivalent |
|---|---|---|
SELECT |
select |
@select |
WHERE |
subset(df, :colnm => ByRow(x -> x>1)) |
@rsubset(df, :colnm > 1) |
LIKE |
subset(df, :colnm=>ByRow(x -> occursin(r"string", x))) |
@rsubset(df, occursin(r"string", :colnm)) |
GROUP BY |
gd = groupby(df, :colnm)combine(gd, :colnm=>sum) |
gd = groupby(df, :colnm)@combine(gd, :newcol=sum(:colnm))or @by(df, :colnm, :newcol=sum(:colnm)) |
SUM(colnm) OVER (PARTITION BY other_colnm) AS newcol |
gd = groupby(df, :colnm)transform(gd, :colnm=>sum) |
gd = groupby(df, :colnm)@transform(gd, :newcol=sum(:colnm)) |
ORDER BY colnm ASC |
sort(df, :colnm) |
@orderby(df, :colnm) |
ORDER BY colnm DESC |
sort(df, :colnm, rev=true) |
@orderby(df, sort(:colnm, rev=true) |
Joins
| SQL Clause | DataFrames Equivalent |
|---|---|
INNER JOIN |
innerjoin(df, df2, on=:colnm)innerjoin(df, df2, on=[:left=>:right]) |
LEFT JOIN |
leftjoin(df, df2, on=:colnm)leftjoin(df, df2, on=[:left=>:right]) |
RIGHT JOIN |
rightjoin(df, df2, on=:colnm)rightjoin(df, df2, on=[:left=>:right]) |
OUTER JOIN |
outerjoin(df, df2, on=:colnm)outerjoin(df, df2, on=[:left=>:right]) |
SELECT * FROM table1, table2 aka cartesian product or crossjoin |
crossjoin |
A Worked Example
The Dataset
We’ll manually create a dataset of employees df:
using CSV, DataFramesMeta, Statistics, Dates
# DataFrame(column=data)
df = DataFrame(id=1:8,
first_name=["Michael", "Dwight", "Angela", "Jim", "Pam", "Oscar", "Meredith", "Creed"],
last_name=["Scott", "Schrute", "Martin", "Halpert", "Beesly", "Nunez", "Palmer", "Bratton"],
department=["Management & Admin", "Sales", "Accounting", "Sales", "Management & Admin", "Accounting",
"Purchasing", "Purchasing"],
salary=[5100, 4200, 3750, 4300, 2200, 3400, 3300, 3200])
8×5 DataFrame
Row │ id first_name last_name department salary
│ Int64 String String String Int64
─────┼──────────────────────────────────────────────────────────
1 │ 1 Michael Scott Management & Admin 5100
2 │ 2 Dwight Schrute Sales 4200
3 │ 3 Angela Martin Accounting 3750
4 │ 4 Jim Halpert Sales 4300
5 │ 5 Pam Beesly Management & Admin 2200
6 │ 6 Oscar Nunez Accounting 3400
7 │ 7 Meredith Palmer Purchasing 3300
8 │ 8 Creed Bratton Purchasing 3200Let’s create a sales database called db_sales with client information (thanks to this site)
# Parse dates as Date objects
dates = ["1-2-2006", "1-29-2006", "2-1-2006", "2-14-2006", "3-1-2006", "3-20-2006"]
dates = parse.(Date, dates, dateformat"m-d-y")
db_sales = DataFrame(id=1:6,
transaction_date=dates,
employee_id=[4, 2, 4, 2, 4, 2],
quantity=[100, 500, 600, 200, 400, 250],
customer=["Dunmore High School", "Harper Collins", "Blue Cross of Pennsylvania",
"Apex Technology", "Blue Cross of Pennsylvania",
"Stone, Cooper, and Grandy: Attorneys at Law"])
6×5 DataFrame
Row │ id transaction_date employee_id quantity customer
│ Int64 Date Int64 Int64 String
─────┼───────────────────────────────────────────────────────────────────────────────────
1 │ 1 2006-01-02 4 100 Dunmore High School
2 │ 2 2006-01-29 2 500 Harper Collins
3 │ 3 2006-02-01 4 600 Blue Cross of Pennsylvania
4 │ 4 2006-02-14 2 200 Apex Technology
5 │ 5 2006-03-01 4 400 Blue Cross of Pennsylvania
6 │ 6 2006-03-20 2 250 Stone, Cooper, and Grandy: Attor…Subsetting Rows
Subsetting rows is possible in base DataFrames, but the syntax in DataFramesMeta is easier for beginners to follow. The special @rsubset macro saves on having to write anonymous functions so it’s one less syntactical thing to keep typing every time.
#DataFrames
subset(df, :department => ByRow(x -> occursin("Admin", x)))
#DataFramesMeta
@rsubset(df, occursin("Admin", :department))
2×5 DataFrame
Row │ id first_name last_name department salary
│ Int64 String String String Int64
─────┼──────────────────────────────────────────────────────────
1 │ 1 Michael Scott Management & Admin 5100
2 │ 5 Pam Beesly Management & Admin 2200Matching Text
What if you want to do some string matching with wildcards, i.e. SQL WHERE clause with the LIKE or % operator?
We can use the occursin() function and pass it as an argument to @rsubset, like:
#DataFrames
subset(df, :department => ByRow(x -> occursin("Admin", x)))
#DataFramesMeta
@rsubset(df, occursin("Admin", :department))
2×5 DataFrame
Row │ id first_name last_name department salary
│ Int64 String String String Int64
─────┼──────────────────────────────────────────────────────────
1 │ 1 Michael Scott Management & Admin 5100
2 │ 5 Pam Beesly Management & Admin 2200Adding the r in front of the string lets you use regex to use wildcards and more complex string matching criteria.
Aggregation
i.e. GROUP BY Column and SUM(column)
For regular GROUP BY you first use groupby() and then either combine or @combine, or you can use the @by function as shorthand.
Grouping with @by
@by(df, :department,
:"Average Salary" = mean(:salary),
:count=length(:salary))
4×2 DataFrame
Row │ department Average Salary
│ String Float64
─────┼────────────────────────────────────
1 │ Management & Admin 3650.0
2 │ Sales 4250.0
3 │ Accounting 3575.0
4 │ Purchasing 3250.0Grouping with combine and @combine
gd = groupby(df, :department)
#DataFrames
combine(gd, :salary => mean => :"Average Salary",
:department => length => :count)
#DataFramesMeta
@combine(gd, :"Average Salary" = mean(:salary),
:count = length(:department))
4×2 DataFrame
Row │ department Average Salary
│ String Float64
─────┼────────────────────────────────────
1 │ Management & Admin 3650.0
2 │ Sales 4250.0
3 │ Accounting 3575.0
4 │ Purchasing 3250.0Window Functions with @transform
In SQL:
AVG(colnm) OVER (PARTITION BY other_colnm) AS newcoli.e. the PARTITION BY clause, similar to groupby but it returns a value for each row in your table after doing the aggregations in each partition.
In Julia, you can use the @transform macro to do this after grouping.
# Example: add a column that
gd = groupby(df, :department)
#DataFrames
transform(gd, :salary => mean => :"avg_dept_salary")
#DataFramesMeta
@transform(gd, :"avg_dept_salary"=mean(:salary))
8×6 DataFrame
Row │ id first_name last_name department salary avg_dept_salary
│ Int64 String String String Int64 Float64
─────┼──────────────────────────────────────────────────────────────────────────
1 │ 1 Michael Scott Management & Admin 5100 3650.0
2 │ 2 Dwight Schrute Sales 4200 4250.0
3 │ 3 Angela Martin Accounting 3750 3575.0
4 │ 4 Jim Halpert Sales 4300 4250.0
5 │ 5 Pam Beesly Management & Admin 2200 3650.0
6 │ 6 Oscar Nunez Accounting 3400 3575.0
7 │ 7 Meredith Palmer Purchasing 3300 3250.0
8 │ 8 Creed Bratton Purchasing 3200 3250.0And we see indeed that we have the same number of rows as the initial dataset.
Putting together a query with @chain
sales = @chain db_sales begin
groupby(:employee_id)
@combine(:total_quantity=sum(:quantity),
:number_of_customers=length(:customer))
end
result = @chain df begin
@select(:id, :first_name, :last_name, :department)
@rsubset(:department=="Sales")
leftjoin(sales, on=[:id=>:employee_id])
@orderby(sort(:total_quantity, rev=true)) # descending
end
2×6 DataFrame
Row │ id first_name last_name department total_quantity number_of_customers
│ Int64 String String String Int64? Int64?
─────┼───────────────────────────────────────────────────────────────────────────────
1 │ 4 Jim Halpert Sales 1100 3
2 │ 2 Dwight Schrute Sales 950 3Alternative: Use DuckDB
Another amazing alternative is to use DuckDB to query dataframes, CSV files, parquet files, etc directly with SQL.
Assuming we had the dataframes created above, we’d query it with SQL with a few lines:
using DuckDB
# create a new in-memory dabase
con = DBInterface.connect(DuckDB.DB)
# register it as a view in the database
DuckDB.register_data_frame(con, df, "my_df")
queryStr = """
SELECT
first_name
, last_name
, department
, salary
, AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
FROM my_df
"""
# run a SQL query over the DataFrame
results = DBInterface.execute(con, queryStr)
print(results)
# 8×5 DataFrame
# Row │ first_name last_name department salary avg_dept_salary
# │ String? String? String? Int64? Float64?
# ─────┼────────────────────────────────────────────────────────────────────
# 1 │ Angela Martin Accounting 3750 3575.0
# 2 │ Oscar Nunez Accounting 3400 3575.0
# 3 │ Michael Scott Management & Admin 5100 3650.0
# 4 │ Pam Beesly Management & Admin 2200 3650.0
# 5 │ Meredith Palmer Purchasing 3300 3250.0
# 6 │ Creed Bratton Purchasing 3200 3250.0
# 7 │ Dwight Schrute Sales 4200 4250.0
# 8 │ Jim Halpert Sales 4300 4250.0