Vinyl Syntax

Basic syntax

with car as c:
    c.select([c.Name, c.Horsepower], sort=c.Year)
    c.limit(10)

SQL
Result
AST
AST graph

r0 := DatabaseTable: cars
  Name             string
  Miles_per_Gallon float64
  Cylinders        int64
  Displacement     float64
  Horsepower       float64
  Weight_in_lbs    int64
  Acceleration     float64
  Year             timestamp(6)
  Origin           string

r1 := Project[r0]
  Year:       r0.Year
  Name:       r0.Name
  Horsepower: r0.Horsepower

r2 := Sort[r1]
  asc r1.Year

Limit[r2, n=10]

Null handling

from vinyl.operations import coalesce

with birdstrikes as b:
    b.filter(b.Effect__Amount_of_damage == None)
    b.filter(b.Airport__Name != None)
    b.select({"airport_name": coalesce(b.Airport__Name, "Unknown")})

column is None and column is not None will not work as expected because Python requires is statements to evaluate to True or False.

SQL
Result
AST
AST graph

r0 := DatabaseTable: birdstrikes​
  Airport__Name              string
  Aircraft__Make_Model       string
  Flight_Date                string
  Aircraft__Airline_Operator string
  Origin_State               string
  When__Phase_of_flight      string
  Wildlife__Size             string
  Wildlife__Species          string
  When__Time_of_day          string
  Cost__Other                int64
  Cost__Repair               int64
  Cost__Total_$              int64
  Speed_IAS_in_knots         float64
  Effect__Amount_of_damage   string

r1 := Filter[r0]
  IsNull(r0.Effect__Amount_of_damage)

r2 := Filter[r1]
  NotNull(r1.Airport__Name)

Project[r2]
  airport_name: Coalesce([r2.Airport__Name, 'Unknown'])

Functions

def perimeter(x, y):
    return 2 * (x + y)


with iris as i:
    i.define({"petalPerimeter": perimeter(i.petalLength, i.petalWidth)})

SQL
Result
AST
AST graph

r0 := DatabaseTable: iris
  sepalLength float64
  sepalWidth  float64
  petalLength float64
  petalWidth  float64
  species     string

Project[r0]
  sepalLength:    r0.sepalLength
  sepalWidth:     r0.sepalWidth
  petalLength:    r0.petalLength
  petalWidth:     r0.petalWidth
  species:        r0.species
  petalPerimeter: r0.petalLength + r0.petalWidth * 2

Window Automation

with co2_concentration as c:
    c.define(
        {"CO2_normalized": c.CO2 - c.CO2.mean()},
        by=[c.Date.dt.extract("month")],
    )
    c.select([c.CO2_normalized], sort=c.Date)

SQL
Result
AST
AST graph

r0 := DatabaseTable: co2_concentration​
  CO2  float64
  Date date

r1 := Project[r0]
  ExtractMonth(Date): ExtractMonth(r0.Date)
  CO2:                r0.CO2
  Date:               r0.Date
  CO2_normalized:     r0.CO2 - WindowFunction(func=Mean(r0.CO2), frame=RowsWindowFrame(table=r0, group_by=[ExtractMonth(r0.Date)]))

r2 := Project[r1]
  Date:           r1.Date
  CO2_normalized: r1.CO2_normalized

Sort[r2]
  asc r2.Date

Structs

with weather as w:
    w.define({"high_vs_expected": w.actual["high"] - w.normal["high"]})
    w.dropna(w.high_vs_expected)

SQL
Result
AST
AST graph

r0 := DatabaseTable: weather​
  day    string
  normal high: int64
  low:  int64
  actual high: int64
  low:  int64
  id     int64

r1 := Project[r0]
  day:              r0.day
  normal:           r0.normal
  actual:           r0.actual
  id:               r0.id
  high_vs_expected: StructField(r0.actual, field='high') - StructField(r0.normal, field='high')

DropNa[r1]
  how:
    any
  subset:
    r1.high_vs_expected

Pivots

import re

with budget as b:
    b.unpivot(
        [col for col in b.columns if re.match(r"\d{4}", col)], 
        colnames_to = "year", 
        colnames_transform = int, 
        values_to= "budget"
    )

SQL
Result
AST
AST graph

r0 := DatabaseTable: budget​
  Source Category Code int64
  Source subcategory   int64
  Agency code          int64
  Bureau code          int64
  Account code         int64
  Treasury Agency code int64
  On- or off-budget    string
  1962                 string
  1963                 string
  1964                 string
  1965                 string
  1966                 string
  1967                 string
  1968                 string
  1969                 string
  1970                 string
  1971                 string
  1972                 string
  1973                 string
  1974                 string
  1975                 string
  1976                 string
  TQ                   string
  1977                 string
  1978                 string
  1979                 string
  1980                 string
  1981                 string
  1982                 string
  1983                 string
  1984                 string
  1985                 string
  1986                 string
  1987                 string
  1988                 string
  1989                 string
  1990                 string
  1991                 string
  1992                 string
  1993                 string
  1994                 string
  1995                 string
  1996                 string
  1997                 string
  1998                 string
  1999                 string
  2000                 string
  2001                 string
  2002                 string
  2003                 string
  2004                 string
  2005                 string
  2006                 string
  2007                 string
  2008                 string
  2009                 string
  2010                 string
  2011                 string
  2012                 string
  2013                 string
  2014                 string
  2015                 string
  2016                 string
  2017                 string
  2018                 string
  2019                 string
  2020                 string

