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]