Basic operations

In this tutorial, we will perform basic operations on demo data in Starburst Galaxy.

Prerequisites

This tutorial assumes you have completed the setup and connected to a database with the astronauts and missions demo data, including setup of a Python environment with Ibis and the Trino backend installed.

Code 
import os
import ibis
from dotenv import load_dotenv

ibis.options.interactive = True

load_dotenv()

user = os.getenv("USERNAME")
password = os.getenv("PASSWORD")
host = os.getenv("HOSTNAME")
port = os.getenv("PORTNUMBER")
catalog = "sample"
schema = "demo"

con = ibis.trino.connect(
    user=user, password=password, host=host, port=port, database=catalog, schema=schema
)
con
1
Import necessary libraries.
2
Use Ibis in interactive mode.
3
Load environment variables.
4
Load secrets from environment variables.
5
Use the sample demo data.
6
Connect to Starburst Galaxy.
7
Display the connection object. 
<ibis.backends.trino.Backend at 0x15508ecd0>

Load tables

Once you have a connection, you can assign tables to variables.

astronauts = con.table("astronauts")
missions = con.table("missions")
1
Create astonauts variable.
2
Create missions variable.

You can display slices of data:

astronauts[0:5]
1
Display the first 5 rows of the astronauts table. 
┏━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ id    ┃ number ┃ nationwide_number ┃ name                ┃ original_name           ┃ sex    ┃ year_of_birth ┃ nationality    ┃ military_civilian ┃ selection              ┃ year_of_selection ┃ mission_number ┃ total_number_of_missions ┃ occupation ┃ year_of_mission ┃ mission_title   ┃ ascend_shuttle  ┃ in_orbit        ┃ descend_shuttle ┃ hours_mission ┃ total_hrs_sum ┃ field21 ┃ eva_hrs_mission ┃ total_eva_hrs ┃
┡━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ int32 │ int32  │ int32             │ string              │ string                  │ string │ int32         │ string         │ string            │ string                 │ int32             │ int32          │ int32                    │ string     │ int32           │ string          │ string          │ string          │ string          │ float64       │ float64       │ int32   │ float64         │ float64       │
├───────┼────────┼───────────────────┼─────────────────────┼─────────────────────────┼────────┼───────────────┼────────────────┼───────────────────┼────────────────────────┼───────────────────┼────────────────┼──────────────────────────┼────────────┼─────────────────┼─────────────────┼─────────────────┼─────────────────┼─────────────────┼───────────────┼───────────────┼─────────┼─────────────────┼───────────────┤
│     1 │      1 │                 1 │ Gagarin, Yuri       │ ГАГАРИН Юрий Алексеевич │ male   │          1934 │ U.S.S.R/Russia │ military          │ TsPK-1                 │              1960 │              1 │                        1 │ pilot      │            1961 │ Vostok 1        │ Vostok 1        │ Vostok 2        │ Vostok 3        │          1.77 │          1.77 │       0 │             0.0 │           0.0 │
│     2 │      2 │                 2 │ Titov, Gherman      │ ТИТОВ Герман Степанович │ male   │          1935 │ U.S.S.R/Russia │ military          │ TsPK-1                 │              1960 │              1 │                        1 │ pilot      │            1961 │ Vostok 2        │ Vostok 2        │ Vostok 2        │ Vostok 2        │         25.00 │         25.30 │       0 │             0.0 │           0.0 │
│     3 │      3 │                 1 │ Glenn, John H., Jr. │ Glenn, John H., Jr.     │ male   │          1921 │ U.S.           │ military          │ NASA Astronaut Group 1 │              1959 │              1 │                        2 │ pilot      │            1962 │ MA-6            │ MA-6            │ MA-6            │ MA-6            │          5.00 │        218.00 │       0 │             0.0 │           0.0 │
│     4 │      3 │                 1 │ Glenn, John H., Jr. │ Glenn, John H., Jr.     │ male   │          1921 │ U.S.           │ military          │ NASA Astronaut Group 2 │              1959 │              2 │                        2 │ PSP        │            1998 │ STS-95          │ STS-95          │ STS-95          │ STS-95          │        213.00 │        218.00 │       0 │             0.0 │           0.0 │
│     5 │      4 │                 2 │ Carpenter, M. Scott │ Carpenter, M. Scott     │ male   │          1925 │ U.S.           │ military          │ NASA- 1                │              1959 │              1 │                        1 │ Pilot      │            1962 │ Mercury-Atlas 7 │ Mercury-Atlas 7 │ Mercury-Atlas 7 │ Mercury-Atlas 7 │          5.00 │          5.00 │       0 │             0.0 │           0.0 │
└───────┴────────┴───────────────────┴─────────────────────┴─────────────────────────┴────────┴───────────────┴────────────────┴───────────────────┴────────────────────────┴───────────────────┴────────────────┴──────────────────────────┴────────────┴─────────────────┴─────────────────┴─────────────────┴─────────────────┴─────────────────┴───────────────┴───────────────┴─────────┴─────────────────┴───────────────┘