r1 := Project[r0]
  Source Category Code: r0['Source Category Code']
  Source subcategory:   r0['Source subcategory']
  Agency code:          r0['Agency code']
  Bureau code:          r0['Bureau code']
  Account code:         r0['Account code']
  Treasury Agency code: r0['Treasury Agency code']
  On- or off-budget:    r0['On- or off-budget']
  TQ:                   r0.TQ
  __pivoted__:          Unnest(Array([StructColumn(names=['year', 'budget'], values=[1962, r0['1962']]), StructColumn(names=['year', 'budget'], values=[1963, r0['1963']]), StructColumn(names=['year', 'budget'], values=[1964, r0['1964']]), StructColumn(names=['year', 'budget'], values=[1965, r0['1965']]), StructColumn(names=['year', 'budget'], values=[1966, r0['1966']]), StructColumn(names=['year', 'budget'], values=[1967, r0['1967']]), StructColumn(names=['year', 'budget'], values=[1968, r0['1968']]), StructColumn(names=['year', 'budget'], values=[1969, r0['1969']]), StructColumn(names=['year', 'budget'], values=[1970, r0['1970']]), StructColumn(names=['year', 'budget'], values=[1971, r0['1971']]), StructColumn(names=['year', 'budget'], values=[1972, r0['1972']]), StructColumn(names=['year', 'budget'], values=[1973, r0['1973']]), StructColumn(names=['year', 'budget'], values=[1974, r0['1974']]), StructColumn(names=['year', 'budget'], values=[1975, r0['1975']]), StructColumn(names=['year', 'budget'], values=[1976, r0['1976']]), StructColumn(names=['year', 'budget'], values=[1977, r0['1977']]), StructColumn(names=['year', 'budget'], values=[1978, r0['1978']]), StructColumn(names=['year', 'budget'], values=[1979, r0['1979']]), StructColumn(names=['year', 'budget'], values=[1980, r0['1980']]), StructColumn(names=['year', 'budget'], values=[1981, r0['1981']]), StructColumn(names=['year', 'budget'], values=[1982, r0['1982']]), StructColumn(names=['year', 'budget'], values=[1983, r0['1983']]), StructColumn(names=['year', 'budget'], values=[1984, r0['1984']]), StructColumn(names=['year', 'budget'], values=[1985, r0['1985']]), StructColumn(names=['year', 'budget'], values=[1986, r0['1986']]), StructColumn(names=['year', 'budget'], values=[1987, r0['1987']]), StructColumn(names=['year', 'budget'], values=[1988, r0['1988']]), StructColumn(names=['year', 'budget'], values=[1989, r0['1989']]), StructColumn(names=['year', 'budget'], values=[1990, r0['1990']]), StructColumn(names=['year', 'budget'], values=[1991, r0['1991']]), StructColumn(names=['year', 'budget'], values=[1992, r0['1992']]), StructColumn(names=['year', 'budget'], values=[1993, r0['1993']]), StructColumn(names=['year', 'budget'], values=[1994, r0['1994']]), StructColumn(names=['year', 'budget'], values=[1995, r0['1995']]), StructColumn(names=['year', 'budget'], values=[1996, r0['1996']]), StructColumn(names=['year', 'budget'], values=[1997, r0['1997']]), StructColumn(names=['year', 'budget'], values=[1998, r0['1998']]), StructColumn(names=['year', 'budget'], values=[1999, r0['1999']]), StructColumn(names=['year', 'budget'], values=[2000, r0['2000']]), StructColumn(names=['year', 'budget'], values=[2001, r0['2001']]), StructColumn(names=['year', 'budget'], values=[2002, r0['2002']]), StructColumn(names=['year', 'budget'], values=[2003, r0['2003']]), StructColumn(names=['year', 'budget'], values=[2004, r0['2004']]), StructColumn(names=['year', 'budget'], values=[2005, r0['2005']]), StructColumn(names=['year', 'budget'], values=[2006, r0['2006']]), StructColumn(names=['year', 'budget'], values=[2007, r0['2007']]), StructColumn(names=['year', 'budget'], values=[2008, r0['2008']]), StructColumn(names=['year', 'budget'], values=[2009, r0['2009']]), StructColumn(names=['year', 'budget'], values=[2010, r0['2010']]), StructColumn(names=['year', 'budget'], values=[2011, r0['2011']]), StructColumn(names=['year', 'budget'], values=[2012, r0['2012']]), StructColumn(names=['year', 'budget'], values=[2013, r0['2013']]), StructColumn(names=['year', 'budget'], values=[2014, r0['2014']]), StructColumn(names=['year', 'budget'], values=[2015, r0['2015']]), StructColumn(names=['year', 'budget'], values=[2016, r0['2016']]), StructColumn(names=['year', 'budget'], values=[2017, r0['2017']]), StructColumn(names=['year', 'budget'], values=[2018, r0['2018']]), StructColumn(names=['year', 'budget'], values=[2019, r0['2019']]), StructColumn(names=['year', 'budget'], values=[2020, r0['2020']])]))

Project[r1]
  Source Category Code: r1['Source Category Code']
  Source subcategory:   r1['Source subcategory']
  Agency code:          r1['Agency code']
  Bureau code:          r1['Bureau code']
  Account code:         r1['Account code']
  Treasury Agency code: r1['Treasury Agency code']
  On- or off-budget:    r1['On- or off-budget']
  TQ:                   r1.TQ
  year:                 StructField(r1.__pivoted__, field='year')
  budget:               StructField(r1.__pivoted__, field='budget')

with barley as b:
    b.pivot(colnames_from="year", values_from="yield")

SQL
Result
AST
AST graph

r0 := DatabaseTable: barley
  yield   float64
  variety string
  year    int64
  site    string

Aggregate[r0]
  groups:
    variety: r0.variety
    site:    r0.site
  metrics:
    1931: First(r0.yield, where=r0.year == 1931)
    1932: First(r0.yield, where=r0.year == 1932)

b.visualize()

Getting Started

Showcase

Basic Concepts

Walkthrough

Integrations