Working with arrays in Google BigQuery

blog
bigquery
arrays
cloud
Author

Phillip Cloud

Published

September 12, 2023

Introduction

Ibis and BigQuery have worked well together for years.

In Ibis 7.0.0, they work even better together with the addition of array functionality for BigQuery.

Let’s look at some examples using BigQuery’s IMDB sample data.

Basics

First we’ll connect to BigQuery and pluck out a table to work with.

We’ll start with from ibis.interactive import * for maximum convenience.

from ibis.interactive import *

con = ibis.connect("bigquery://ibis-gbq")
con.set_database("bigquery-public-data.imdb")
1
from ibis.interactive import * imports Ibis APIs into the global namespace and enables interactive mode.
2
Connect to Google BigQuery. Compute (but not storage) is billed to the project you connect to–ibis-gbq in this case.
3
Set the database to the project and dataset that we will use for analysis.

Let’s look at the tables in this dataset:

con.tables
Tables
------
- name_basics
- reviews
- title_akas
- title_basics
- title_crew
- title_episode
- title_principals
- title_ratings

Let’s pull out the name_basics table, which contains names and metadata about people listed on IMDB. We’ll call this ents (short for entities), and remove some columns we won’t need:

ents = con.tables.name_basics.drop("birth_year", "death_year")
ents
┏━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ nconst    ┃ primary_name         ┃ primary_profession ┃ known_for_titles              ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string    │ string               │ string             │ string                        │
├───────────┼──────────────────────┼────────────────────┼───────────────────────────────┤
│ nm7138056 │ Rick Rosenthal       │ NULL               │ NULL                          │
│ nm7139577 │ Swearwolf            │ NULL               │ NULL                          │
│ nm7142352 │ Sue Alcock           │ NULL               │ tt0106077,tt2882076,tt0206501 │
│ nm7148481 │ Fayez Sayegh         │ NULL               │ tt0334858,tt0257303           │
│ nm7152942 │ Tyler Trevyon Wilson │ NULL               │ NULL                          │
│ nm7155448 │ Kieran Statham       │ NULL               │ NULL                          │
│ nm7180904 │ Ivan Houston         │ NULL               │ tt4513850,tt6479150           │
│ nm7205405 │ Beryl Heryet         │ NULL               │ tt0108961                     │
│ nm7205905 │ Catherine A. Hughes  │ NULL               │ NULL                          │
│ nm7224551 │ Rick Young           │ NULL               │ NULL                          │
│ …         │ …                    │ …                  │ …                             │
└───────────┴──────────────────────┴────────────────────┴───────────────────────────────┘

Splitting strings into arrays

We can see that known_for_titles looks sort of like an array, so let’s call the split method on that column and replace the existing column:

ents = ents.mutate(known_for_titles=_.known_for_titles.split(","))
ents
┏━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┓
┃ nconst    ┃ primary_name     ┃ primary_profession ┃ known_for_titles ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━┩
│ string    │ string           │ string             │ array<string>    │
├───────────┼──────────────────┼────────────────────┼──────────────────┤
│ nm7136100 │ Aaisha Nance     │ NULL               │ []               │
│ nm7154336 │ Eduardo Martínez │ NULL               │ ['tt3916326']    │
│ nm7159199 │ Russell Michael  │ NULL               │ []               │
│ nm7164837 │ Chris Hayden     │ NULL               │ []               │
│ nm7173311 │ Brenda Richmond  │ NULL               │ []               │
│ nm7184223 │ Lise Hogstad     │ NULL               │ ['tt4517204']    │
│ nm7188457 │ Jake Hirsch      │ NULL               │ []               │
│ nm7201641 │ Zvi Hillman      │ NULL               │ ['tt3800526']    │
│ nm7201872 │ Eleanor Glissan  │ NULL               │ []               │
│ nm7202400 │ Don Wolfe        │ NULL               │ ['tt0350393']    │
│ …         │ …                │ …                  │ …                │
└───────────┴──────────────────┴────────────────────┴──────────────────┘

