datatable: process 2 frames

Question

data_df = pd.DataFrame({"AAA": [1, 2, 1, 3],
                        "BBB": [1, 1, 2, 2],
                        "CCC": [2, 1, 3, 1]})

lookup_df = pd.DataFrame({"key": [1,2,3], "value" : ["Alpha", "Beta", "Charlie"]})

data_dt = dt.Frame(data_df)
lookup_dt = dt.Frame(lookup_df)

I have these 2 datatables; one containing the data and another containing the lookup values.

I am trying to produce a result datatable with additional columns with lookups done.

For example:

AAA BBB CCC AAA_Category BBB_Category CCC_Category
1   1   2   Alpha        Alpha        Beta

I can do that by converting the lookup into a list of tuples and doing something like

for name, cond, value in conditions:
   data_dt[cond, f"{name}_category"] = value

Is there anyway to achieve this is in a more native vector "datatable" way.

langtang · Answer 1 · 2022-03-26T19:28:43.440

You can set a key on lookup_dt, and then join, each time renaming the target column to key, and then renaming back; use dt.cbind to bind the resulting list of frames

lookup_dt.key = "key"

dt.cbind(
    [data_dt[:,{"key":f[c]}][:,:, join(lookup_dt)][:, {c:f.key,c+"category":f.value}] for c in data_dt.names]
)

Output:

   |   AAA  AAAcategory    BBB  BBBcategory    CCC  CCCcategory
   | int64  str32        int64  str32        int64  str32      
-- + -----  -----------  -----  -----------  -----  -----------
 0 |     1  Alpha            1  Alpha            2  Beta       
 1 |     2  Beta             1  Alpha            1  Alpha      
 2 |     1  Alpha            2  Beta             3  Charlie    
 3 |     3  Charlie          2  Beta             1  Alpha      
[4 rows x 6 columns]

Comment: The lack of a reshape long / reshape wide capability is one of the key missing elements of py-datatable, in my opinion. A natural approach to your problem is to make the data_dt long, and do a single merge with the lookup_dt, and then if desired, reshape the result back to wide format. This is done below, but is a workaround, given the lack of built-in reshape long / reshape wide:

# Set key
lookup_dt.key = "key"

# Reshape long and do a single join
long_dt = dt.rbind([data_dt[:,{"key":f[c], "cat":c}] for c in data_dt.names])[:,:,join(lookup_dt)]

# Reshape wide and rename to your desired column names
dt.cbind([long_dt[f.cat==c,{c:f.key,c+"category":f.value}] for c in data_dt.names])

score 0 · Answer 2 · answered Mar 28 '22 at 00:37

You can avoid flipping from wide to long and back to wide for this particular scenario by taking advantage of the replace function, before recombining. This should be more performant, although it does involve a couple of steps, and me ripping into some of your existing data:

from datatable import dt, f
# ripping here
# if you can access a dictionary for your lookup
# which is a what a lookup is, then do so
key = [1,2,3]
value =  ["Alpha", "Beta", "Charlie"]
# the replace function is pretty strict
# you can only replace like type for type
# so string for string, int for int, ...
mapping = dict(zip(map(str, key), value))

# create another dataframe to munge
temp = data_dt.copy()
# convert to string, so that replace can work
temp = temp[:, dt.str32(f[:])] # you can also use as_type instead
# replace is an in place operation; no need for reassignment
temp.replace(mapping)
# change the column names :
temp.names = [f"{name}_category" for name in temp.names]
 
temp

   | AAA_category  BBB_category  CCC_category
   | str32         str32         str32
-- + ------------  ------------  ------------
 0 | Alpha         Alpha         Beta
 1 | Beta          Alpha         Alpha
 2 | Alpha         Beta          Charlie
 3 | Charlie       Beta          Alpha
[4 rows x 3 columns]

Now, you can combine data_dt and temp with dt.cbind:

dt.cbind([data_dt, temp])

   |   AAA    BBB    CCC  AAA_category  BBB_category  CCC_category
   | int64  int64  int64  str32         str32         str32
-- + -----  -----  -----  ------------  ------------  ------------
 0 |     1      1      2  Alpha         Alpha         Beta
 1 |     2      1      1  Beta          Alpha         Alpha
 2 |     1      2      3  Alpha         Beta          Charlie
 3 |     3      2      1  Charlie       Beta          Alpha
[4 rows x 6 columns]

datatable: process 2 frames

2 Answers2