Expressiveness Benchmark

Task: Row per family to row per child

Specification: For each family, return a row for each child containing the family ID, child ID, DOB, and height.

Input: families

family	dob_child1	dob_child2	dob_child3	height_child1	height_child2	height_child3
`1`	`1998`	`1999`	`2000`	`5_11`	`5_10`	`5_9`
`2`	`1997`	`1995`	`1996`	`5_3`	`5_5`	`5_1`

Output:

family child dob height
1 child1 1998 5_11
1 child2 1999 5_10
1 child3 2000 5_9
2 child1 1997 5_3
2 child2 1995 5_5
2 child3 1996 5_1

family	child	dob	height
`1`	`child1`	`1998`	`5_11`
`1`	`child2`	`1999`	`5_10`
`1`	`child3`	`2000`	`5_9`
`2`	`child1`	`1997`	`5_3`
`2`	`child2`	`1995`	`5_5`
`2`	`child3`	`1996`	`5_1`

Python - Imperative


def row_per_child(families):
  children = []
  for family in families:
    for i in [1, 2, 3]:
      children.append({
        'family': family['family'],
        'child': f'child{i}',
        'dob': family[f'dob_child{i}'],
        'height': family[f'height_child{i}']
      })
  return children

Python - Functional

def row_per_child(families):
  return [
    {'family': family['family'],
     'child': f'child{i}',
     'dob': family[f'dob_child{i}'],
     'height': family[f'height_child{i}']}
    for family in families    
    for i in [1, 2, 3]
  ]

Python - Pandas

def row_per_child(families):
  df = pd.wide_to_long(
    families, 
    stubnames=['dob', 'height'], 
    sep="_child", 
    i='family', 
    j='child').reset_index()
  df.child = df.child.map(lambda c: f'child{c}')
  return df

R - Tidyverse

row_per_child <- function(families) {
  families %>%
    pivot_longer(
      !family,
      names_to = c(".value", "child"),
      names_sep = "_",
    )
}

SQL - SQLite

SELECT 
  family,
  ('child' || child) AS child,
  (CASE child 
    WHEN 1 THEN dob_child1 
    WHEN 2 THEN dob_child2 
    WHEN 3 THEN dob_child3 
   END) AS dob,
  (CASE child 
    WHEN 1 THEN height_child1 
    WHEN 2 THEN height_child2 
    WHEN 3 THEN height_child3 
   END) AS height
FROM 
  families
  CROSS JOIN
  (SELECT 1 as child UNION VALUES (2), (3))

Datalog - Souffle

row_per_child("child1", dob, family, height) :-
  families(dob, _, _, family, height, _, _).
row_per_child("child2", dob, family, height) :-
  families(_, dob, _, family, _, height, _).
row_per_child("child3", dob, family, height) :-
  families(_, _, dob, family, _, _, height).

Q - kdb+

child_rows: {[child] 
  rows: ?[families; (); 0b; `family`dob`height ! 
    (`family; `$("dob_child",child); `$("height_child", child))];
  update child: (count families)#enlist ("child", child) from rows}
      
row_per_child: (child_rows each ("1"; "2"; "3")) ,/;