How to turn a df into a dictionary of lists consisting of index values in python pandas

Question

I have the following df that ranks players by week (represented by each column)

   1    2    3    4
0 Bob  Jim  Pam  Pam
1 Amy  Amy  Jim  Bob
2 Jim  Bob  Bob  Amy
3 Pam  Pam  Amy  Jim

For example, Bob ranks 1st in week 1, 3rd in week 2, etc... I would like to turn this df into a dictionary that consists of a list of numeric ranks (ie the index value for each week) for each player, like so:

{'Bob': [1,
  3,
  3,
  2],
 'Jim': [3,
  1,
  2,
  4],
 'Pam': [4,
  4,
  1,
  1],
 'Amy': [2,
  2,
  4,
  3]}

I know I can do something with index to get each team's index for each week, but first I would need to

1. Shift the index so it starts at 1 (I was able to do this with df.index += 1)
2. Make a key for each team with the value pair being a list of their ranks

I am pretty stumped on part 2 but there has to be a pretty simple solution to this that I am missing. Any help would be appreciated. Thanks!

Answer 1

We can transpose with T and stack to get rank in a column as an index, then swap indexes and values in a new dataframe, groupby and apply(list) to get all ranks for each person as a list, and finally convert to dictionary with to_dict:

s = df.T.stack().droplevel(0)
pd.DataFrame(
    s.index + 1,
    index=s.values).groupby(level=0)[0].apply(list).to_dict()

Output:

{'Amy': [2, 2, 4, 3],
 'Bob': [1, 3, 3, 2],
 'Jim': [3, 1, 2, 4],
 'Pam': [4, 4, 1, 1]}

Answer 2

First use .unstack.reset_index(-1) to combine all the columns in one column. Then groupby using the column containing player name and then use aggregate with list function.

Use:

stacked_df = df.unstack().reset_index(-1)
stacked_df.level_1 += 1
>>> stacked_df
     level_1    0
1        1  Bob
1        2  Amy
1        3  Jim
1        4  Pam
2        1  Jim
...
3        4  Amy
4        1  Pam
4        2  Bob
4        3  Amy
4        4  Jim

out = stacked_df.groupby([0])['level_1'].agg(list).to_dict()

Output:

>>> out
{'Amy': [2, 2, 4, 3], 'Bob': [1, 3, 3, 2],
'Jim': [3, 1, 2, 4], 'Pam': [4, 4, 1, 1]}

Answer 3

rank = dict()

for col in df.columns:
  for element in df[col]:
    if element not in rank.keys():
      rank[element] =[]
      rank[element].append(df[col].to_list().index(element)+1)
    else:
      rank[element].append(df[col].to_list().index(element)+1)

print(rank)

{'Bob': [1, 3, 3, 2],
 'Jim': [3, 1, 2, 4],
 'Pam': [4, 4, 1, 1],
 'Amy': [2, 2, 4, 3] }