Replace column values in a large dataframe

user_11616334 0
Viewed 64

I have a dataframe that has similar ids with spatiotemporal data like below:

car_id    lat long
 xxx      32  150
 xxx      33  160
 yyy      20  140
 yyy      22  140
 zzz      33   70
 zzz      33   80
  .        .    .

I want to replace car_id with car_1, car_2, car_3, ... However, my dataframe is large and it's not possible to do it manually by name so first I made a list of all unique values in the car_id column and made a list of names that should be replaced with:

u_values = [i for i in df['car_id'].unique()]
r = ['car'+str(i) for i in range(len(u_values))]

Now I'm not sure how to replace all unique numbers in car_id column with list values so the result is like this:

car_id    lat long
 car_1     32  150
 car_1     33  160
 car_2     20  140
 car_2     22  140
 car_3     33   70
 car_3     33   80
     .       .   .
large-data pandas dataframe python replace
3 Answers

Create a mapping from u_values to r and map it to car_id column. Also simplify the definition of u_values and r by using tolist() method and f-strings, respectively.

u_values = df['car_id'].unique().tolist()
r = [f'car_{i}' for i in range(len(u_values))]
mapping = pd.Series(r, index=u_values)
df['car_id'] = df['car_id'].map(mapping)

That said, it seems vectorized string concatenation is enough for this task. factorize() method encodes the strings.

df['car_id'] = 'car_' + pd.Series(df['car_id'].factorize()[0], dtype='string')

When I timed some these methods (I omitted Juan Manuel Rivera's solution because replace is very slow and the code takes forever on larger data), the map() implementation that built on OP's code turned out to be the fastest.

The factorize() implementation, while concise, is not fast after all. Also I agree with pasnik that their solution is the easiest to read.

# a dataframe with 500k rows and 100k unique car_ids
df = pd.DataFrame({'car_id': np.random.default_rng().choice(100000, size=500000)})

%timeit u_values = df['car_id'].unique().tolist(); r = [f'car_{i}' for i in range(len(u_values))]; mapping = pd.Series(r, index=u_values); df.assign(car_id=df['car_id'].map(mapping))
# 136 ms ± 2.92 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

%timeit df.assign(car_id = 'car_' + pd.Series(df['car_id'].factorize()[0], dtype='string'))
# 602 ms ± 19.3 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

%timeit r={k:'car_{}'.format(i) for i,k in enumerate(df['car_id'].unique())}; df.assign(car_id=df['car_id'].map(r))
# 196 ms ± 3.02 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
user_191231030

The answers so far seem a little complicated to me, so here's another suggestion. This creates a dictionary that has the old name as the keys and the new name as the values. That can be used to map the old values to new values.

r={k:'car_{}'.format(i) for i,k in enumerate(df['car_id'].unique())}
df['car_id'] = df['car_id'].map(r)

edit: the answer using factorize is probably better even though I think this is a bit easier to read

user_121332800

It may be easier if you use a dictionary to maintain the relation between each unique value (xxxx,yyyy...) and the new id you want (1, 2, 3...)

newIdDict={}
idCounter=1
for i in df['Car id'].unique():
   if i not in newIdDict:
     newIdDict[i] = 'car_'+str(idCounter)
     idCounter += 1

Then, you can use Pandas replace function to change the values in car_id column:

df['Car id'].replace(newIdDict, inplace=True)

Take into account that this will change ALL the xxxx, yyyy in your dataframe, so if you have any xxxx in lat or long columns it will also be modified

user_164532370
Related