Create new columns which show values based on ranking of other columns python

user_7307875 0
Viewed 261

I have a dataframe with some dates as rows and values in columns. To have an idea the df looks like the below:

print(df1)

            c1  c2  c3  c4
12/12/2016  38  10   1   8
12/11/2016  44  12  17  46
12/10/2016  13   6   2   7
12/09/2016   9  16  13  26

I want to create a rule such that it ranks each row in df1 and create another dataframe where it stores some constant values. For example to the 2 highest values in each row it assigns the value k = 5 and for the lowest 2 values it shows the value y = -9

What I would like to obtain is the following df:

            c1  c2  c3  c4
12/12/2016  5    5  -9  -9
12/11/2016  5  -9   -9   5
12/10/2016  5  -9   -9   5
12/09/2016  -9  5   -9   5

I thought about using np.partition on df1 but I am stuck on how to create the new dataframe. Any hints is highly appreciated!

Thanks!

partition pandas partitioning dataframe python
2 Answers

Use rank with numpy.where and DataFrame contructor:

arr = np.where(df.rank(axis=1, method='dense') > 2, 5, -9)

df = pd.DataFrame(arr, index=df.index, columns=df.columns)
print (df)
            c1  c2  c3  c4
12/12/2016   5   5  -9  -9
12/11/2016   5  -9  -9   5
12/10/2016   5  -9  -9   5
12/09/2016  -9   5  -9   5
user_29010020

Here's a NumPy solution:

df.iloc[:] = np.where(df.values.argsort(1).argsort(1) > 1, 5, -9)

print(df)

            c1  c2  c3  c4
12/12/2016   5   5  -9  -9
12/11/2016   5  -9  -9   5
12/10/2016   5  -9  -9   5
12/09/2016  -9   5  -9   5
user_92095460
Related