scrappy data analysis, with seamless support for pandas and SQL
siuba is a port of dplyr and other R libraries. It supports a tabular data analysis workflow centered on 5 common actions:
select()- keep certain columns of data.
filter()- keep certain rows of data.
mutate()- create or modify an existing column of data.
summarize()- reduce one or more columns down to a single number.
arrange()- reorder the rows of data.
These actions can be preceeded by a
group_by(), which causes them to be applied individually to grouped rows of data. Moreover, many SQL concepts, such as
count(), and joins are implemented.
Inputs to these functions can be a pandas
DataFrame or SQL connection (currently postgres, redshift, or sqlite).
pip install siuba
The code below uses the example DataFrame
mtcars, to get the average horsepower (hp) per cylinder.
from siuba import group_by, summarize, _ from siuba.data import mtcars (mtcars >> group_by(_.cyl) >> summarize(avg_hp = _.hp.mean()) )
Out: cyl avg_hp 0 4 82.636364 1 6 122.285714 2 8 209.214286
There are three key concepts in this example:
||a function that operates on a table, like a DataFrame or SQL table|
||an expression created with
||a syntax that allows you to chain verbs with the
What is a siu expression (e.g.
_.cyl == 4)?
A siu expression is a way of specifying what action you want to perform. This allows siuba verbs to decide how to execute the action, depending on whether your data is a local DataFrame or remote table.
from siuba import _ _.cyl == 4
Out: █─== ├─█─. │ ├─_ │ └─'cyl' └─4
You can also think siu expressions as a shorthand for a lambda function.
from siuba import _ # lambda approach mtcars[lambda _: _.cyl == 4] # siu expression approach mtcars[_.cyl == 4]
Out: mpg cyl disp hp drat wt qsec vs am gear carb 2 22.8 4 108.0 93 3.85 2.320 18.61 1 1 4 1 7 24.4 4 146.7 62 3.69 3.190 20.00 1 0 4 2 .. ... ... ... ... ... ... ... .. .. ... ... 27 30.4 4 95.1 113 3.77 1.513 16.90 1 1 5 2 31 21.4 4 121.0 109 4.11 2.780 18.60 1 1 4 2 [11 rows x 11 columns]
Using with SQL
A killer feature of siuba is that the same analysis code can be run on a local DataFrame, or a SQL source.
In the code below, we set up an example database.
# Setup example data ---- from sqlalchemy import create_engine from siuba.data import mtcars # copy pandas DataFrame to sqlite engine = create_engine("sqlite:///:memory:") mtcars.to_sql("mtcars", engine, if_exists = "replace")
Next, we use the code from the first example, except now executed a SQL table.
# Demo SQL analysis with siuba ---- from siuba import _, group_by, summarize, filter from siuba.sql import LazyTbl # connect with siuba tbl_mtcars = LazyTbl(engine, "mtcars") (tbl_mtcars >> group_by(_.cyl) >> summarize(avg_hp = _.hp.mean()) )
Out: # Source: lazy query # DB Conn: Engine(sqlite:///:memory:) # Preview: cyl avg_hp 0 4 82.636364 1 6 122.285714 2 8 209.214286 # .. may have more rows
Below are some examples I've kept as I've worked on siuba. For the most up to date explanations, see the siuba docs
- siu expressions
- dplyr style pandas
- sql using dplyr style
- tidytuesday examples
- tidytuesday is a weekly R data analysis project. In order to kick the tires on siuba, I've been using it to complete the assignments. More specifically, I've been porting Dave Robinson's tidytuesday analyses to use siuba.
Tests are done using pytest. They can be run using the following.
# start postgres db docker-compose up pytest siuba