I am trying to replicate countifs in excel to get a rank between two unique values that are listed in my dataframe. I have attached the expected output calculated in excel using countif and let/rank functions.

I am trying to generate "average rank of gas and coal plants" that takes the number from the "average rank column" and then ranks the two unique types from technology (CCGT or COAL) into two new ranks (Gas or Coal) so then I can get the relavant quantiles for this. In case you are wondering why I would need to do this seeing as there are only two coal plants, well when I run this model on a larger dataset it will be useful to know how to do this in code and not manually on my dataset.

Ideally the output will return two ranks 1-47 for all units with technology == CCGT and 1-2 for all units with technology == COAL.

I have tried to do it the same way I got average rank, which is a rank of the average of inputs in the dataframe but it doesn't seem to work with additional conditions.

Thank you!!

CodePudding user response：

import pandas as pd
df = pd.read_csv("gas.csv")
display(df['Technology'].value_counts())
print('------')
display(df['Technology'].value_counts()[0]) # This is how you access count of CCGT
display(df['Technology'].value_counts()[1])

Output:

CCGT    47
COAL     2
Name: Technology, dtype: int64

------

47

2

By the way: pd.cut or pd.qcut can be used to calculate quantiles. You don't have to manually define what a quantile is.

Refer to the documentation and other websites:

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.qcut.html

https://www.geeksforgeeks.org/how-to-use-pandas-cut-and-qcut/

There are many methods you can pass to rank. Refer to documentation:

1. https://pandas.pydata.org/docs/reference/api/pandas.Series.rank.html

df['rank'] = df.groupby("Technology")["Average Rank"].rank(method = "dense", ascending = True)
df

method{‘average’, ‘min’, ‘max’, ‘first’, ‘dense’}, default ‘average’

How to rank the group of records that have the same value (i.e. ties):

    average: average rank of the group

    min: lowest rank in the group

    max: highest rank in the group

    first: ranks assigned in order they appear in the array

    dense: like ‘min’, but rank always increases by 1 between groups.