How to change all values of a column into unique integers?

user_13720594 0
Viewed 2194

I'm working on a chess dataset, where I got a column for move_1, move_2, move_3 and so on... each of these columns has the 'play code' made by the player. Each row is for a specific game and there are about 1000 rows.

  result move_1 move_2 move_3 ...
      1     e4     d4    Nc3  ...    
      1     d4    Nf3     c4  ...
      1     e4     d3    Nd2  ...
      1     d4     c4     d5  ...
      0     d4     c4    Nc3  ...

keeps going down...

what is the best way for me to change each unique value in each row/column to an assigned integer so I can do some analysis on it? I've done the following:

chess_df['move_2'].replace(to_replace=['Nf3', 'c4', 'd3','d4', 'Nc3'],
           value= [3, 2, 4, 1, 7],
           inplace=True)

where each 'play code' has it's assigned integer. So, 'd4' = 1, 'c4 = 2 and so on, on ALL rows.

Problem is, this is NOT a great way of doing it since I need to find each and every unique code and replace it for a unique integer by columns... This will take ages, and it will be really confusing.

What I want is, move_1 column got 1000 rows and 45 unique 'play codes'. So each play code in it would become a number ranging from 1-45.

move_2 column has 1000 rows with 89 unique values where 25 of them are also in move_1 column. So I need to assign the 25 numbers already assigned in move_1 column + 64 new numbers. And so on...

So my question is:

How can I through Python code, create something that would automate the process of changing the 'play code' into a unique integer on all rows and columns?

pandas python loops
4 Answers

Does this help? 

unique_vals = chess_df['move_2'].unique()
chess_df['move_2'].replace(to_replace=unique_vals,
           value= list(range(len(unique_vals))),
           inplace=True)
user_82933090

A simple alternative is to stack everything into one Series, rank it, then unstack back. This way the rank is applied to the entire DataFrame. Here "d4" becomes 6 regardless of where it occurs. 

df.filter(like='move_').stack().rank(method='dense').atype(int).unstack()
#   move_1  move_2  move_3
#0       8       6       1
#1       6       3       4
#2       8       5       2
#3       6       4       7
#4       6       4       1

Alternatively you could use the return_inverse argument of np.unique and re-create the DataFrame.

arr = df.filter(like='move')
pd.DataFrame(np.unique(arr, return_inverse=True)[1].reshape(arr.shape),
             index=arr.index,
             columns=arr.columns)

#   move_1  move_2  move_3
#0       7       5       0
#1       5       2       3
#2       7       4       1
#3       5       3       6
#4       5       3       0
user_43333590

This would be my approach:

import pandas as pd
df = pd.DataFrame({'Move_1':['a1','b2','c3','c4'],
                   'Mode_2':['a1','f5','h6','b2']})

replacers = {k:list(set([x[0] for x in df.values.reshape(-1,1)])).index(k) for k in list(set([x[0] for x in df.values.reshape(-1,1)]))}
df_new = df.replace(replacers)

Output:

   Move_1  Mode_2
0       0       0
1       5       3
2       1       4
3       2       5

Essentially we are creating a dictionary with the chess positions as key and a unique number as value. This will be then used in df.replace() to perform the replacement across all the values in the df.

user_118970070

This answer accounts all moves in move_1, then move_2 and so on (I assume this is the way you described in question) and finds unique moves in ALL moves in the order mentioned and assigns them integer values:

moves = chess_df.loc[:,chess_df.columns!='result'].stack().sort_index(level=1).unique()
chess_df.replace(to_replace=moves,value=list(range(len(moves))),inplace=True)

In other words, it assigns integers to move_1 first, then move_2 (accounting for repetitions) and so on.

input/output for the example in question:

   result move_1 move_2 move_3
0       1     e4     d4    Nc3
1       1     d4    Nf3     c4
2       1     e4     d3    Nd2
3       1     d4     c4     d5
4       0     d4     c4    Nc3

   result  move_1  move_2  move_3
0       1       0       1       5
1       1       1       2       4
2       1       0       3       6
3       1       1       4       7
4       0       1       4       5
user_49759810
Related