.. _user_guide.data_preparation.joins:

======
Joins
======

When working with datasets, we often need to merge data from different sources. To do this, we need keys on which to join our data.

Let's use the `US Flights 2015 datasets <https://www.kaggle.com/datasets/usdot/flight-delays>`__. We have three datasets.

First, we have information on each flight.

.. code-block:: python

    import vastorbit as vo

    flights  = vo.read_csv("flights.csv")
    flights.head(100)

.. ipython:: python
    :suppress:

    import vastorbit as vo

    try:
        flights = vo.read_csv("/Users/badr.ouali/Documents/VastOrbit-master/docs/source/_static/website/examples/data/flights/flights.csv")
    except:
        flights = vo.VastFrame("flights")
    res = flights.head(100)
    html_file = open("/Users/badr.ouali/Documents/VastOrbit-master/docs/figures/ug_dp_table_join_1.html", "w")
    html_file.write(res._repr_html_())
    html_file.close()

.. raw:: html
    :file: /Users/badr.ouali/Documents/VastOrbit-master/docs/figures/ug_dp_table_join_1.html

Second, we have information on each airport.

.. code-block:: python

    airports = vo.read_csv("airports.csv")
    airports.head(100)

.. ipython:: python
    :suppress:

    import vastorbit as vo

    try:
        airports = vo.read_csv("/Users/badr.ouali/Documents/VastOrbit-master/docs/source/_static/website/examples/data/flights/airports.csv",
            dtype = {
                "IATA_CODE": "Varchar(20)",
                "AIRPORT": "Varchar(156)",
                "CITY": "Varchar(60)",
                "STATE": "Varchar(20)",
                "COUNTRY": "Varchar(20)",
                "LATITUDE": "real",
                "LONGITUDE": "real",
            }
        )
    except:
        airports = vo.VastFrame("airports")
    res = airports.head(100)
    html_file = open("/Users/badr.ouali/Documents/VastOrbit-master/docs/figures/ug_dp_table_join_2.html", "w")
    html_file.write(res._repr_html_())
    html_file.close()

.. raw:: html
    :file: /Users/badr.ouali/Documents/VastOrbit-master/docs/figures/ug_dp_table_join_2.html

Third, we have the names of each airline.

.. code-block:: python

    airlines = vo.read_csv("airlines.csv")
    airlines.head(100)

.. ipython:: python
    :suppress:

    import vastorbit as vo
    airlines = vo.read_csv("/Users/badr.ouali/Documents/VastOrbit-master/docs/source/_static/website/examples/data/flights/airlines.csv")
    res = airlines.head(100)
    html_file = open("/Users/badr.ouali/Documents/VastOrbit-master/docs/figures/ug_dp_table_join_3.html", "w")
    html_file.write(res._repr_html_())
    html_file.close()

.. raw:: html
    :file: /Users/badr.ouali/Documents/VastOrbit-master/docs/figures/ug_dp_table_join_3.html

Notice that each dataset has a primary or secondary key on which to join the data. For example, we can join the ``flights`` dataset to the ``airlines`` and ``airport`` datasets using the corresponding ``IATA`` code.

To join datasets in vastorbit, use the :py:func:`~vastorbit.VastFrame.join` method.

.. ipython:: python

    help(vo.VastFrame.join)

Let's use a left join to merge the ``airlines`` dataset and the ``flights`` dataset.

.. code-block:: python

    flights = flights.join(
        airlines,
        how = "left",
        on = {"airline": "IATA_CODE"},
        expr2 = ["AIRLINE AS airline_long"],
    )
    flights.head(100)

.. ipython:: python
    :suppress:

    flights = flights.join(
        airlines,
        how = "left",
        on = {"airline": "IATA_CODE"},
        expr2 = ["AIRLINE AS airline_long"],
    )
    res = flights.head(100)
    html_file = open("/Users/badr.ouali/Documents/VastOrbit-master/docs/figures/ug_dp_table_join_4.html", "w")
    html_file.write(res._repr_html_())
    html_file.close()

