Pandas DataFrame column value remapping

Question

Assuming the following DataFrame:

df = pd.DataFrame({'id': [8,16,23,8,23], 'count': [5,8,7,1,2]}, columns=['id', 'count'])

   id  count
0   8      5
1  16      8
2  23      7
3   8      1
4  23      2

...is there some Pandas magic that allows me to remap the ids so that the ids become sequential? Looking for a result like:

   id  count
0   0      5
1   1      8
2   2      7
3   0      1
4   2      2

where the original ids [8,16,23] were remapped to [0,1,2]

Note: the remapping doesn't have to maintain original order of ids. For example, the following remapping would also be fine: [8,16,23] -> [2,0,1], but the id space after remapping should be contiguous.

I'm currently using a for loop and a dict to keep track of the remapping, but it feels like Pandas might have a better solution.

behzad.nouri · Accepted Answer · 2015-12-20 00:20:39Z

3

use factorize:

>>> df
   id  count
0   8      5
1  16      8
2  23      7
3   8      1
4  23      2
>>> df['id'] = pd.factorize(df['id'])[0]
>>> df
   id  count
0   0      5
1   1      8
2   2      7
3   0      1
4   2      2

answered Dec 20, 2015 at 0:20

behzad.nouri

78.5k18 gold badges130 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andy Hayden · Accepted Answer · 2015-12-20 00:20:59Z

1

You can do this via a groupby's labels:

In [11]: df
Out[11]:
   id  count
0   8      5
1  16      8
2  23      7
3   8      1
4  23      2

In [12]: g = df.groupby("id")

In [13]: g.grouper.labels
Out[13]: [array([0, 1, 2, 0, 2])]

In [14]: df["id"] = g.grouper.labels[0]

In [15]: df
Out[15]:
   id  count
0   0      5
1   1      8
2   2      7
3   0      1
4   2      2

answered Dec 20, 2015 at 0:20

Andy Hayden

378k110 gold badges640 silver badges546 bronze badges

Comments

Stephen Rauch · Accepted Answer · 2017-09-16 03:08:12Z

0

This may be helpful to you.

x,y = pd.factorize(df['id'])
remap = dict(set(zip(list(x),list(y))))

edited Sep 16, 2017 at 3:08

Stephen Rauch♦

50.1k32 gold badges118 silver badges143 bronze badges

answered Sep 16, 2017 at 2:47

KKAKKOONG

1

Collectives™ on Stack Overflow

Pandas DataFrame column value remapping

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related