Combining two columns to generate index in pd Dataframe

Question

Firstly, excuses if this has been asked and answered. I have looked here and here, went through the titles of recommended duplicates and try to use search engines, but cannot seem to come up with the right keyword.

problem

My problem is the following: given a dataframe with two "identifier" columns, I want to create an index that uniquely describes each combination of values in the two columns:

For instance: column 'a' has value 0, and column 'b' has value '0' and this should get index number 1. Same combinations should map to the same value.

approach

df = pd.DataFrame({
    'a': np.random.randint(0,3,10),
    'b': np.random.randint(0,3,10),
    'c': np.random.randint(0,10,10)
})

mapping = [(*key, i+1) for i, key in enumerate(df.groupby(by=['a', 'b']).groups.keys())]
crutch = pd.DataFrame(mapping, columns=['a', 'b', 'new_index'])
df = df.merge(crutch, left_on=['a', 'b'], right_on=['a', 'b'])

This works, but it seems like there should be something built into pandas that I am missing.

question

So, is there something built into pandas that would help and that I could not figure out?

thanks

Help is greatly appreciated.

I want to use the 1-index downstream, so thought I would add it there. But you are correct of course. — warped
– warped, Commented Feb 14, 2020 at 14:48

ansev · Accepted Answer · 2020-02-14 14:46:01Z

3

IIUC, groupby.ngroup

df['new_index'] = df.groupby(['a','b']).ngroup().add(1)
print(df)
   a  b  c  new_index
0  2  1  4          6
1  1  1  5          3
2  1  1  8          3
3  1  1  2          3
4  2  2  8          7
5  2  2  5          7
6  2  0  8          5
7  0  1  7          1
8  1  2  5          4
9  0  2  5          2

answered Feb 14, 2020 at 14:46

ansev

31k5 gold badges21 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

Combining two columns to generate index in pd Dataframe

problem

approach

question

thanks

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

problem

approach

question

thanks

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related