Label encoding across multiple columns with same attributes in sckit-learn

Question

If I have two columns as below:

Origin  Destination  
China   USA  
China   Turkey  
USA     China  
USA     Turkey  
USA     Russia  
Russia  China  

How would I perform label encoding while ensuring the label for the Origin column matches the one in the destination column i.e

Origin  Destination  
0   1  
0   3  
1   0  
1   0  
1   0  
2   1  

If I do the encoding for each column separately then the algorithm will see the China in column1 as different from column2 which is not the case

Answer 1

pandas Method

You could create a dictionary of {country: value} pairs and map the dataframe to that:

country_map = {country:i for i, country in enumerate(df.stack().unique())}

df['Origin'] = df['Origin'].map(country_map)    
df['Destination'] = df['Destination'].map(country_map)

>>> df
   Origin  Destination
0       0            1
1       0            2
2       1            0
3       1            2
4       1            3
5       3            0

sklearn method

Since you tagged sklearn, you could use LabelEncoder():

from sklearn.preprocessing import LabelEncoder
le= LabelEncoder()
le.fit(df.stack().unique())

df['Origin'] = le.transform(df['Origin'])
df['Destination'] = le.transform(df['Destination'])

>>> df
   Origin  Destination
0       0            3
1       0            2
2       3            0
3       3            2
4       3            1
5       1            0

To get the original labels back:

>>> le.inverse_transform(df['Origin'])
# array(['China', 'China', 'USA', 'USA', 'USA', 'Russia'], dtype=object)

Answer 2

stack

df.stack().pipe(lambda s: pd.Series(pd.factorize(s.values)[0], s.index)).unstack()

   Origin  Destination
0       0            1
1       0            2
2       1            0
3       1            2
4       1            3
5       3            0

---

factorize with reshape

pd.DataFrame(
    pd.factorize(df.values.ravel())[0].reshape(df.shape),
    df.index, df.columns
)

   Origin  Destination
0       0            1
1       0            2
2       1            0
3       1            2
4       1            3
5       3            0

---

np.unique and reshape

pd.DataFrame(
    np.unique(df.values.ravel(), return_inverse=True)[1].reshape(df.shape),
    df.index, df.columns
)

   Origin  Destination
0       0            3
1       0            2
2       3            0
3       3            2
4       3            1
5       1            0

---

Disgusting Option

I couldn't stop trying stuff... sorry!

df.applymap(
    lambda x, y={}, c=itertools.count():
        y.get(x) if x in y else y.setdefault(x, next(c))
)

   Origin  Destination
0       0            1
1       0            3
2       1            0
3       1            3
4       1            2
5       2            0

---

As pointed out by cᴏʟᴅsᴘᴇᴇᴅ

You can shorten this by assigning back to dataframe

df[:] = pd.factorize(df.values.ravel())[0].reshape(df.shape)

Answer 3

You can using replace

df.replace(dict(zip(np.unique(df.values),list(range(len(np.unique(df.values)))))))
   Origin  Destination
0       0            3
1       0            2
2       3            0
3       3            2
4       3            1
5       1            0

Succinct and nice answer from Pir

df.replace((lambda u: dict(zip(u, range(u.size))))(np.unique(df)))

And

df.replace(dict(zip(np.unique(df), itertools.count())))