How to update a pandas dataframe with sets, from another dataframe

Mick Published at Java

Mick

Trying to update each row from df1 to df2 if unique_value is matched, then based on status from df1, update price in price_array in df2; If not, append the row to df2 and assign new ID column.

This is a part 2 question from: Iterate each row by updating values from 1st dataframe to 2nd dataframe based on unique value w/ different index, otherwise append and assign new ID

Note:
active and new: add
suspended and inactive: remove

df1 (NO ID COLUMN):

        unique_value        Status        Price
0       xyz123              active        6.67
1       eff987              suspended     1.75
2       efg125              active        5.77
3       xyz123              new           7.55
4       xyz123              inactive      4.55
5       eff987              new           5.55

df2:

        unique_value        Price_array  ID
0       xyz123              {4.55}       1000
1       xyz985              {1.31}       1001
2       abc987              {4.56}       1002
3       eff987              {1.75}       1003
4       asd541              {8.85}       1004

Desired output for updated df2:

        unique_value        Price_array       ID
0       xyz123              {6.67,7.55}       1000    <- updated (added 6.67, added 7.55, removed 4.55)
1       xyz985              {1.31}            1001    
2       abc987              {4.56}            1002
3       eff987              {5.55}            1003    <- updated (removed 1.75, added 5.55)
4       asd541              {8.85}            1004
5       efg125              {5.77}            1005    <- appended and new ID assigned

Here is the code from part 1: Iterate each row by updating values from 1st dataframe to 2nd dataframe based on unique value w/ different index, otherwise append and assign new ID

# additional state variables
# 1. for the ID to be added
current_max_id = df2["ID"].max()
# 2. for matching unique_values, avoiding searching df2["unique_value"] every time
current_value_set = set(df2["unique_value"].values)

# match unique_value's using the state variable instead of `df2`
mask = df1["unique_value"].isin(current_value_set)

for i in range(len(df1)):
    
    # current unique_value from df1
    uv1 = df1["unique_value"][i]
    
    # 1. update existing
    if mask[i]:
        
        # broadcast df1 into the matched rows in df2 (mind the shape)
        df2.loc[df2["unique_value"] == uv1, ["unique_value", "Status", "Price"]] = df1.iloc[i, :].values.reshape((1, 3))
        
        #UPDATE PRICE with PRICE_ARRAY
        ...see below

    # 2. append new
    else:
        # update state variables
        current_max_id += 1
        current_value_set.add(uv1)
        # append the row (assumes df2.index=[0,1,2,3,...])
        df2.loc[len(df2), :] = [df1.iloc[i, 0], df1.iloc[i, 1], df1.iloc[i, 2], current_max_id]

Is there any way to update the price in df1 to price_array in df2 based on status from df1? I'm thinking something along the line of this ("status" column removed from the broadcast portion of the code):

        curr_price=df1.iloc[i,df1.columns.get_loc('Price')]
        if df1.iloc[i,df1.columns.get_loc('Status')] in ('inactive', 'suspended'):
            df2.loc[df2["unique_value"] == uv1,'Price_array'].discard(curr_price)
        else:
            df2.loc[df2["unique_value"] == uv1,'Price_array'].add(curr_price)

But got the following error:

ValueError                                Traceback (most recent call last)
<ipython-input-156-6ff78c7a4a9a> in <module>()
     46     if mask[i]:
     47         # Broadcast refresh table into the matched rows in historical
---> 48         df2.loc[df2["unique_value"] == uv1, ["unique_value", "Price"]] = df1.iloc[i, :].values.reshape((1,3))
     49 

/anaconda/envs/pyfull36/lib/python3.6/site-packages/pandas/core/indexing.py in __setitem__(self, key, value)
    192             key = com._apply_if_callable(key, self.obj)
    193         indexer = self._get_setitem_indexer(key)
--> 194         self._setitem_with_indexer(indexer, value)
    195 
    196     def _has_valid_type(self, k, axis):

/anaconda/envs/pyfull36/lib/python3.6/site-packages/pandas/core/indexing.py in _setitem_with_indexer(self, indexer, value)
    581                     value = np.array(value, dtype=object)
    582                     if len(labels) != value.shape[1]:
--> 583                         raise ValueError('Must have equal len keys and value '
    584                                          'when setting with an ndarray')
    585 