Similarly for primary_profession, since people involved in show business often have more than one responsibility on a project:

ents = ents.mutate(primary_profession=_.primary_profession.split(","))

Array length

Let’s see how many titles each entity is known for, and then show the five people with the largest number of titles they’re known for:

This is computed using the length API on array expressions:

(
    ents.select("primary_name", num_titles=_.known_for_titles.length())
    .order_by(_.num_titles.desc())
    .limit(5)
)
┏━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━┓
┃ primary_name       ┃ num_titles ┃
┡━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━┩
│ string             │ int64      │
├────────────────────┼────────────┤
│ Paul Winter        │          6 │
│ Chris Estrada      │          6 │
│ Jonathon Saunders  │          5 │
│ Jean-Marie Dupriez │          5 │
│ Henry Townsend     │          5 │
└────────────────────┴────────────┘

It seems like the length of the known_for_titles might be capped at five!

Index

We can see the position of "actor" in primary_professions:

ents.primary_profession.index("actor")
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ ArrayPosition(primary_profession, 'actor') ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ int64                                      │
├────────────────────────────────────────────┤
│                                         -1 │
│                                         -1 │
│                                         -1 │
│                                         -1 │
│                                         -1 │
│                                         -1 │
│                                         -1 │
│                                         -1 │
│                                         -1 │
│                                         -1 │
│                                          … │
└────────────────────────────────────────────┘

A return value of -1 indicates that "actor" is not present in the value:

Let’s look for entities that are not primarily actors:

We can do this using the index method by checking whether the position of the string "actor" is greater than zero:

actor_index = ents.primary_profession.index("actor")
not_primarily_actors = actor_index > 0
not_primarily_actors.mean()
1
The average of a bool column gives the percentage of True values 








0.019416901585524518








Who are they?




ents[not_primarily_actors]




┏━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ nconst     ┃ primary_name      ┃ primary_profession                ┃ known_for_titles                     ┃
┡━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string     │ string            │ array<string>                     │ array<string>                        │
├────────────┼───────────────────┼───────────────────────────────────┼──────────────────────────────────────┤
│ nm4130226  │ Brian Castleforte │ ['animation_department', 'actor'] │ ['tt6565702', 'tt0379786', ... +2]   │
│ nm8393678  │ Vincent Bonduau   │ ['animation_department', 'actor'] │ ['tt1807824']                        │
│ nm14260828 │ Brian Nurken      │ ['animation_department', 'actor'] │ ['tt23752664', 'tt23772766']         │
│ nm4683360  │ Youngmin Yoon     │ ['animation_department', 'actor'] │ ['tt2063592', 'tt0206512']           │
│ nm3549362  │ Rundeep Khaira    │ ['animation_department', 'actor'] │ ['tt8801880', 'tt10468754', ... +2]  │
│ nm4797828  │ Sandria Dajani    │ ['animation_department', 'actor'] │ ['tt13614758', 'tt11714502', ... +1] │
│ nm9568757  │ Katsuki Fukuda    │ ['animation_department', 'actor'] │ ['tt9179096', 'tt9203780', ... +1]   │
│ nm14460434 │ Akuago220         │ ['animation_department', 'actor'] │ ['tt26241684', 'tt26241620', ... +1] │
│ nm3707489  │ Sejin Lee         │ ['animation_department', 'actor'] │ ['tt1554501', 'tt4612828', ... +1]   │
│ nm5427775  │ Man Yee Wong      │ ['animation_department', 'actor'] │ ['tt6726818', 'tt2355236', ... +1]   │
│ …          │ …                 │ …                                 │ …                                    │
└────────────┴───────────────────┴───────────────────────────────────┴──────────────────────────────────────┘







It’s not 100% clear whether the order of elements in primary_profession matters here.






