DEV Community: Peter Hozák

Self-Join in Sheets, SQL, Python, and JavaScript

Peter Hozák — Sat, 23 Jul 2022 13:27:36 +0000

Let's start with a self-referencing data table employees:

id  name                 manager_id
1   Djan Seriy Anaplian
2   Fal 'Ngeestra
3   Turminder Xuss       1           --->  Djan Seriy Anaplian
4   Jase                 2           --->  Fal 'Ngeestra

How can we get the other record?

Sheets

As with so many questions in Excel, Numbers, Calc, or Sheets, let's solve it by adding a column with an INDEX and MATCH formula:

# 1. find row number of the matching record
=match(C2, A:A, 0)

# 2. get value from the other record
=index(B:B, match(C2, A:A, 0))

# 3. handle errors
=iferror(index(B:B, match(C2, A:A, 0)), "")

You can try out the example in Sheets:

SQL

In virtually all database query languages, joins to a table itself work exactly like joins between tables:

select
  e.name employee,
  m.name manager
from employees e
left join employees m on e.manager_id = m.id

Try out in SQL Fiddle:

Python

To represent the "normal form" of our data in Python, let's choose a dictionary:

employees = {
    1: {'name': 'Djan Seriy Anaplian'},
    2: {'name': 'Fal \'Ngeestra'},
    3: {'name': 'Turminder Xuss', 'manager_id': 1},
    4: {'name': 'Jase', 'manager_id': 2},
}

We want to change it to a list of nested dictionaries, replacing each manager_id pointer with a live manager object, to give ourselves the most freedom to explore. Using imperative procedural code style with a walrus operator := and dict.pop(), that might look like following:

for em in employees.values():
    if manager_id := em.pop('manager_id', None):
        em['manager'] = employees[manager_id]

employees = list(employees.values())
employees[0]['short'] = 'Anaplian'
employees[1]['short'] = 'Fal'

This example lives on Repl.it. Will it work for a deeper management hierarchy? Would it break for a cyclical structure?

Let me know if you would like to see other code styles in Python, functional like JS below or FRP like the Sheets above (with Pandas).

JavaScript / TypeScript

We can start with the exact same employees object as in the Python example, but this time we choose a more team-friendly approach inspired by functional programming style that avoids mutation and side effects, because the nested objects might be used in other parts of the project. We also want to avoid too many levels of nesting to keep it simple, so we replace manager_id with an optional immutable string managerName:

const employeesWithManager = Object.values(employees)
  .map(({name, manager_id}) => ({
    name,
    ...(manager_id && {
      managerName: employees[manager_id].name
    }),
  }))

You can play with a TypeScript version on TS Playground, or run the JavaScript example directly in your browser console:

Have you noticed how we can use the spread syntax in object literal ({...expr}) with a more complex expression, not just a variable name? Can you refactor the code to use an explicit variable at that place? Which version is more readable for you and for your project coworkers?

👋 Hi, I am Peter Hozák, a.k.a. Aprillion. Do you have any follow-up or similar questions? Let's discuss in the comments below or on Twitter.

Quantum Developers

Peter Hozák — Thu, 20 Jan 2022 17:14:15 +0000

Just like electrons, software developers are impossible to observe without interfering.

