How to create rows for unique values in columns in pandas?

Question

I have a pandas dataframe with thousands of rows like so:

IntentID     IntentName         Query           Response
1            Intent Name 1      Query 1         Response1
2            Intent Name 1      Query 1         Response2
3            Intent Name 2      Query 2         Response3
4            Intent Name 2      Query 2         Response4
5            Intent Name 3      Query 3         Response5

I need all unique values in "IntentName" to have the same IntentID value like so:

IntentID     IntentName         Query           Response
1            Intent Name 1      Query 1         Response1
1            Intent Name 1      Query 1         Response2
2            Intent Name 2      Query 2         Response3
2            Intent Name 2      Query 2         Response4
3            Intent Name 3      Query 3         Response5

What is the easiest way to do this?

Code Different · Accepted Answer · 2019-12-03 02:33:20Z

2

Try this:

df['IntentID'] = df.groupby('IntentName') \
                    ['IntentID'].transform('first') \
                    .rank(method='dense') \
                    .astype('int')

How it works:

Group the rows by IntentName
For each group, keep the first IntentID
Rank those IntentIDs 1, 1, 2, 2, 3, etc. (method=dense)
Convert the ranks to int

answered Dec 3, 2019 at 2:33

Code Different

93.4k16 gold badges154 silver badges175 bronze badges

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

Comments

oppressionslayer · Accepted Answer · 2019-12-03 02:35:50Z

1

You can use regex:

df['IntentID'] = df.IntentName.apply(lambda x: re.search('(?P<num>\d+)',x).groups()[0])

output

  IntentID     IntentName    Query   Response
0        1  Intent Name 1  Query 1  Response1
1        1  Intent Name 1  Query 1  Response2
2        2  Intent Name 2  Query 2  Response3
3        2  Intent Name 2  Query 2  Response4
4        3  Intent Name 3  Query 3  Response5

answered Dec 3, 2019 at 2:35

oppressionslayer

7,2242 gold badges11 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to create rows for unique values in columns in pandas?

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related