missions[0:5]
1
Display the first 5 rows of the missions table. 
┏━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃ id    ┃ company_name ┃ location                                                  ┃ date                       ┃ detail                                       ┃ status_rocket ┃ cost    ┃ status_mission ┃
┡━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ int32 │ string       │ string                                                    │ string                     │ string                                       │ string        │ float64 │ string         │
├───────┼──────────────┼───────────────────────────────────────────────────────────┼────────────────────────────┼──────────────────────────────────────────────┼───────────────┼─────────┼────────────────┤
│     0 │ SpaceX       │ LC-39A, Kennedy Space Center, Florida, USA                │ Fri Aug 07, 2020 05:12 UTC │ Falcon 9 Block 5 | Starlink V1 L9 & BlackSky │ StatusActive  │   50.00 │ Success        │
│     1 │ CASC         │ Site 9401 (SLS-2), Jiuquan Satellite Launch Center, China │ Thu Aug 06, 2020 04:01 UTC │ Long March 2D | Gaofen-9 04 & Q-SAT          │ StatusActive  │   29.75 │ Success        │
│     2 │ SpaceX       │ Pad A, Boca Chica, Texas, USA                             │ Tue Aug 04, 2020 23:57 UTC │ Starship Prototype | 150 Meter Hop           │ StatusActive  │     nan │ Success        │
│     3 │ Roscosmos    │ Site 200/39, Baikonur Cosmodrome, Kazakhstan              │ Thu Jul 30, 2020 21:25 UTC │ Proton-M/Briz-M | Ekspress-80 & Ekspress-103 │ StatusActive  │   65.00 │ Success        │
│     4 │ ULA          │ SLC-41, Cape Canaveral AFS, Florida, USA                  │ Thu Jul 30, 2020 11:50 UTC │ Atlas V 541 | Perseverance                   │ StatusActive  │  145.00 │ Success        │
└───────┴──────────────┴───────────────────────────────────────────────────────────┴────────────────────────────┴──────────────────────────────────────────────┴───────────────┴─────────┴────────────────┘

Table schemas

You can view the schemas of the tables:

astronauts.schema()
1
Display the schema of the astronauts table. 
ibis.Schema {
  id                        int32
  number                    int32
  nationwide_number         int32
  name                      string
  original_name             string
  sex                       string
  year_of_birth             int32
  nationality               string
  military_civilian         string
  selection                 string
  year_of_selection         int32
  mission_number            int32
  total_number_of_missions  int32
  occupation                string
  year_of_mission           int32
  mission_title             string
  ascend_shuttle            string
  in_orbit                  string
  descend_shuttle           string
  hours_mission             float64
  total_hrs_sum             float64
  field21                   int32
  eva_hrs_mission           float64
  total_eva_hrs             float64
}
missions.schema()
1
Display the schema of the missions table. 
ibis.Schema {
  id              int32
  company_name    string
  location        string
  date            string
  detail          string
  status_rocket   string
  cost            float64
  status_mission  string
}

Selecting columns