Containment


We can get people who are not actors using contains:




non_actors = ents[~ents.primary_profession.contains("actor")]
non_actors




┏━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ nconst    ┃ primary_name               ┃ primary_profession ┃ known_for_titles                     ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string    │ string                     │ array<string>      │ array<string>                        │
├───────────┼────────────────────────────┼────────────────────┼──────────────────────────────────────┤
│ nm7150442 │ Amelia Fay Robinson        │ ['actress']        │ ['tt13481164', 'tt13465802', ... +2] │
│ nm7165224 │ Isabel Gregory             │ ['actress']        │ ['tt4443854', 'tt4496070']           │
│ nm7178372 │ Stephanie Antia            │ ['actress']        │ ['tt4511160']                        │
│ nm7185484 │ Roberta Fjelsted Rasmussen │ ['actress']        │ ['tt1973692']                        │
│ nm7207148 │ Vinti Idnani               │ ['actress']        │ ['tt4193556', 'tt1949650', ... +2]   │
│ nm7226395 │ Ezgi Geles                 │ ['actress']        │ ['tt4562100']                        │
│ nm7240877 │ Natalie Mitchell           │ ['actress']        │ ['tt4579596']                        │
│ nm7242240 │ Lea Monique Chavez         │ ['actress']        │ ['tt5862516', 'tt4580910']           │
│ nm7244117 │ Claire Luger               │ ['actress']        │ ['tt2287817']                        │
│ nm7270151 │ Kitty                      │ ['actress']        │ ['tt4461794']                        │
│ …         │ …                          │ …                  │ …                                    │
└───────────┴────────────────────────────┴────────────────────┴──────────────────────────────────────┘











Element removal


We can remove elements from arrays too.












Noteremove() does not mutate the underlying data












Let’s see who only has “actor” in the list of their primary professions:




ents.filter(
    [
        _.primary_profession.length() > 0,
        _.primary_profession.remove("actor").length() == 0,
    ]
)




┏━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ nconst    ┃ primary_name     ┃ primary_profession ┃ known_for_titles                   ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string    │ string           │ array<string>      │ array<string>                      │
├───────────┼──────────────────┼────────────────────┼────────────────────────────────────┤
│ nm7149861 │ Max Arnoud       │ ['actor']          │ ['tt3385542']                      │
│ nm7165794 │ Arnaud Charrin   │ ['actor']          │ ['tt6441072', 'tt9120416', ... +2] │
│ nm7166580 │ Dragoslav Curcin │ ['actor']          │ ['tt4497928']                      │
│ nm7172699 │ Guillaume Ferrer │ ['actor']          │ ['tt4505204']                      │
│ nm7181207 │ Lino Vitale      │ ['actor']          │ ['tt2396200']                      │
│ nm7183738 │ Taia Handlin     │ ['actor']          │ ['tt4516616']                      │
│ nm7189398 │ Attila Gal       │ ['actor']          │ ['tt3414510', 'tt2442080']         │
│ nm7202327 │ Leonardo Tadeu   │ ['actor']          │ ['tt4536560']                      │
│ nm7213972 │ Michael Davis    │ ['actor']          │ ['tt4547714']                      │
│ nm7215705 │ Darryl Daniels   │ ['actor']          │ ['tt4549616']                      │
│ …         │ …                │ …                  │ …                                  │
└───────────┴──────────────────┴────────────────────┴────────────────────────────────────┘











Slicing with square-bracket syntax


Let’s remove everyone’s first profession from the list, but only if they have more than one profession listed:




ents[_.primary_profession.length() > 1].mutate(
    primary_profession=_.primary_profession[1:],
)




