How to extend the columns of a pydatable with a dictionary containing the values in list?

user_9479925 0
Viewed 83

I have created a sample datatable as,

DT_EX = dt.Frame({'recency': ['current','savings','fixex','current','savings','fixed','savings','current'],
                  'amount': [4200,2300,1500,8000,1200,6500,4500,9010],
                  'no_of_pl': [3,2,1,5,1,2,5,4],
                  'default': [True,False,True,False,True,True,True,False]})

and it can be viewed as,

   | recency  amount  no_of_pl  default
-- + -------  ------  --------  -------
 0 | current    4200         3        1
 1 | savings    2300         2        0
 2 | fixex      1500         1        1
 3 | current    8000         5        0
 4 | savings    1200         1        1
 5 | fixed      6500         2        1
 6 | savings    4500         5        1
 7 | current    9010         4        0

[8 rows x 4 columns]

I'm doing some data manipulations as explained in the below steps:

Step 1: Two new columns are added to datatable as

DT_EX[:, f[:].extend({"total_amount": f.amount*f.no_of_pl,
                      'test_col': f.amount/f.no_of_pl})]

output:

   | recency  amount  no_of_pl  default  total_amount  test_col
-- + -------  ------  --------  -------  ------------  --------
 0 | current    4200         3        1         12600    1400  
 1 | savings    2300         2        0          4600    1150  
 2 | fixex      1500         1        1          1500    1500  
 3 | current    8000         5        0         40000    1600  
 4 | savings    1200         1        1          1200    1200  
 5 | fixed      6500         2        1         13000    3250  
 6 | savings    4500         5        1         22500     900  
 7 | current    9010         4        0         36040    2252.5

[8 rows x 6 columns]

Step2:

A dictionary is created as, and note it has values stored in a list

test_dict = {'discount': [10,20,30,40,50,60,70,80],
             'charges': [0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8]}

Step 3:

A new datatable created with the above mentioned dict and append to a datatable DT_EX as,

dt.cbind(DT_EX, dt.Frame(test_dict))

output:

   | recency  amount  no_of_pl  default  discount  charges
-- + -------  ------  --------  -------  --------  -------
 0 | current    4200         3        1        10      0.1
 1 | savings    2300         2        0        20      0.2
 2 | fixex      1500         1        1        30      0.3
 3 | current    8000         5        0        40      0.4
 4 | savings    1200         1        1        50      0.5
 5 | fixed      6500         2        1        60      0.6
 6 | savings    4500         5        1        70      0.7
 7 | current    9010         4        0        80      0.8

[8 rows x 6 columns]

Here we can see a datatable with the newly added columns (discount, charges)

Step 4:

As we know that extend function can be used to add on the columns i tried to pass in the dictionary named test_dict as,

DT_EX[:, f[:].extend(test_dict)]

Output:

Out[18]: 
   | recency  amount  no_of_pl  default  discount  discount.0  discount.1  discount.2  discount.3  discount.4  …  charges.2  charges.3  charges.4  charges.5  charges.6
-- + -------  ------  --------  -------  --------  ----------  ----------  ----------  ----------  ----------     ---------  ---------  ---------  ---------  ---------
 0 | current    4200         3        1        10          20          30          40          50          60  …        0.4        0.5        0.6        0.7        0.8
 1 | savings    2300         2        0        10          20          30          40          50          60  …        0.4        0.5        0.6        0.7        0.8
 2 | fixex      1500         1        1        10          20          30          40          50          60  …        0.4        0.5        0.6        0.7        0.8
 3 | current    8000         5        0        10          20          30          40          50          60  …        0.4        0.5        0.6        0.7        0.8
 4 | savings    1200         1        1        10          20          30          40          50          60  …        0.4        0.5        0.6        0.7        0.8
 5 | fixed      6500         2        1        10          20          30          40          50          60  …        0.4        0.5        0.6        0.7        0.8
 6 | savings    4500         5        1        10          20          30          40          50          60  …        0.4        0.5        0.6        0.7        0.8
 7 | current    9010         4        0        10          20          30          40          50          60  …        0.4        0.5        0.6        0.7        0.8

[8 rows x 20 columns]

Note : Here in the output it is seen that there are about 8 columns created (each element of a list is filled in) for each of dictionary key (discount, charges) and total newly added columns are 16.

Step 5:

I have had thought of creating a dictionary with values of numpy array as,

test_dict_1 = {'discount': np.array([10,20,30,40,50,60,70,80]),
               'charges': np.array([0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8])}

I have pass the test_dict_1 to extend function as 

DT_EX[:, f[:].extend(test_dict_1)]

output:

Out[20]: 
   | recency  amount  no_of_pl  default  discount  charges
-- + -------  ------  --------  -------  --------  -------
 0 | current    4200         3        1        10      0.1
 1 | savings    2300         2        0        20      0.2
 2 | fixex      1500         1        1        30      0.3
 3 | current    8000         5        0        40      0.4
 4 | savings    1200         1        1        50      0.5
 5 | fixed      6500         2        1        60      0.6
 6 | savings    4500         5        1        70      0.7
 7 | current    9010         4        0        80      0.8

[8 rows x 6 columns]

At this step, extend has taken a dictionary and added the new columns to DT_EX. and it is an expected output.

So, here i would like to understand what has happened in the step 4? Why didn't it take a list of values from a dictionary key to add a new column? Why the step 5 case was executed?

Could you please write your comments/answers on it?

py-datatable python
1 Answers

You could wrap the dictionary in a Frame constructor to get the desired result:

>>> DT_EX[:, f[:].extend(dt.Frame(test_dict))]
   | recency  amount  no_of_pl  default  discount  charges
-- + -------  ------  --------  -------  --------  -------
 0 | current    4200         3        1        10      0.1
 1 | savings    2300         2        0        20      0.2
 2 | fixex      1500         1        1        30      0.3
 3 | current    8000         5        0        40      0.4
 4 | savings    1200         1        1        50      0.5
 5 | fixed      6500         2        1        60      0.6
 6 | savings    4500         5        1        70      0.7
 7 | current    9010         4        0        80      0.8

[8 rows x 6 columns]

As to what happens in step 4, the following logic is applied: when we evaluate a dictionary for the DT[] call, we treat it simply as a list of elements, where each item in the list is named by the corresponding key. If an "item" produces multiple columns, then each of the columns gets the same name from the key. Now, in this case each "item" is a list again, and we don't have any special rules for evaluating such lists of primitives. So they end up expanding into a list of columns where each column is a constant.

You are right that the end result looks quite counterintuitive, so we'd probably want to adjust the rules for evaluating lists inside DT[] expressions.

user_9586240
Related