["python","pandas"]
Convert categorical data in pandas dataframe

I have a dataframe with this type of data (too many columns): col1 int64 col2 int64 col3 category col4 category col5 category Columns look like this: Name: col3, dtype: category Categories (8, object): [B, C, E, G, H, N, S, W] I want to convert all the values in each column to integer like this: [1, 2, 3, 4, 5, 6, 7, 8] I solved this for one column by this: dataframe['c'] = pandas.Categorical.from_array(dataframe.col3).codes Now I have two columns in my dataframe - old col3 and new c and need to drop old columns. That's bad practice. It works but in my dataframe there are too many columns and I don't want do it manually. How can I do this more cleverly?
First, to convert a Categorical column to its numerical codes, you can do this easier with: dataframe['c'].cat.codes. Further, it is possible to select automatically all columns with a certain dtype in a dataframe using select_dtypes. This way, you can apply above operation on multiple and automatically selected columns. First making an example dataframe: In [75]: df = pd.DataFrame({'col1':[1,2,3,4,5], 'col2':list('abcab'), 'col3':list('ababb')}) In [76]: df['col2'] = df['col2'].astype('category') In [77]: df['col3'] = df['col3'].astype('category') In [78]: df.dtypes Out[78]: col1 int64 col2 category col3 category dtype: object Then by using select_dtypes to select the columns, and then applying .cat.codes on each of these columns, you can get the following result: In [80]: cat_columns = df.select_dtypes(['category']).columns In [81]: cat_columns Out[81]: Index([u'col2', u'col3'], dtype='object') In [83]: df[cat_columns] = df[cat_columns].apply(lambda x: x.cat.codes) In [84]: df Out[84]: col1 col2 col3 0 1 0 0 1 2 1 1 2 3 2 0 3 4 0 1 4 5 1 1 Note: NaN becomes -1 This method is fast because the relationship between code and category is readily available and do not need to be computed.