Pfthb

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.

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]