With Ibis, you can run SQL-like queries on your tables. For example, you can select specific columns from a table:

t = astronauts.select("name", "nationality", "mission_title", "mission_number", "hours_mission")
t.head(3)
1
Select specific columns from the astronauts table.
2
Display the results. 
┏━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ name                ┃ nationality    ┃ mission_title ┃ mission_number ┃ hours_mission ┃
┡━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ string              │ string         │ string        │ int32          │ float64       │
├─────────────────────┼────────────────┼───────────────┼────────────────┼───────────────┤
│ Gagarin, Yuri       │ U.S.S.R/Russia │ Vostok 1      │              1 │          1.77 │
│ Titov, Gherman      │ U.S.S.R/Russia │ Vostok 2      │              1 │         25.00 │
│ Glenn, John H., Jr. │ U.S.           │ MA-6          │              1 │          5.00 │
└─────────────────────┴────────────────┴───────────────┴────────────────┴───────────────┘

And from the missions table:

t = missions.select("company_name", "status_rocket", "cost", "status_mission")
t.head(3)
1
Select specific columns from the missions table.
2
Display the results. 
┏━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃ company_name ┃ status_rocket ┃ cost    ┃ status_mission ┃
┡━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ string       │ string        │ float64 │ string         │
├──────────────┼───────────────┼─────────┼────────────────┤
│ SpaceX       │ StatusActive  │   50.00 │ Success        │
│ CASC         │ StatusActive  │   29.75 │ Success        │
│ SpaceX       │ StatusActive  │     nan │ Success        │
└──────────────┴───────────────┴─────────┴────────────────┘

You can also apply filters to your queries:

t = astronauts.filter(~astronauts["nationality"].like("U.S.%"))
t.head(3)
1
Filter astronauts table by nationality.
2
Display the results. 
┏━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ id    ┃ number ┃ nationwide_number ┃ name                      ┃ original_name            ┃ sex    ┃ year_of_birth ┃ nationality ┃ military_civilian ┃ selection              ┃ year_of_selection ┃ mission_number ┃ total_number_of_missions ┃ occupation      ┃ year_of_mission ┃ mission_title ┃ ascend_shuttle ┃ in_orbit ┃ descend_shuttle ┃ hours_mission ┃ total_hrs_sum ┃ field21 ┃ eva_hrs_mission ┃ total_eva_hrs ┃
┡━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ int32 │ int32  │ int32             │ string                    │ string                   │ string │ int32         │ string      │ string            │ string                 │ int32             │ int32          │ int32                    │ string          │ int32           │ string        │ string         │ string   │ string          │ float64       │ float64       │ int32   │ float64         │ float64       │
├───────┼────────┼───────────────────┼───────────────────────────┼──────────────────────────┼────────┼───────────────┼─────────────┼───────────────────┼────────────────────────┼───────────────────┼────────────────┼──────────────────────────┼─────────────────┼─────────────────┼───────────────┼────────────────┼──────────┼─────────────────┼───────────────┼───────────────┼─────────┼─────────────────┼───────────────┤
│    68 │    101 │                 1 │ Jugderdemidiin Gurragchaa │ Жүгдэрдэмидийн Гүррагчаа │ male   │          1947 │ Mongolia    │ civilian          │ 1978 Intercosmos Group │              1978 │              1 │                        1 │ flight engineer │            1981 │ Soyuz 39      │ Soyuz 39       │ Soyuz 39 │ Soyuz 39        │         188.7 │        188.70 │       0 │             0.0 │          0.00 │
│    73 │    103 │                 1 │ Dumitru Prunariu          │ Dumitru-Dorin Prunariu   │ male   │          1952 │ Romania     │ civilian          │ 1978 Intercosmos Group │              1978 │              1 │                        1 │ MSP             │            1981 │ Soyuz 40      │ Soyuz 40       │ Soyuz 40 │ Soyuz 40        │         188.7 │        188.70 │       0 │             0.0 │          0.00 │
│    81 │    108 │                 1 │ Chrétien, Jean-Loup       │ Chrétien, Jean-Loup      │ male   │          1938 │ France      │ military          │ CNES-1                 │              1980 │              1 │                        3 │ MSP             │            1982 │ Salyut 7      │ Soyuz T-6      │ Salyut 7 │ Soyuz T-6       │         190.0 │       1043.32 │       0 │             0.0 │          5.95 │
└───────┴────────┴───────────────────┴───────────────────────────┴──────────────────────────┴────────┴───────────────┴─────────────┴───────────────────┴────────────────────────┴───────────────────┴────────────────┴──────────────────────────┴─────────────────┴─────────────────┴───────────────┴────────────────┴──────────┴─────────────────┴───────────────┴───────────────┴─────────┴─────────────────┴───────────────┘

