How to lump together factor levels of a string type column into another in pydatatable?

Question

I have a datatable as,

DT_X = dt.Frame({'variety': ['Caturra',
  'Bourbon',
  'Typica',
  'Catuai',
  'Hawaiian Kona',
  'Yellow Bourbon',
  'Mundo Novo',
  'Catimor',
  'SL14',
  'SL28',
  'Pacas',
  'Gesha',
  'Pacamara',
  'SL34',
  'Arusha',
  'Peaberry',
  'Mandheling',
  'Sumatra',
  'Blue Mountain',
  'Ethiopian Yirgacheffe',
  'Java',
  'Ruiru 11',
  'Ethiopian Heirlooms',
  'Marigojipe',
  'Moka Peaberry',
  'Pache Comun',
  'Sulawesi',
  'Sumatra Lintong'],
 'count': [256,
  226,
  211,
  74,
  44,
  35,
  33,
  20,
  17,
  15,
  13,
  12,
  8,
  8,
  6,
  5,
  3,
  3,
  2,
  2,
  2,
  2,
  1,
  1,
  1,
  1,
  1,
  1]})

and it can be viewed as,

Out[8]: 
   | variety                count
-- + ---------------------  -----
 0 | Caturra                  256
 1 | Bourbon                  226
 2 | Typica                   211
 3 | Catuai                    74
 4 | Hawaiian Kona             44
 5 | Yellow Bourbon            35
 6 | Mundo Novo                33
 7 | Catimor                   20
 8 | SL14                      17
 9 | SL28                      15
10 | Pacas                     13
11 | Gesha                     12
12 | Pacamara                   8
13 | SL34                       8
14 | Arusha                     6
15 | Peaberry                   5
16 | Mandheling                 3
17 | Sumatra                    3
18 | Blue Mountain              2
19 | Ethiopian Yirgacheffe      2
20 | Java                       2
21 | Ruiru 11                   2
22 | Ethiopian Heirlooms        1
23 | Marigojipe                 1
24 | Moka Peaberry              1
25 | Pache Comun                1
26 | Sulawesi                   1
27 | Sumatra Lintong            1

I would now like to fill in the variety column with the top 4 levels 'Caturra', 'Bourbon', 'Typica','Catuai' and the remaining levels should be treated as Others.

The expected output is:

Out[9]: 
   | variety  count
-- + -------  -----
 0 | Caturra    256
 1 | Bourbon    226
 2 | Typica     211
 3 | Catuai      74
 4 | Others     236

[5 rows x 2 columns]

Case 2:

I have a datatable as,

DT_X_1 = dt.Frame({'variety': ['Bourbon',
  'Catimor',
  'Ethiopian Yirgacheffe',
  'Caturra',
  'Bourbon',
  'SL14',
  'Caturra',
  'Sumatra',
  'Bourbon',
  'Caturra',
  'SL34',
  'Hawaiian Kona',
  'Caturra',
  'Yellow Bourbon',
  'Yellow Bourbon',
  'Bourbon',
  'SL28',
  'Bourbon',
  'Caturra',
  'SL28',
  'Bourbon',
  'SL14',
  'Caturra',
  'Gesha',
  'Bourbon',
  'Catuai',
  'Caturra',
  'Bourbon',
  'Bourbon',
  'Hawaiian Kona']})

and it can be viewed as

Out[7]: 
   | variety              
-- + ---------------------
 0 | Bourbon              
 1 | Catimor              
 2 | Ethiopian Yirgacheffe
 3 | Caturra              
 4 | Bourbon              
 5 | SL14                 
 6 | Caturra              
 7 | Sumatra              
 8 | Bourbon              
 9 | Caturra              
10 | SL34                 
11 | Hawaiian Kona        
12 | Caturra              
13 | Yellow Bourbon       
14 | Yellow Bourbon       
15 | Bourbon              
16 | SL28                 
17 | Bourbon              
18 | Caturra              
19 | SL28                 
20 | Bourbon              
21 | SL14                 
22 | Caturra              
23 | Gesha                
24 | Bourbon              
25 | Catuai               
26 | Caturra              
27 | Bourbon              
28 | Bourbon              
29 | Hawaiian Kona        

[30 rows x 1 column]

