How to Put Categorical Data in Bins

Question

I have the following Categorical Data:

['Self employed', 'Government Dependent',
 'Formally employed Private', 'Informally employed',
 'Formally employed Government', 'Farming and Fishing',
 'Remittance Dependent', 'Other Income',
 'Don't Know/Refuse to answer', 'No Income']

How do I put them in bins such that:

 ['Government Dependent','Formally employed Government','Formally 
  employed Private'] = 0

 ['Remittance Dependent', 'Informally employed','Self employed','Other Income'] = 1
 ['Dont Know/Refuse to answer', 'No Income','Farming and Fishing'] = 2

I already know of putting numerical data into categorical bins....can the reverse be done?

TRAIN = pd.read_csv("Train_v2.csv")
TRAIN['job_type'].unique()
output:
array(['Self employed', 'Government Dependent',
       'Formally employed Private', 'Informally employed',
       'Formally employed Government', 'Farming and Fishing',
       'Remittance Dependent', 'Other Income',
       'Dont Know/Refuse to answer', 'No Income'], dtype=object)

I am kinda new to stack overflow, so if there is any additional information needed please let me know. — Aina Emmanuel, Mar 15 '20 at 09:13
@jezrael Nope...it ends at ['Don't Know/Refuse to answer', 'No Income'] =2 — Aina Emmanuel, Mar 15 '20 at 09:31
@Aina Emmanuel adding to what jezrael said you should make sure that every value is classified as 0, 1, or 2. Otherwise, additional np.nan logic may be needed to accomodate for values that are not binned as 0, 1, or 2... if you could include that in the question that would be helpful. — David Erickson, Mar 15 '20 at 09:37
Okay..thanks.....i was actually going to do that in my main code....i just wanted to make the question as short as possible — Aina Emmanuel, Mar 15 '20 at 09:40

jezrael · Accepted Answer · 2020-03-15T09:36:02.043

Create dictionary first, change it by swapping and last use Series.map:

a = ['Self employed', 'Government Dependent',
       'Formally employed Private', 'Informally employed',
       'Formally employed Government', 'Farming and Fishing',
       'Remittance Dependent', 'Other Income',
       'Dont Know/Refuse to answer', 'No Income']

TRAIN = pd.DataFrame({'job_type':a})

#add another groups to dict
d = {0: ['Government Dependent','Formally employed Government','Formally employed Private'],
     1: ['Remittance Dependent', 'Informally employed'],
     2: ["Don't Know/Refuse to answer", 'No Income']}

#swap key values in dict
#http://stackoverflow.com/a/31674731/2901002
d1 = {k: oldk for oldk, oldv in d.items() for k in oldv}
TRAIN['new'] = TRAIN['job_type'].map(d1)
print (TRAIN)
                       job_type  new
0                 Self employed  NaN
1          Government Dependent  0.0
2     Formally employed Private  0.0
3           Informally employed  1.0
4  Formally employed Government  0.0
5           Farming and Fishing  NaN
6          Remittance Dependent  1.0
7                  Other Income  NaN
8    Dont Know/Refuse to answer  NaN
9                     No Income  2.0

If there are only 0, 1 and NaNs output working also numpy.select, but if many groups it is complicated and slow:

m1 = TRAIN['job_type'].isin(['Government Dependent','Formally employed Government','Formally employed Private'])
m2 = TRAIN['job_type'].isin(['Remittance Dependent', 'Informally employed'])
m3 = TRAIN['job_type'].isin(["Don't Know/Refuse to answer", 'No Income'])
TRAIN['new'] = np.select([m1, m2, m3], [0, 1, 2], np.nan)

Thanks alot....your solution worked just fine......really sorry i didn't pick yours as the final answer....both your solution and @David Erickson's where correct and i had to randomly select one...cheers — Aina Emmanuel, Mar 15 '20 at 09:58
@AinaEmmanuel - OK, it is not so problem, what is selected, but I suggest use `map` solution - for easy add new categories and fastest. — jezrael, Mar 15 '20 at 10:00

David Erickson · Answer 2 · 2020-03-15T09:39:24.647

You could do an np.where and make np.nan the value if it doesn't fall into category 0 or 1 or 2. More resources on np.where numpy.where() detailed, step-by-step explanation / examples:

list_0 = ['Government Dependent','Formally employed Government','Formally employed Private']
list_1 = ['Remittance Dependent', 'Informally employed']
list_2 = ['Don't Know/Refuse to answer', 'No Income']
TRAIN['job_type_bin'] = np.where(TRAIN['job_type'].isin(list_0), 0, np.nan)
TRAIN['job_type_bin'] = np.where(TRAIN['job_type'].isin(list_1), 1, np.nan)
TRAIN['job_type_bin'] = np.where(TRAIN['job_type'].isin(list_1), 2, np.nan)

How to Put Categorical Data in Bins

2 Answers2