Add a column at sub-level based on position of another sub-level column

user_10852466 0
Viewed 172

I have the following pandas Dataframe (df): 

quarters = ['first_q', 'second_q', 'third_q', 'fourth_q']
columns = pd.MultiIndex.from_product([quarters, ['points']])
df = pd.DataFrame([[5, 13, 6, 12], [7,5,5,14], [3,0,7,4]], index=['curry', 'lebron','stockton'], columns=columns)

Out[]:
         first_q   second_q    third_q    fourth_q
          points     points     points      points
curry       5          13          6          12
lebron      7           5          5          14
stockton    3           0          7           4

I would like to create a new column at level 1 (next to 'points'), containing the ranking of each player's (index) anotation (column, level 1 "points") at each quarter (column, level 0). In other words, the expected result is:

          first_q         second_q        third_q        fourth_q
       points ranking  points ranking  points ranking  points ranking
curry     5      2       13      1       6       2       12     2
lebron    7      1        5      2       5       3       14     1
stockton  3      3        0      3       7       1        4     3

The ranking level-1 column in the first_q level-0 column means that lebron made the highest points in the first quarter, followed by curry and stockton ended that quarter in third place.

I have managed to achieve this result with the following code: 

for quarter in quarters:
    df = df.sort_values([(quarter, 'points')], ascending=False)
    df.loc[:,(quarter,'ranking')] = np.arange(1, 4)
df = df[quarters]
df = df.reindex(['curry', 'lebron', 'stockton'])

However, I was trying to find a more elegant/idiomatic way to achieve this without the for iteration over quarters, but I haven't found any.

Does anyone know how to achieve this?

Thanks a lot in advance!

multi-index pandas python
1 Answers

Use rank and concat:

v = ((len(df) + 1 - df.rank(method='dense'))
        .rename({'points':'rank'}, axis=1)
        .astype(int))

pd.concat([df, v], axis=1).sort_index(axis=1)

         first_q      fourth_q      second_q      third_q     
          points rank   points rank   points rank  points rank
curry          5    2       12    2       13    1       6    2
lebron         7    1       14    1        5    2       5    3
stockton       3    3        4    3        0    3       7    1
user_49090870
Related