I have a dataset, df, that looks like this:

WhereQTDPERCAPITA is simply Quantity/Population. I have multiple cities (4149 to be more precise).

The quantities change according to every month, and so does the population.

I would like to create a new variable that serve as a ranking, ranging from [0,1], where 0 is the city with the lowest QTDPERCAPITA in that month, and 1 is the city with the most quantity per capita in that month. Essentially, I want to create a new column that looks like this:

How can I create this column such that the RANKING changes every month? I was thinking of a for loop that extracts the QTDPERCAPITA for every city on every unique date, and creates a new column, df['RANKING'] with the same date and city.

CodePudding user response：

You can use:

# MinMax scaler: (rank - min) / (max - min)
ranking = lambda x: (x.rank() - 1) / (len(x) - 1)

# Rank between [0, 1] -> 0 the lowest, 1 the highest
df['RANKING'] = df.groupby('Date')['QTDPERCAPITA'].apply(ranking)

# Rank between [1, 4149] -> 1 the lowest, 4149 the highest
# df['RANKING'] = df.groupby('Date')['QTDPERCAPITA'].rank('dense')

Output:

CodePudding user response：

Try this:

# get unique values for each month
months = list(set(df['Date'].values))

# initialze a new column the length of our dataframe
rank_col = [None] * 9

for month in months:
  # get all the QTDPERCAPITA values for current month
  month_qtdpc = df[df['Date'] == month]['QTDPERCAPITA']

  # normalize such that best city has 1 and worst has 0 for this month
  max = month_qtdpc.max()
  min = month_qtdpc.min()
  rankings = (month_qtdpc - min) / (max - min)

  # insert each rank value at the proper index in our list 
  for i, rank in rankings.iteritems(): 
    rank_col[i] = rank

# add it as a column to the dataframe
df['RANKING'] = rank_col

Note: This will give you the score, not just the rank. For example, with three cities the output would be (fake data):

With Corralien's answer, it will give you true normalized rankings: