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 1 import osimport ibisfrom dotenv import load_dotenv2 = True 3 4 = 'dopep89606@hartaria.com/accountadmin' = os.getenv("PASSWORD" )= os.getenv("HOSTNAME" )= os.getenv("PORTNUMBER" )5 = "sample" = "demo" 6 = ibis.trino.connect (= user,= password,= host,= port,= catalog,= schema,7
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 0x727cf116e8f0> Load tables Once you have a connection, you can assign tables to variables.
1 = con.table("astronauts" )2 = con.table("missions" )
1 Create astonauts variable. 2 Create missions variable. You can display slices of data:
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 │ └───────┴────────┴───────────────────┴─────────────────────┴─────────────────────────┴────────┴───────────────┴────────────────┴───────────────────┴────────────────────────┴───────────────────┴────────────────┴──────────────────────────┴────────────┴─────────────────┴─────────────────┴─────────────────┴─────────────────┴─────────────────┴───────────────┴───────────────┴─────────┴─────────────────┴───────────────┘ 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 │ NULL │ 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:
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 } 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:
1 = astronauts.select("name" , "nationality" , "mission_title" , "mission_number" , "hours_mission" )2 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:
1 = missions.select("company_name" , "status_rocket" , "cost" , "status_mission" )2 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 │ NULL │ Success │ └──────────────┴───────────────┴─────────┴────────────────┘ You can also apply filters to your queries:
1 = astronauts.filter (~ astronauts["nationality" ].like("U.S.%" ))2 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:
1 = missions.filter (missions["status_mission" ] == "Failure" )2 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? \x81 hia 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 1 = missions.mutate(date= ibis.coalesce(ibis._["date" ], None ))2 = t.order_by(t["date" ].asc())3 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 │ NULL │ Success │ │ 3366 │ RVSN USSR │ Site 43/3, Plesetsk Cosmodrome, Russia │ Fri Apr 02, 1971 08:20 UTC │ Voskhod | Cosmos 403 │ StatusRetired │ NULL │ Success │ │ 1262 │ Arianespace │ ELA-2, Guiana Space Centre, French Guiana, France │ Fri Apr 02, 1999 22:03 UTC │ Ariane 42P | Insat-2E │ StatusRetired │ NULL │ 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:
1 = astronauts.filter (~ astronauts["nationality" ].like("U.S.%" )).agg(2 "number_trips" ),"hours_mission" ].max ().name("longest_time" ),"hours_mission" ].min ().name("shortest_time" ),3 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:
= (1 filter (~ astronauts["nationality" ].like("U.S.%" ))2 "nationality" )3 "number_trips" ),"hours_mission" ].max ().name("longest_time" ),"hours_mission" ].min ().name("shortest_time" ),4 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 │ ├──────────────┼──────────────┼──────────────┼───────────────┤ │ Hungry │ 1 │ 188.75 │ 188.75 │ │ Canada │ 18 │ 4887.00 │ 193.77 │ │ Saudi Arabia │ 1 │ 170.00 │ 170.00 │ └──────────────┴──────────────┴──────────────┴───────────────┘ And order the results by ‘number_trips’ and ‘longest_time’ in descending order:
= (1 filter (~ astronauts["nationality" ].like("U.S.%" ))2 "nationality" )3 "number_trips" ),"hours_mission" ].max ().name("longest_time" ),"hours_mission" ].min ().name("shortest_time" ),4 "number_trips" ), ibis.desc("longest_time" )])5 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’:
= (1 filter (missions["status_mission" ] == "Failure" )2 "company_name" , "status_rocket" ])3 "cost" ].sum ().name("cost" ))4 "cost" ))5 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.
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!