┏━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ nconst     ┃ primary_name     ┃ primary_profession       ┃ known_for_titles                   ┃
┡━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string     │ string           │ array<string>            │ array<string>                      │
├────────────┼──────────────────┼──────────────────────────┼────────────────────────────────────┤
│ nm4461143  │ Sima Gorjestani  │ ['actress']              │ ['tt10570018', 'tt1933537']        │
│ nm3178635  │ Bing Lei Li      │ ['animation_department'] │ ['tt1527823', 'tt1311062', ... +2] │
│ nm11878910 │ Wang Yu          │ ['animation_department'] │ ['tt17067176', 'tt13051266']       │
│ nm0878047  │ Jack Turpin      │ ['animation_department'] │ ['tt5377482', 'tt0249683']         │
│ nm9844243  │ Alaa Alamri      │ ['animation_department'] │ ['tt8426318', 'tt12326056']        │
│ nm3878112  │ Rodney Baker     │ ['animation_department'] │ ['tt1638935', 'tt1667135', ... +1] │
│ nm10066507 │ Matthew Creed    │ ['animation_department'] │ ['tt8881470', 'tt26342009']        │
│ nm4429144  │ Cisko Vandeverre │ ['animation_department'] │ ['tt3104592', 'tt1908523']         │
│ nm2024311  │ Oliver Morales   │ ['animation_department'] │ ['tt0477716']                      │
│ nm10216453 │ Alister Fell     │ ['animation_department'] │ ['tt9177850']                      │
│ …          │ …                │ …                        │ …                                  │
└────────────┴──────────────────┴──────────────────────────┴────────────────────────────────────┘













Set operations and sorting


Treating arrays as sets is possible with the union and intersect APIs.


Let’s take a look at intersect:




Intersection


Let’s see if we can use array intersection to figure which actors share known-for titles and sort the result:




left = ents.filter(_.known_for_titles.length() > 0).limit(10_000)
right = left.view()
shared_titles = (
    left
    .join(right, left.nconst != right.nconst)
    .select(
        s.startswith("known_for_titles"),
        left_name="primary_name",
        right_name="primary_name_right",
    )
    .filter(_.known_for_titles.intersect(_.known_for_titles_right).length() > 0)
    .group_by(name="left_name")
    .agg(together_with=_.right_name.collect())
    .mutate(together_with=_.together_with.unique().sort())
)
shared_titles




┏━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ name                ┃ together_with                                    ┃
┡━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string              │ array<string>                                    │
├─────────────────────┼──────────────────────────────────────────────────┤
│ Juan Manuel Urtubey │ ['Adrián Ventura', 'Carlos Gallardo', ... +6]    │
│ Too Too             │ ['Hainan Liu', 'Zhao Xianfei']                   │
│ Elizabeth Daughtry  │ ['Angela DiPentima', 'Anthony Coard', ... +12]   │
│ Marc                │ ['Ava', 'Clint Sanner', ... +6]                  │
│ Geoffrey Devereux   │ ['Rex Chaston']                                  │
│ Philippe Gandilhon  │ ['Claude Leicher', 'Fabrice Leste', ... +8]      │
│ Pete Freeman        │ ['Monica Iken', 'Roberta Prisco']                │
│ Vivian Murray       │ ['Alice Seifullan', 'Andre Lowe', ... +12]       │
│ Patrick Bahnken     │ ['Adele Springsteen', 'Alexander Sich', ... +73] │
│ Robin Eames         │ ['Adele Springsteen', 'Alexander Sich', ... +73] │
│ …                   │ …                                                │
└─────────────────────┴──────────────────────────────────────────────────┘













Advanced operations




Flatten arrays into rows


Thanks to the tireless efforts of the folks working on sqlglot, as of version 7.0.0 Ibis supports unnest for BigQuery!


You can use it standalone on a column expression:




ents.primary_profession.unnest()




┏━━━━━━━━━━━━━━━━━━━━┓
┃ primary_profession ┃
┡━━━━━━━━━━━━━━━━━━━━┩
│ string             │
├────────────────────┤
│ actor              │
│ actor              │
│ actor              │
│ actor              │
│ actor              │
│ actor              │
│ actor              │
│ actor              │
│ actor              │
│ actor              │
│ …                  │
└────────────────────┘