The column variety has got about 12 distinct values as,

Out[8]: 
   | variety                count
-- + ---------------------  -----
 0 | Bourbon                    9
 1 | Catimor                    1
 2 | Catuai                     1
 3 | Caturra                    7
 4 | Ethiopian Yirgacheffe      1
 5 | Gesha                      1
 6 | Hawaiian Kona              2
 7 | SL14                       2
 8 | SL28                       2
 9 | SL34                       1
10 | Sumatra                    1
11 | Yellow Bourbon             2

[12 rows x 2 columns]

Here i wanted to collapse the field variety levels from 12 to 2 which are the most frequent ones.

the expected output would be,

Out[13]: 
   | variety
-- + -------
 0 | Bourbon
 1 | Others 
 2 | Others 
 3 | Caturra
 4 | Bourbon
 5 | Others 
 6 | Caturra
 7 | Others 
 8 | Bourbon
 9 | Caturra
10 | Others 
11 | Others 
12 | Caturra
13 | Others 
14 | Others 
15 | Bourbon
16 | Others 
17 | Bourbon
18 | Caturra
19 | Others 
20 | Bourbon
21 | Others 
22 | Caturra
23 | Others 
24 | Bourbon
25 | Others 
26 | Caturra
27 | Bourbon
28 | Bourbon
29 | Others 

[30 rows x 1 column]

Pasha · Accepted Answer · 2020-07-10T17:53:39.990

One way would be to first replace all variety values starting from 4th with string "Other" and then group by the variety:

>>> DT_X[4:, f.variety] = "Other"
>>> DT_X = DT_X[:, sum(f.count), by(f.variety)]
   | variety  count
-- + -------  -----
 0 | Bourbon    226
 1 | Catuai      74
 2 | Caturra    256
 3 | Other      236
 4 | Typica     211

[5 rows x 2 columns]

Another possibility is to take the original table, split it into 2 parts by rows, collapse the second part and rbind back to the original:

>>> dt.rbind(DT_X[:4, :], 
             dt.Frame(variety=["Other"], count=[DT_X[4:, f.count].sum1()]))
   | variety  count
-- + -------  -----
 0 | Caturra    256
 1 | Bourbon    226
 2 | Typica     211
 3 | Catuai      74
 4 | Other      236

[5 rows x 2 columns]

Case 2

You already created table of counts by variety, so now you just need to sort it by counts and select the 2 most frequent varieties:

>>> from datatable import by, sort, count, join, update, f, g
>>> counts = DT_X_1[:, count(), by(f.variety)]
>>> frequent = counts[-2:, :, sort(f.count)]
>>> frequent
   | variety  count
-- + -------  -----
 0 | Caturra      7
 1 | Bourbon      9

[2 rows x 2 columns]

(Alternatively, you can filter by count value).

Now, the next step is to join this table back to the original so that we have the indicator of which values are "frequent". The join operation can be combined with an update, so that in the same operation we set all fields that are not matched during join to "others":

>>> frequent.key = "variety"
>>> DT_X_1[g.variety==None, update(variety="others"), join(frequent)]
>>> DT_X_1
   | variety
-- + -------
 0 | Bourbon
 1 | others 
 2 | others 
 3 | Caturra
 4 | Bourbon
 5 | others 
 6 | Caturra
 7 | others 
 8 | Bourbon
 9 | Caturra
10 | others 
11 | others 
12 | Caturra
13 | others 
14 | others 
15 | Bourbon
16 | others 
17 | Bourbon
18 | Caturra
19 | others 
20 | Bourbon
21 | others 
22 | Caturra
23 | others 
24 | Bourbon
25 | others 
26 | Caturra
27 | Bourbon
28 | Bourbon
29 | others 

[30 rows x 1 column]

Wow, The answer for case - 2 is awesome, it's working as expected. and one small doubt: what's the purpose of g imported from datatable. i'm familiar with f, but not with g. — myamulla_ciencia, Jul 11 '20 at 10:09
`g` allows you to reference the second dataframe in the join; note that `f` is already referencing the first dataframe. — sammywemmy, Jul 11 '20 at 19:04

How to lump together factor levels of a string type column into another in pydatatable?

1 Answers1

Case 2