ValueError: Must have equal len keys and value when setting with an ndarray

Trenton McKinney

The following code as 3 main steps:
1. Setup the dataframes, and .join them.
2. Use np.where and set math, to update 'Price_array'.
  - Older versions of pandas raise a TypeError when aggregating set, as per Pandas groupby and make set of items. This is not an issue in pandas 1.1.2.
3. Fill any missing ID values, using .update.

Setup DataFrames

This is how they should look to begin with

import pandas as pd

# setup dataframes
df1 = pd.DataFrame({'unique_value': ['xyz123', 'eff987', 'efg125', 'xyz123', 'xyz123', 'eff987'], 'Status': ['active', 'suspended', 'active', 'new', 'inactive', 'new'], 'Price': [6.67, 1.75, 5.77, 7.55, 4.55, 5.55]})
df2 = pd.DataFrame({'unique_value': ['xyz123', 'xyz985', 'abc987', 'eff987', 'asd541'], 'Price_array': [{4.55}, {1.31}, {4.56}, {1.75}, {8.85}], 'ID': [1000, 1001, 1002, 1003, 1004]})

# df1
  unique_value     Status  Price
0       xyz123     active   6.67
1       eff987  suspended   1.75
2       efg125     active   5.77
3       xyz123        new   7.55
4       xyz123   inactive   4.55
5       eff987        new   5.55

# df2
  unique_value Price_array    ID
0       xyz123      {4.55}  1000
1       xyz985      {1.31}  1001
2       abc987      {4.56}  1002
3       eff987      {1.75}  1003
4       asd541      {8.85}  1004

setup the dataframes to be joined

# for df2, set unique_value as the index
df2.set_index('unique_value', inplace=True)

# for df1, groupby unique_value and aggregate a set onto Price
df1g = df1.groupby('unique_value').agg({'Price': set})

# join df2 and df1g
dfj = df2.join(df1g, how='outer')

# replace NaN with empty string, '', then replace '', with empty set; NaN can't be directly replace with a set
dfj[['Price_array', 'Price']] = dfj[['Price_array', 'Price']].fillna('').applymap(set)

# dfj
             Price_array      ID               Price
unique_value                                        
abc987            {4.56}  1002.0                  {}
asd541            {8.85}  1004.0                  {}
eff987            {1.75}  1003.0        {1.75, 5.55}
efg125                {}     NaN              {5.77}
xyz123            {4.55}  1000.0  {4.55, 6.67, 7.55}
xyz985            {1.31}  1001.0                  {}

use `np.where` and `set` math to update `'Price_array'`

If 'Price' is an empty set {}
- Use x.Price - x.Price_array
Otherwise
- Use x.Price_array
The order of set math, matters
- {4.56} - set() is {4.56}
- set() - {4.56} is set()

# use np.where and set math to update Price_array
dfj['Price_array'] = dfj[['Price_array', 'Price']].apply(lambda x: np.where(len(x.Price) > 0, x.Price - x.Price_array, x.Price_array), axis=1)

# drop the Price column
dfj.drop(columns=['Price'], inplace=True)

# reset the index
dfj.reset_index(inplace=True)

# dfj
  unique_value   Price_array      ID
0       abc987        {4.56}  1002.0
1       asd541        {8.85}  1004.0
2       eff987        {5.55}  1003.0
3       efg125        {5.77}     NaN
4       xyz123  {6.67, 7.55}  1000.0
5       xyz985        {1.31}  1001.0

fill any missing `'ID'` values

# extract all rows with missing ID
dfjna = dfj.loc[dfj.ID.isna()].copy()

# get the max ID value from the ID column
idm = int(dfj.ID.max())

# update all the missing ID values from a range beginning at idm+1
dfjna.ID = range(idm+1, idm+len(dfjna)+1)

# update the missing ID values in dfj with dfjna
dfj.update(dfjna)

# set the ID column as int
dfj.ID = dfj.ID.astype(int)

# display(dfj)
  unique_value   Price_array    ID
0       abc987        {4.56}  1002
1       asd541        {8.85}  1004
2       eff987        {5.55}  1003
3       efg125        {5.77}  1005
4       xyz123  {6.67, 7.55}  1000
5       xyz985        {1.31}  1001

Collected from the Internet

Please contact [email protected] to delete if infringement.