How to convert Pandas dataframe column into bin string data?

Question

I have a Pandas dataframe called odf that looks like this:

Customer         Employees
   A                 2
   B                 100
   C                 5
   D                 1000

I have created custom bins for the employee data:

df = odf['Employees']
bins = [0,5,1000]
df.value_counts(bins=bins)

(-0.001, 5.0]        2
(5.0,   1000]        2
Name:Employees, dtype: int64

now I'd like to 'join' this data but am unsure how to do this, or if there is an easier way to accomplish what I need. I want the end result to look like this:

  Customer         Employees    NewBinColumn
   A                 2          -0.001, 5.0
   B                 100         5.0,   1000
   C                 5          -0.001, 5.0
   D                 1000        5.0,   1000

That way I can see the bin column next to the original dataframe columns

here is what I tried that did not work:

ndf = odf.join(df, lsuffix='Employees', rsuffix='Employees', how='left')
ndf

And while it does join the two, what I get is this:

  Customer         EmployeesEmployees    Employees
   A                 2                     2
   B                 100                   100
   C                 5                     5
   D                 1000                  1000

If this was SQL I'd use a case statement to get the new column, but I was hoping there is an easier way to dynamically do this without writing out a really long statement.

Answer 1

It is not exactly the same formating that what you want, but using pd.cut on odf['Employees'] such as:

odf['NewBinColumn'] = pd.cut(odf['Employees'],bins)

will give:

  Customer  Employees NewBinColumn
0        A          2       (0, 5]
1        B        100    (5, 1000]
2        C          5       (0, 5]
3        D       1000    (5, 1000]