And in the missions table:

t = missions.filter(missions["status_mission"] == "Failure")
t.head(3)
1
Filter missions table by mission status.
2
Display the results. 
┏━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃ id    ┃ company_name ┃ location                                                ┃ date                       ┃ detail                                      ┃ status_rocket ┃ cost    ┃ status_mission ┃
┡━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ int32 │ string       │ string                                                  │ string                     │ string                                      │ string        │ float64 │ string         │
├───────┼──────────────┼─────────────────────────────────────────────────────────┼────────────────────────────┼─────────────────────────────────────────────┼───────────────┼─────────┼────────────────┤
│    11 │ ExPace       │ Site 95, Jiuquan Satellite Launch Center, China         │ Fri Jul 10, 2020 04:17 UTC │ Kuaizhou 11 | Jilin-1 02E, CentiSpace-1 S2  │ StatusActive  │    28.3 │ Failure        │
│    15 │ Rocket Lab   │ Rocket Lab LC-1A, M?\x81hia Peninsula, New Zealand      │ Sat Jul 04, 2020 21:19 UTC │ Electron/Curie | Pics Or It Didn??¦t Happen │ StatusActive  │     7.5 │ Failure        │
│    27 │ Virgin Orbit │ Cosmic Girl, Mojave Air and Space Port, California, USA │ Mon May 25, 2020 19:50 UTC │ LauncherOne | Demo Flight                   │ StatusActive  │    12.0 │ Failure        │
└───────┴──────────────┴─────────────────────────────────────────────────────────┴────────────────────────────┴─────────────────────────────────────────────┴───────────────┴─────────┴────────────────┘

Mutating columns

t = missions.mutate(date=ibis.coalesce(ibis._["date"], None))
t = t.order_by(t["date"].asc())
t.head(3)
1
Mutate the date column.
2
Order the results by the date column.
3
Display the results. 
┏━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃ id    ┃ company_name ┃ location                                          ┃ date                       ┃ detail                       ┃ status_rocket ┃ cost    ┃ status_mission ┃
┡━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ int32 │ string       │ string                                            │ string                     │ string                       │ string        │ float64 │ string         │
├───────┼──────────────┼───────────────────────────────────────────────────┼────────────────────────────┼──────────────────────────────┼───────────────┼─────────┼────────────────┤
│  4268 │ US Air Force │ SLC-17A, Cape Canaveral AFS, Florida, USA         │ Fri Apr 01, 1960 11:40 UTC │ Thor DM-18 Able-II | TIROS-1 │ StatusRetired │     nan │ Success        │
│  3366 │ RVSN USSR    │ Site 43/3, Plesetsk Cosmodrome, Russia            │ Fri Apr 02, 1971 08:20 UTC │ Voskhod | Cosmos 403         │ StatusRetired │     nan │ Success        │
│  1262 │ Arianespace  │ ELA-2, Guiana Space Centre, French Guiana, France │ Fri Apr 02, 1999 22:03 UTC │ Ariane 42P | Insat-2E        │ StatusRetired │     nan │ Success        │
└───────┴──────────────┴───────────────────────────────────────────────────┴────────────────────────────┴──────────────────────────────┴───────────────┴─────────┴────────────────┘

Aggregating and grouping results

Ibis also supports aggregate functions and grouping. For example, you can count the number of rows in a table and group the results by a specific column:

t = astronauts.filter(~astronauts["nationality"].like("U.S.%")).agg(
    [
        ibis._.count().name("number_trips"),
        ibis._["hours_mission"].max().name("longest_time"),
        ibis._["hours_mission"].min().name("shortest_time"),
    ]
)
t.head(3)
1
Filter the astronauts table.
2
Aggregate the results.
3
Display the results. 
┏━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ number_trips ┃ longest_time ┃ shortest_time ┃
┡━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ int64        │ float64      │ float64       │
├──────────────┼──────────────┼───────────────┤
│          149 │      6902.35 │          21.0 │
└──────────────┴──────────────┴───────────────┘

You can add a group by:

t = (
    astronauts.filter(~astronauts["nationality"].like("U.S.%"))
    .group_by("nationality")
    .agg(
        [
            ibis._.count().name("number_trips"),
            ibis._["hours_mission"].max().name("longest_time"),
            ibis._["hours_mission"].min().name("shortest_time"),
        ]
    )
)
t.head(3)
1
Filter the astronauts table.
2
Group by nationality.
3
Aggregate the results.
4
Display the results. 
┏━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ nationality ┃ number_trips ┃ longest_time ┃ shortest_time ┃
┡━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ string      │ int64        │ float64      │ float64       │
├─────────────┼──────────────┼──────────────┼───────────────┤
│ Mongolia    │            1 │       188.70 │         188.7 │
│ Romania     │            1 │       188.70 │         188.7 │
│ France      │           18 │      4721.83 │         118.8 │
└─────────────┴──────────────┴──────────────┴───────────────┘

And order the results by ‘number_trips’ and ‘longest_time’ in descending order:

t = (
    astronauts.filter(~astronauts["nationality"].like("U.S.%"))
    .group_by("nationality")
    .agg(
        [
            ibis._.count().name("number_trips"),
            ibis._["hours_mission"].max().name("longest_time"),
            ibis._["hours_mission"].min().name("shortest_time"),
        ]
    )
    .order_by([ibis.desc("number_trips"), ibis.desc("longest_time")])
)
t.head(3)
1
Filter the astronauts table.
2
Group by nationality.
3
Aggregate the results.
4
Order the result.
5
Display the results. 
┏━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ nationality ┃ number_trips ┃ longest_time ┃ shortest_time ┃
┡━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ string      │ int64        │ float64      │ float64       │
├─────────────┼──────────────┼──────────────┼───────────────┤
│ Japan       │           20 │      6902.35 │        189.90 │
│ Canada      │           18 │      4887.00 │        193.77 │
│ France      │           18 │      4721.83 │        118.80 │
└─────────────┴──────────────┴──────────────┴───────────────┘

For the missions table, you can group by ‘company_name’ and ‘status_rocket’, and then sum the ‘cost’:

t = (
    missions.filter(missions["status_mission"] == "Failure")
    .group_by(["company_name", "status_rocket"])
    .agg(ibis._["cost"].sum().name("cost"))
    .order_by(ibis.desc("cost"))
)
t.head(3)
1
Filter the missions table.
2
Group by company_name and status_rocket.
3
Aggregate the results.
4
Order the results.
5
Display the results. 
┏━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━┓
┃ company_name ┃ status_rocket ┃ cost    ┃
┡━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━┩
│ string       │ string        │ float64 │
├──────────────┼───────────────┼─────────┤
│ NASA         │ StatusRetired │   900.0 │
│ Northrop     │ StatusActive  │   255.0 │
│ Arianespace  │ StatusActive  │   237.0 │
└──────────────┴───────────────┴─────────┘

Writing tables

Finally, let’s write a table back to Starburst Galaxy.

Warning

You cannot write to the sample catalog; uncomment the code and write to a catalog you have write access to.

#con.create_table("t", t, overwrite=True)

Next steps

Now that you’ve connected to Starburst Galaxy and learned the basics, you can query your own data. See the rest of the Ibis documentation or Starburst Galaxy documentation. You can open an issue if you run into one!

Back to top