Column ranking and column selection

Question

If I had a table:

abc 15 15 5 20 10 7 25 30 9 

And I wanted to do two things: 1) Select the column with the largest value along the axis and assign it to the column. 2) Take the value and assign it to another column, for example:

 abc 1st 1st_value 2nd 2nd_value 3rd 3rd_value 15 15 5 a/b 15 c 5 NaN NaN 20 10 7 a 20 b 10 c 7 25 30 9 b 30 a 25 c 9 

Is it possible?

Answer 1

I can suggest you solve it as follows:

 import pandas as pd import numpy as np df = pd.DataFrame([{'a': 15, 'b': 15, 'c': 5}, {'a': 20, 'b': 10, 'c': 7}, {'a': 25, 'b': 30, 'c': 9}]) ext = {0: 'st', 1: 'nd', 2: 'rd'} cols = df.columns def make_ranking(row, rank=0, is_value=False): values = list(row[cols]) sorted_values = sorted(set(values), reverse=True) value = sorted_values[rank] if len(sorted_values) > rank else np.nan if not is_value: items = [k for k, v in enumerate(values) if v == value] value = '/'.join([cols[item] for item in items]) or np.nan return value for i in range(len(cols)): df[str(i+1)+ext[i]] = df.apply(make_ranking, args=(i, False, ), axis=1) df[str(i+1)+ext[i]+'_value'] = df.apply(make_ranking, args=(i, True, ), axis=1) print(df) 

Output:

  abc 1st 1st_value 2nd 2nd_value 3rd 3rd_value 0 15 15 5 a/b 15 c 5 NaN NaN 1 20 10 7 a 20 b 10 c 7 2 25 30 9 b 30 a 25 c 9 

Answer 2

 df_sorted = df.apply(lambda row: sorted(set(row), reverse=True) + [None]*(len(row)-len(set(row))), axis=1) >>> df_sorted abc 0 15 5 NaN 1 20 10 7 2 30 25 9 

Rename the columns if you want:

 df_sorted.rename(columns={'a': '1st_value', 'b': '2nd_value', 'c': '3rd_value'}, inplace=True) >>> df_sorted 1st_value 2nd_value 3rd_value 0 15 5 NaN 1 20 10 7 2 30 25 9 

The concat is original and sorted if you want:

 df_concat = pd.concat([df, df_sorted], axis=1) >>> df_concat abc 1st_value 2nd_value 3rd_value 0 15 15 5 15 5 NaN 1 20 10 7 20 10 7 2 25 30 9 30 25 9