3

So I've essentially got this dataframe:

,club_name,tr_begin,year,ranking
0,ADO Den Haag,1357,2010,6.0
1,ADO Den Haag,1480,2011,15.0
2,ADO Den Haag,1397,2012,9.0
3,ADO Den Haag,1384,2013,9.0
4,ADO Den Haag,1451,2014,13.0

What I want to do is this, I want to go through every ranking and put them into a class based on it's value. So a ranking of 6 would go into class number 2 and a ranking 1 would go into class number 1. The conversion table is this:

if ranking > 0 and ranking =< 3:
    rank_class = 1
if ranking > 3 and ranking =< 6:
    rank_class = 2 

etc etc etc

This I would like to happen in multiples of 3 up until 18.

So my hoped output would be:

,club_name,tr_begin,year,ranking, ranking_class
0,ADO Den Haag,1357,2010,6.0, 2
1,ADO Den Haag,1480,2011,15.0, 5
2,ADO Den Haag,1397,2012,9.0, 3
3,ADO Den Haag,1384,2013,9.0, 3
4,ADO Den Haag,1451,2014,13.0, 5

I tried with the mask function, and by making a new dataframe and then merging, This worked but just seemed very sloppy. Is there some easy way to do this?

Thanks in advance

PEREZje
  • 2,272
  • 3
  • 9
  • 23

2 Answers2

3

Using pandas.cut, you can define iterables for your "bins" and "labels". This is simplified by the fact they can both be defined using range objects.

I recommend you convert your ranking series to int first; it may be affected by floating-point rounding which may yield undesirable results.

df = pd.read_csv('file.csv')

binrange = range(0, 19, 3)
labrange = range(1, 7)

df['ranking_class'] = pd.cut(df['ranking'], bins=binrange, labels=labrange)

print(df)

      club_name  tr_begin  year  ranking ranking_class
0  ADO Den Haag      1357  2010      6.0             2
1  ADO Den Haag      1480  2011     15.0             5
2  ADO Den Haag      1397  2012      9.0             3
3  ADO Den Haag      1384  2013      9.0             3
4  ADO Den Haag      1451  2014     13.0             5
jpp
  • 159,742
  • 34
  • 281
  • 339
1

I think integer division // would do it:

df.assign(ranking_class=(df.ranking // 3).astype(int))

      club_name  tr_begin  year  ranking  ranking_class
0  ADO Den Haag      1357  2010      6.0              2
1  ADO Den Haag      1480  2011     15.0              5
2  ADO Den Haag      1397  2012      9.0              3
3  ADO Den Haag      1384  2013      9.0              3
4  ADO Den Haag      1451  2014     13.0              4
Piotr
  • 2,029
  • 1
  • 13
  • 8
  • The only thing I worry about with this solution (and probably affects mine too) is floating point rounding, e.g. if `6.0` is stored as `6.00001` will this be correct? Maybe we have to convert to `int` first, with `np.isclose` to guarantee we get the right one. – jpp Jun 02 '18 at 13:46