python pandas relabelling values by value counts

user_7728410 0
Viewed 51

Given the following example:

example = pd.DataFrame({'y':[1,1,1,1,1,1,1,1,1,1,2,2,2,2,0,0,-1,-1,-1]})

I would like to relabel the values by their frequency counts, in descending order. So I want the value (e.g. 1) with the largest number of cases to be replaced by 0, then the next largest bin with 1, and so on through all of the values. The caveat is I want to ignore the cases that take on values of -1. If I run value_counts(), I can see this:

y 
 1    10
 2     4
-1     3
 0     2
dtype: int64

But I would like a pythonic and non-hacky/clean solution to create the following:

    y
0   0
1   0
2   0
3   0
4   0
5   0
6   0
7   0
8   0
9   0
10  1
11  1
12  1
13  1
14  2
15  2
16 -1
17 -1
18 -1

y 
 0    10
 1     4
-1     3
 2     2
dtype: int64

(ideally I retain the old column as well for good record keeping). I could loop over each value, check that it is not -1, then value_counts() and replace it by the iteration number, but this feels quite high maintenance. Is there a clean way to achieve it?

pandas python
1 Answers

Use Series.map by dictionary created by index from Series after Series.value_counts without -1:

s = example['y'].value_counts().drop(-1)
d = {v:k for k, v in dict(enumerate(s.index)).items()}

Or:

s = example['y'].value_counts().drop(-1)
d = dict(zip(s.index, range(len(s))))

m = example['y'].ne(-1)
example.loc[m, 'y'] = example.loc[m, 'y'].map(d)

print (example)
  y
0   0
1   0
2   0
3   0
4   0
5   0
6   0
7   0
8   0
9   0
10  1
11  1
12  1
13  1
14  2
15  2
16 -1
17 -1
18 -1

Another idea is add -1 with value -1 to dictionary:

s = example['y'].value_counts().drop(-1)
d = {**{-1:-1}, **dict(zip(s.index, range(len(s))))}

example['y'] = example['y'].map(d)
user_29010020
Related