You can also use it in select/mutate calls to expand the table accordingly:




ents.mutate(primary_profession=_.primary_profession.unnest())




┏━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ nconst    ┃ primary_name     ┃ primary_profession ┃ known_for_titles                   ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string    │ string           │ string             │ array<string>                      │
├───────────┼──────────────────┼────────────────────┼────────────────────────────────────┤
│ nm7149861 │ Max Arnoud       │ actor              │ ['tt3385542']                      │
│ nm7165794 │ Arnaud Charrin   │ actor              │ ['tt6441072', 'tt9120416', ... +2] │
│ nm7166580 │ Dragoslav Curcin │ actor              │ ['tt4497928']                      │
│ nm7172699 │ Guillaume Ferrer │ actor              │ ['tt4505204']                      │
│ nm7181207 │ Lino Vitale      │ actor              │ ['tt2396200']                      │
│ nm7183738 │ Taia Handlin     │ actor              │ ['tt4516616']                      │
│ nm7189398 │ Attila Gal       │ actor              │ ['tt3414510', 'tt2442080']         │
│ nm7202327 │ Leonardo Tadeu   │ actor              │ ['tt4536560']                      │
│ nm7213972 │ Michael Davis    │ actor              │ ['tt4547714']                      │
│ nm7215705 │ Darryl Daniels   │ actor              │ ['tt4549616']                      │
│ …         │ …                │ …                  │ …                                  │
└───────────┴──────────────────┴────────────────────┴────────────────────────────────────┘







Unnesting can be useful when joining nested data.


Here we use unnest to find people known for any of the godfather movies:




basics = con.tables.title_basics.filter(
    [
        _.title_type == "movie",
        _.original_title.lower().startswith("the godfather"),
        _.genres.lower().contains("crime"),
    ]
)

known_for_the_godfather = (
    ents.mutate(tconst=_.known_for_titles.unnest())
    .join(basics, "tconst")
    .select("primary_title", "primary_name")
    .distinct()
    .order_by(["primary_title", "primary_name"])
)
known_for_the_godfather






1



Filter the title_basics data set to only the Godfather movies



2



Unnest the known_for_titles array column



3



Join with basics to get movie titles



4



Ensure that each entity is only listed once and sort the results









┏━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┓
┃ primary_title ┃ primary_name        ┃
┡━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━┩
│ string        │ string              │
├───────────────┼─────────────────────┤
│ The Godfather │ A. Emmett Adams     │
│ The Godfather │ Abe Vigoda          │
│ The Godfather │ Al Lettieri         │
│ The Godfather │ Al Martino          │
│ The Godfather │ Al Pacino           │
│ The Godfather │ Albert S. Ruddy     │
│ The Godfather │ Alex Rocco          │
│ The Godfather │ Andrea Eastman      │
│ The Godfather │ Angelo Infanti      │
│ The Godfather │ Anna Hill Johnstone │
│ …             │ …                   │
└───────────────┴─────────────────────┘







Let’s summarize by showing how many people are known for each Godfather movie:




known_for_the_godfather.primary_title.value_counts()




┏━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┓
┃ primary_title          ┃ primary_title_count ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━┩
│ string                 │ int64               │
├────────────────────────┼─────────────────────┤
│ The Godfather Part II  │                 113 │
│ The Godfather Part III │                 199 │
│ The Godfather          │                  97 │
└────────────────────────┴─────────────────────┘











Filtering array elements


Filtering array elements can be done with the filter method, which applies a predicate to each array element and returns an array of elements for which the predicate returns True.


This method is similar to Python’s filter function.


Let’s show all people who are neither editors nor actors:




ents.mutate(
    primary_profession=_.primary_profession.filter(
        lambda pp: ~pp.isin(("actor", "editor"))
    )
).filter(_.primary_profession.length() > 0)