.. raw:: html
    :file: /Users/badr.ouali/Documents/VastOrbit-master/docs/figures/ug_dp_table_join_4.html

Let's use two left joins to get the information on the origin and destination airports.

.. code-block:: python

    flights = flights.join(
        airports,
        how = "left",
        on = {"origin_airport": "IATA_CODE"},
        expr2 = [
            "LATITUDE AS origin_lat",
            "LONGITUDE AS origin_lon",
        ],
    )
    flights = flights.join(
        airports,
        how = "left",
        on = {"destination_airport": "IATA_CODE"},
        expr2 = [
            "LATITUDE AS destination_lat",
            "LONGITUDE AS destination_lon",
        ],
    )
    flights.head(100)

.. ipython:: python
    :suppress:

    flights = flights.join(
        airports,
        how = "left",
        on = {"origin_airport": "IATA_CODE"},
        expr2 = [
            "LATITUDE AS origin_lat",
            "LONGITUDE AS origin_lon",
        ],
    )
    flights = flights.join(
        airports,
        how = "left",
        on = {"destination_airport": "IATA_CODE"},
        expr2 = [
            "LATITUDE AS destination_lat",
            "LONGITUDE AS destination_lon",
        ],
    )
    res = flights.head(100)
    html_file = open("/Users/badr.ouali/Documents/VastOrbit-master/docs/figures/ug_dp_table_join_5.html", "w")
    html_file.write(res._repr_html_())
    html_file.close()

.. raw:: html
    :file: /Users/badr.ouali/Documents/VastOrbit-master/docs/figures/ug_dp_table_join_5.html

To avoid duplicate information, splitting the data into different tables is very important. Just imagine: what if we wrote the longitude and the latitude of the destination and origin airports for each flight? It would add way too many duplicates and drastically impact the volume of the data.

Cross joins are special: they don't need a key. Cross joins are used to perform mathematical operations.

Let's use a cross join of the ``airports`` dataset on itself to compute the distance between every airport.

.. code-block:: python

    distances = airports.join(
        airports, 
        how = "cross", 
        expr1 = [
            "IATA_CODE AS airport1", 
            "LATITUDE AS airport1_latitude", 
            "LONGITUDE AS airport1_longitude"
        ],
        expr2 = [
            "IATA_CODE AS airport2", 
            "LATITUDE AS airport2_latitude", 
            "LONGITUDE AS airport2_longitude",
        ],
    )
    distances.filter("airport1 != airport2")

    import vastorbit.sql.functions as fun

    distances["distance"] = fun.distance(
        distances["airport1_latitude"], 
        distances["airport1_longitude"],                                
        distances["airport2_latitude"],
        distances["airport2_longitude"],
    )

.. ipython:: python
    :suppress:

    distances = airports.join(
        airports, 
        how = "cross", 
        expr1 = [
            "IATA_CODE AS airport1", 
            "LATITUDE AS airport1_latitude", 
            "LONGITUDE AS airport1_longitude"
        ],
        expr2 = [
            "IATA_CODE AS airport2", 
            "LATITUDE AS airport2_latitude", 
            "LONGITUDE AS airport2_longitude",
        ],
    )
    distances.filter("airport1 != airport2")

    import vastorbit.sql.functions as fun

    distances["distance"] = fun.distance(
        distances["airport1_latitude"], 
        distances["airport1_longitude"],                                
        distances["airport2_latitude"],
        distances["airport2_longitude"],
    )
    res = distances["distance"]
    html_file = open("/Users/badr.ouali/Documents/VastOrbit-master/docs/figures/ug_dp_table_join_6.html", "w")
    html_file.write(res._repr_html_())
    html_file.close()

.. raw:: html
    :file: /Users/badr.ouali/Documents/VastOrbit-master/docs/figures/ug_dp_table_join_6.html

vastorbit offers many powerful options for joining datasets.

In the next lesson, we'll learn how to deal with duplicates.

.. ipython:: python
   :suppress:

   from vastorbit._utils._sql._sys import purge_memory
   purge_memory()