how to mapping string to numerical ID in each column of dataframe python3

user_5865579 0
Viewed 936

I have a dataframe:

import pandas as pd
d = {'user': ['bob','alice','bob'], 'item': 
['apple','coconut','pear']}
df = pd.DataFrame(data=d)




    user    item
0   bob     apple 
1   alice   coconut 
2   bob     pear

My goal is to map each string in each column to an increasing ID (start from 0) as

    user    item
0   0       0
1   1       1
2   0       2

For example, for column user, the [bob, alice] will map to [0,1]. The goal is to save memory for dataframe.

Moreover, is it possible to specify the column to map? For example, only mapping the user column. Thanks

dataframe mapping python string
4 Answers

You can use a combination of .groupby() and .ngroup() to replace the names in each column with a unique number.

df['user'] = df.groupby(['user']).ngroup()
df['item'] = df.groupby(['item']).ngroup()
user_75177240

You should first build a map from users to integers, and then make the substitution with Pandas built-in pandas.Series.map:

import pandas as pd

d = {'user': ['bob','alice','bob'],
     'item': ['apple','coconut','pear']}
df = pd.DataFrame(data = d)

unique_users = df.user.unique()
user_map = {u: i for i, u in enumerate(unique_users)}
df.user = df.user.map(user_map)
user_26433640

You can try this:

import pandas as pd
d = {'user': ['bob','alice','bob'], 'item': 
['apple','coconut','pear']}
df = pd.DataFrame(data=d)
col_user = df['user'].unique()
col_item = df['item'].unique()
d_user = pd.Series(range(len(col_user)), index = col_user).to_dict()
d_item = pd.Series(range(len(col_item)), index = col_item).to_dict()
df = df.replace({'user': d_user, 'item': d_item}) 
df
user_114093210

SKLearn has a library for this that will transform and inverse transform pandas series

>>> from sklearn.preprocessing import LabelEncoder
>>> import pandas as pd
>>> import numpy as np
>>>
>>> df = pd.DataFrame(data = {
... 'user': ['bob','alice','bob'], 'item': ['apple','coconut','pear']
... })
>>>
>>> le = LabelEncoder()
>>> le.fit_transform(df["user"])
array([1, 0, 1])
>>> le.inverse_transform(np.array([1,0,1]))
array(['bob', 'alice', 'bob'], dtype=object)
user_129580020
Related