1



This filter call is applied to each array element



2



This filter call is applied to the table









┏━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ nconst     ┃ primary_name          ┃ primary_profession       ┃ known_for_titles                    ┃
┡━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string     │ string                │ array<string>            │ array<string>                       │
├────────────┼───────────────────────┼──────────────────────────┼─────────────────────────────────────┤
│ nm8193687  │ Mandl Cho             │ ['animation_department'] │ ['tt5782050', 'tt5792070']          │
│ nm1894548  │ Frederic Cabral       │ ['animation_department'] │ ['tt0414853', 'tt0265223']          │
│ nm5810804  │ Yukio Narita          │ ['animation_department'] │ ['tt1375678', 'tt8339440']          │
│ nm6194234  │ Noc                   │ ['animation_department'] │ ['tt0084904', 'tt4916874', ... +1]  │
│ nm11022011 │ Shea Lawrence         │ ['animation_department'] │ ['tt0297607', 'tt10954022', ... +1] │
│ nm11160130 │ Tim Steeman           │ ['animation_department'] │ ['tt11158166', 'tt21401568']        │
│ nm0547493  │ Philippe Marin        │ ['animation_department'] │ ['tt0071653', 'tt0367288', ... +1]  │
│ nm13967963 │ Christopher O'Connell │ ['animation_department'] │ ['tt6283598']                       │
│ nm5216032  │ Jorge Cereijo         │ ['animation_department'] │ ['tt2304694', 'tt3010976']          │
│ nm6381330  │ William Harris        │ ['animation_department'] │ ['tt3916088', 'tt3621408', ... +1]  │
│ …          │ …                     │ …                        │ …                                   │
└────────────┴───────────────────────┴──────────────────────────┴─────────────────────────────────────┘











Applying a function to array elements


You can apply a function to run an ibis expression on each element of an array using the map method.


Let’s normalize the case of primary_profession to upper case:




ents.mutate(
    primary_profession=_.primary_profession.map(lambda pp: pp.upper())
).filter(_.primary_profession.length() > 0)




┏━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ nconst    ┃ primary_name       ┃ primary_profession ┃ known_for_titles                   ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string    │ string             │ array<string>      │ array<string>                      │
├───────────┼────────────────────┼────────────────────┼────────────────────────────────────┤
│ nm7145114 │ Andrés León        │ ['ACTOR']          │ ['tt1831897', 'tt0315638', ... +2] │
│ nm7146648 │ Eric Hunter        │ ['ACTOR']          │ ['tt4473584']                      │
│ nm7162932 │ Karsten Wolf       │ ['ACTOR']          │ ['tt4493476']                      │
│ nm7189102 │ Nagibe Chader      │ ['ACTOR']          │ ['tt5487218', 'tt4513020']         │
│ nm7213059 │ Alberto Tofoletti  │ ['ACTOR']          │ ['tt2333598']                      │
│ nm7213261 │ Gianni Rojas       │ ['ACTOR']          │ ['tt2333598']                      │
│ nm7219361 │ Danny Mendes       │ ['ACTOR']          │ ['tt4553846']                      │
│ nm7232029 │ Sabin Rich         │ ['ACTOR']          │ ['tt4568740']                      │
│ nm7233638 │ Jorge José do Lana │ ['ACTOR']          │ ['tt1999221']                      │
│ nm7236648 │ Joe Mulgrew        │ ['ACTOR']          │ ['tt2318795']                      │
│ …         │ …                  │ …                  │ …                                  │
└───────────┴────────────────────┴────────────────────┴────────────────────────────────────┘













Conclusion


Ibis has a sizable collection of array APIs that work with many different backends and as of version 7.0.0, Ibis supports a much larger set of those APIs for BigQuery!


Check out the API documentation for the full set of available methods.


Try it out, and let us know what you think.






 Back to top