In the double-slit experiment, zapping electrons with microwave radiation changes your results (for historical reasons, we call that zapping process "observation," because the photon-electron interaction on the human scale is called "seeing with your own eyes," but this process is slightly more destructive to the internal state of chill of an electron [this has led to a lot of metaphysical misunderstandings of the beautiful city of Copenhagen {but let's not get there <yet>}]).

For a long time, humanity believed that the largest beautiful quantum objects were black holes (or rainbows, depending on who you ask), but the last few centuries of corporate fashion has created a completely new phenomenon that's been appreciated infinitely more: managers trust people for whole 2 weeks at a time without pestering them every 5 minutes: "are we there yet?"!

Before a Scrum Sprint was invented, a lot of project managers tracked the tasks by percentage of completion. Centering a DIV was either 50% done or 80% done, but it wasn't acceptable not to know the exact internal state of every microtask. And as we all know, intellectual workers are a lazy folk who cannot be trusted (yeah yeah, a drive for the efficiency of our glucose consumption is driven by evolution, it's biologically impossible not to be lazy, whatever). What changed?

As with any improvement in life, empirical evidence came to the rescue. When asked about minute details, developers are never finished, the results are never perfect. When left alone with a bag of tasks, those tasks get done, somehow and good enough. Over time, managers of the surviving corporations learned a magic ritual - trust the developers to do as much as possible without checking for progress too often. Sometimes, it takes 2x longer as their original estimate, other times they finish 5x faster. But somehow, when we ask about status of some particular task, the quantum state of that task collapses into a binary state "finished" or "not finished" and the latter state always takes 1 more week to finish - so asking the same question too often will make a task infinite with non-zero probability.

So here we are. Managers in 2022 trust developers. It happens by a miracle, and the spell can be broken any time they try to micromanage. The good managers just intuitively follow the Aprillion's Law of Quantum Developers:

Just like electrons, software developers are impossible to observe without interfering.

👋 I am Peter, a.k.a. Aprillion. My strong opinions are held weakly, discussion is open here in the comments or on Twitter.

Multisort in Sheets, SQL, Python, and JavaScript

Peter Hozák — Sun, 25 Jul 2021 14:37:55 +0000

Sorting a 2D table is straightforward in most software solutions designed for data processing. If you are like me, sorting by single column was easy to remember after discovering the feature for the first time. But sorting by multiple columns can be a headache sometimes, so here are my recipes for some of the most popular tools.

Let's imagine that following table represents some useful data. We want to sort our records by column A (ascending) first, then for all records with the same value of A, we want to sort those sub-groups by column B (descending):

 A↓  B   C              A↓  B↑  C
 1   1   a              1   3   c
 1   2   b              1   2   b
 1   3   c    ----->    1   1   a
 2   1   d              2   3   f
 2   2   e              2   2   e
 2   3   f              2   1   d

Sheets

We can sort by multiple columns in Microsoft Excel by using Data -> Sort dialog in the ribbon, or in Google Sheets by using Data -> Sort range in the menu (presumably, OpenOffice Calc and other tools have something similar):

SQL

Comma separated list of column name expressions is supported in the ORDER BY clause at least since SQL:1999 (from what I could see in The History of SQL Standards). And it's supported by all major implementations (even SQLite). You can try the following example on SQL Fiddle:

select *
from myData
order by A, B desc

Python 🐼

Practical (or lazy) people like me use pandas to organize all their Python data that need multi-column sort, just like in this Replit example:

df.sort_values(by=['A', 'B'], ascending=[True, False])

JavaScript / TypeScript

While there might be some nice data libraries in the JavaScript world too, let's imagine we receive a JSON data from some API that is an array of objects like [{A: 1, B: 1, C: 'a'}, ...] and that we don't want to compare all suitable libraries for features, bundle size, and performance. Let's say we want a nice little utility function that accepts some array and SQL-like order criteria, then returns a sorted array, like following:

sorted(jsonLikeData, [{A: 'asc'}, {B: 'desc'}])

Is this even possible? Easy to do? That's what we asked ourselves with Artem while he was implementing PR#103 in mswjs/data.

Here is a simplified version that you can try in TypeScript Playground:

function sorted(data, orderBy) {
  return [...data].sort((a, b) => {
    for (const criteria of orderBy) {
      const [key, value] = Object.entries(criteria)[0]
      const isAsc = value === 'asc'
      if (a[key] > b[key]) {
        return isAsc ? 1 : -1
      }
      if (a[key] < b[key]) {
        return isAsc ? -1 : 1
      }
      // else, if they are equal, continue to next criteria
    }

    // if not returned yet, a and b are equal by all criteria
    return 0
  })
}

If you are not familiar with JavaScript Array.sort, it is an in-place method that takes a function as an argument. It calls your comparison function multiple times, each time with 2 elements from the array. It expects a return value of -1 if the first element should be sorted before the second, 1 if the first element belongs after the second, and 0 if the pre-existing sort order should be preserved (which is needed for a "stable sort"). So we need to iterate over all criteria and return a value as soon as we can determine it... We would need to handle a few more edge cases in practice, but that's all for the core idea how we can approach the multisort implementation in JavaScript.

See you next time, when I will talk about joining data sets in Sheets, SQL, Python and JavaScript.

👋 I am Peter, a.k.a. Aprillion. My strong opinions are held weakly, discussion is open here in the comments or on Twitter.

Issues are not tasks

Peter Hozák — Sun, 06 Jun 2021 14:23:09 +0000

JIRA tickets, GitHub issues, Trello cards, Excel todo lists. Our in-brain memories are not enough to plan and track all the tasks in our software projects; we (programmers, testers, product owners, project managers, ...) collectively decided that we need external tools.

And our tools became so good in collecting ideas that every team on Earth now generates more ideas than they can ever hope to complete. For many of us, we learned to accept this fact of life as an inevitable emergent property in the "machine" for producing complex software (whether or not we hate all of those estimation and prioritization meetings from the core of our souls).

Yet, for many people (especially for programmers motivated by the purpose of a project), ever-growing backlogs are a big source of anxiety and burnout. And the truth is, the more "open" issues there are, the harder it is to organize them. Backlog cleaning sessions and automated tools to close inactive issues are ever more popular.

However, issues are not tasks. They are a mental tool, to help with our memory and to help with our communication. If we treat them as such, we will have a somewhat easier time deciding how to organize our issue tracking tools:

if we split "ideas" from "confirmed issues" to another tracking system, will it improve or hinder our memories?
if we weave our "vision", "goals", "stories", "bugs", and "sub-task" into a big hierarchy of artifacts, will it help or slow down our communication?
if we close a bunch of tickets or just lower the priority, will it make a bigger mess of duplicates or keep us better organized?
what should be the title and metadata, what do we need to remember for search, what should pop up from search results, and do we need some reports?
given that we cannot document everything, what to put into description or comments; what is ephemeral enough for a chat or a coffee break?

The answers will be different in every team, so we can reasonable expect that the tracking tools will be either configurable or niche.

So, if you feel weighted down by your issue tracking system, remember for your next retrospective:

Issues are a tool for our memory and communication. How can we configure them better?

👋 I am Peter, a.k.a. Aprillion. My strong opinions are held weakly, discussion is open here in the comments or on Twitter.

Schrödinger's cat explains TypeScript

Peter Hozák — Sat, 27 Mar 2021 14:16:00 +0000

In 1935, Erwin complained to Albert about a problem in Quantum Mechanics. The bug report is now known as the Schrödinger's cat thought experiment and the fix, to shut up and calculate, is still deployed in production. Many of those calculations involved matrix multiplication of multidimensional vectors and that took a lot of space on blackboards. Thus, Paul established the ⟨bra|ket⟩ notation to help the cool kids make fewer mistakes.

In 2013, library authors complained to Jonathan about a problem in TypeScript. They wanted to make their utilities more reusable, without any compromise. So starting with version 0.9, we can now shut up and <Generics>.

Union cats

Let's imagine writing a library, "Quantum Cats," that checks whether a cat is dead or alive:

type Cat = {status: 'dead'|'alive'}

function checkCat(cat: Cat) {
  return cat.status
}

// inferred type: 'dead'|'alive'
const result = checkCat({status: 'alive'})

TS Playground

And for a while, union types will be all that we need.

Generic cats

However, our users expect no cats to be harmed in the process of checking! There is not just one type of cats that are all "dead or alive," we need to distinguish different types of cats:

type Cat<Status> = {status: Status}

function checkCat<Status extends 'dead'|'alive'>(
  cat: Cat<Status>
) {
  return cat.status
}

// inferred type: 'alive'
const result = checkCat({status: 'alive'})

TS Playground

Reading the code

Our users are now happy, but we have to explain what we did to our future selves after lunch. As a first step, let's try to read the code above. Everyone might read it differently, and that's fine, here is just an example:

we have a type Cat, that is a generic
- it takes a parameter, type Status, which is implicitly unknown
- value of type Cat is an object with one property, status
- type of that property is Status
then, we declare a function checkCat, that has a generic type
- the function itself takes one parameter, cat
- the function type takes one generic parameter, type Status
- Status extends a union of 2 types, both are string literals, namely dead or alive
- the function parameter cat has a type, which is an instance of a generic that we get by invoking Cat with parameter Status inside the angle brackets
  - note: just like in HTML, we write the angle brackets using less-than and greater-than signs <>, not the Unicode characters ⟨⟩
- the function contains 1 return statement
- the return expression is cat dot status
- the return type is inferred
then, we make an assignment to a constant variable result
- the right-hand side consists of a function call
- checkCat is called with one argument, an object literal that has one property, status, whose value is a string, alive
- type of the function is inferred from the argument
- type of the new variable is also inferred

Understanding generics

Now that we can read the code, what to make of it? Let's think of generics as higher order types, a concept similar to higher order functions (when a function returns a function, or if it takes another function as a parameter to call back):

A generic is a type that accepts one or more types as parameters and it returns a different type based on those.

type Cat<Status> = {status: Status}

type CatAlive = Cat<'alive'> // same as {status: 'alive'}
type CatDead  = Cat<'dead'> // same as {status: 'dead'}
type CatUndefined = Cat<undefined> // {status: undefined}

TS Playground

We can restrict what types to allow in the parameters (extends), and we can provide a default type (<... = default>):

type Cat<Status extends 'dead'|'alive' = 'alive'> = {
  status: Status
}

type CatAlive = Cat // {status: 'alive'}
type CatDead = Cat<'dead'> // {status: 'dead'}
type CatDeadOrAlive = Cat<'dead'|'alive'> // CatDead | CatAlive

// not possible because of type error:
type CatUndefined = Cat<undefined>

TS Playground

We can use generics to define function types:

function checkCat<Status extends 'dead'|'alive'>(
  cat: Cat<Status>
): Status {
  return cat.status
}

const checkCat = <Status extends 'dead'|'alive'>(
  cat: Cat<Status>
) => cat.status

TS Playground

Note: <Status>() => {} will not work in *.tsx files, that is ambiguous with a JSX opening tag, but we can use <Status extends unknown>.

We can use generic instances inside other generics:

type Cat<Status> = {status: Status}

function isCatAlive<C extends Cat<string|undefined>>(
  cat: C
): cat is C & Cat<'alive'> {
  return cat.status === 'alive'
}
function feedCat(cat: Cat<'alive'>) {
  return '😻'
}

// inferred type: {status: string}
const cat = {status: 'alive'}

// @ts-expect-error
// Type 'string' is not assignable to type '"alive"'
feedCat(cat)

if (isCatAlive(cat)) {
  feedCat(cat) // ✔
}

TS Playground

And we can mix and match generics with function overloads for our wildly reusable JavaScript utilities, even if we only know how to type some of our use cases, we don't have to be purrfect:

type Cat<Status = string> = {s: Status}

function checkMany<S1>(cats: [Cat<S1>]): [S1]
function checkMany<S1, S2>(cats: [Cat<S1>, Cat<S2>]): [S1, S2]
function checkMany(cats: Cat[]): string[]
function checkMany(cats: Cat[]) {
  return cats.map(({s}) => s)
}

const result1 = checkMany([{s: 'a'}]) // inferred type: [string]
const result2 = checkMany([ // inferred type: ['d', 'a']
  {s: 'd' as const},
  {s: 'a' as const}
])
const result3 = checkMany([ // inferred type: string[] 🤷‍♂️
  {s: 'a' as const},
  {s: 'a' as const},
  {s: 'a' as const}
])

TS Playground

Quantum cats

So a few feature requests later, out code might look like this:

type Cat<Status = 'dead'|'alive'|undefined> =
  Status extends 'dead' ? {status: Status, bury: () => string}
  : Status extends 'alive' ? {status: Status, feed: () => string}
  : {status: Status}

function checkCat<S extends 'dead'|'alive'>(cat: Cat<S>):
  Cat<S>
function checkCat(cat: Cat<undefined>, probability?: number):
  Cat<'dead'|'alive'>
function checkCat(cat: Cat, probability = 0.5) {
  if (cat.status) {
    return cat
  }
  if (Math.random() < probability) {
    return {status: 'dead', bury: () => '😿'}
  }
  return {status: 'alive', feed: () => '😻'}
}

const quantumCat = {status: undefined}

function simulator() {
  const cat = checkCat(quantumCat)
  return cat.status === 'alive' ? cat.feed() : cat.bury()
}
const multipleWorlds = Array.from(Array(100_000)).map(simulator)
const statistics = multipleWorlds.reduce((acc, result) => {
  acc[result] = acc[result] ? acc[result] + 1 : 1
  return acc
}, {} as Record<string, number>)

console.log(statistics)

TS Playground

We introduced some conditional types and type narrowing to be able to call cat.feed() without TypeScript errors about the foolishness of feeding potentially dead cats. Hopefully, we can still read the code.

👋 I am Peter, a.k.a. Aprillion. I considered transcribing the last code example into English sentences, but that would be worth a separate post (or even a youtube video) if enough people are interested. Please let me know in the comments here or on Twitter.