Update values in one table based off the values in another table.

[JRocki]

Hello all,

I have two dataframes and I'd like to update the values in one dataframe based on the values of another dataframe, if the values of a shared column are equivalent. In Pandas, it looks like this:

df1 = pd.DataFrame({'ID': range(10), 'Value': range(10, 20)})
df2 = pd.DataFrame({'ID': [3, 7], 'Value': [100, 200]})
df1.loc[df1.ID.isin(df2.ID), 'Value'] = df2['Value'].values
print(df1)

We can see that in df1 rows where ID = [3,7] now have the values from df2. How would one do this in Ibis? I have tried the following:

df1 = pd.DataFrame({'ID': range(10), 'Value': range(10, 20)})
df2 = pd.DataFrame({'ID': [3, 7], 'Value': [100, 200]})

table1 = ibis.memtable(df1)
table2 = ibis.memtable(df2)

# This returns AttributeError: 'BooleanColumn' object has no attribute 'dtype'
table1 = table1['Value'].case().when(table1['ID'].isin(table2['ID']), table2['Value']).else_(table1['Value']).end()

# This returns ibis.common.exceptions.RelationError: Selection expressions don't fully originate from dependencies of the table expression.
table1 = table1.mutate(Value=ibis.case().when(table1['ID'].isin(table2['ID']), table2['Value']).else_(table1['Value']).end())

[ozgurkalan]

hi, I have a method for my daily routine, you may try:

    @classmethod
    def update_table(cls, t: Table, right:Union[Table, pd.DataFrame],
                    on:str, col_to_update:str, right_col_updater:str)->Table:
        """updates a column of a table with another table or dataframe
        
        t: ibis table, which is the latest modified one
        right:  ibis table or pandas dataframe, which will update the table
        on: str, column name to be used for joining on
        col_to_update: str, column name to be updated in the table
        right_col_updater: str, column name to be used for updating
        
        """
        # check if t is ibis table
        if not isinstance(t, Table):
            raise ValueError("t must be ibis table")
        # check if right is ibis table or pandas dataframe
        if not isinstance(right, (Table, pd.DataFrame)):
            raise ValueError("right must be ibis table or pandas dataframe")
        
        # convert right to ibis table if it is pandas dataframe
        if isinstance(right, pd.DataFrame):
            right = ibis.memtable(right)
            
        # check if on is in t and right
        if on not in t.columns:
            raise ValueError(f"{on} not in t")
        if on not in right.columns:
            raise ValueError(f"{on} not in right")
        
        # check if col_to_update is in t
        if col_to_update not in t.columns:
            raise ValueError(f"{col_to_update} not in t")
        
        # check if right_col_updater is in right
        if right_col_updater not in right.columns:
            raise ValueError(f"{right_col_updater} not in right")
        
        right:Table = right[on, right_col_updater]
        right = right.rename({"REPLACER_delete_me":right_col_updater})
        
        t = t.join(right, predicates=on, rname="{name}_delete_me", how='left')
        t = (t
            .mutate(ibis.ifelse(
                _["REPLACER_delete_me"].notnull(), 
                _["REPLACER_delete_me"], _[col_to_update]
                ).name(col_to_update)
                )
            
            ).drop(sel.matches(".*_delete_me"))
        
        return t

[ozgurkalan]

this may also be useful:

    @classmethod
    def alter_table(cls, t:Table, condition, replace_dict:dict)->Table:
        """replaces values in a table and returns the ibis table
        
        parameters:
            t: ibis table, which is the latest modified one
            condition: ibis condition, e.g. (t.col=="value"), warning: conditions must called with t
            replace_dict: dict, keys are column names, values are strings to be replaced
            
        """
        # check if cols exists
        cols = list(replace_dict.keys())
        t_cols = t.columns
        if set(cols).difference(t_cols) != set():
            raise ValueError(f"columns {set(cols).difference(t_cols)} not in the table")
        
        REPLACEMENTS = replace_dict
        return (t
                    .mutate(
                        sel.across(
                            REPLACEMENTS.keys(),
                            lambda v: ibis.ifelse(
                                condition, REPLACEMENTS[v.get_name()], v
                            )
                        )
                    )
                    )

[cpcloud]

I would suggest a left outer join on ID, followed by selecting the Value column from the right side of the join:

In [32]: import pandas as pd

In [33]: from ibis.interactive import *

In [34]: df1 = pd.DataFrame({'ID': range(10), 'Value': range(10, 20)})

In [35]: df2 = pd.DataFrame({'ID': [3, 7], 'Value': [100, 200]})

In [36]:

In [36]: table1 = ibis.memtable(df1)

In [37]: table2 = ibis.memtable(df2)

In [38]: table1[["ID"]].left_join(table2, "ID").select(table1.ID, table2.Value)
Out[38]:
┏━━━━━━━┳━━━━━━━┓
┃ ID    ┃ Value ┃
┡━━━━━━━╇━━━━━━━┩
│ int64 │ int64 │
├───────┼───────┤
│     3 │   100 │
│     7 │   200 │
│     0 │  NULL │
│     1 │  NULL │
│     2 │  NULL │
│     4 │  NULL │
│     5 │  NULL │
│     6 │  NULL │
│     8 │  NULL │
│     9 │  NULL │
└───────┴───────┘