CODEWITHSUNDEEP
pandas
Jivan
Jivan

Reputation: 23068

Incremental id based on another column's value

From this DataFrame:

car_id    month
93829     September
27483     April
48372     October
93829     December
93829     March
48372     February
27483     March

How to add a third column which is basically a new id for car, but an incremental one, like this:

car_id    month        new_incremental_car_id
93829     September    0
27483     April        1
48372     October      2
93829     December     0
93829     March        0
48372     February     2
27483     March        1

Currently I'm doing it by using groupby('car_id') to create a new DataFrame, to which I add an incremental column, which I then join back to the original DataFrame using car_id join key.

Is there a less cumbersome, more direct method to achieve this goal?

EDIT

The code I'm currently using:

cars_id = pd.DataFrame(list(car_sales.groupby('car_id')['car_id'].groups))
cars_id['car_short_id'] = cars_id.index
cars_id.set_index(0, inplace=True)
car_sales.join(cars_id, on='car_id', how='left')

Upvotes: 3

Views: 1076

Answers (2)

Zero
Zero

Reputation: 76917

Apart from pd.factorize you can

Use, map a dict constructed from unique values.

In [959]: df.car_id.map({x: i for i, x in enumerate(df.car_id.unique())})
Out[959]:
0    0
1    1
2    2
3    0
4    0
5    2
6    1
Name: car_id, dtype: int64

Or, using category type and codes but not in the same order.

In [954]: df.car_id.astype('category').cat.codes
Out[954]:
0    2
1    0
2    1
3    2
4    2
5    1
6    0
dtype: int8

Upvotes: 4

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210832

use factorize method:

In [49]: df['new_incremental_car_id'] = pd.factorize(df.car_id)[0].astype(np.uint16)

In [50]: df
Out[50]:
   car_id      month  new_incremental_car_id
0   93829  September                       0
1   27483      April                       1
2   48372    October                       2
3   93829   December                       0
4   93829      March                       0
5   48372   February                       2
6   27483      March                       1

In [51]: df.dtypes
Out[51]:
car_id                     int64
month                     object
new_incremental_car_id    uint16
dtype: object

Upvotes: 1

Related Questions