Display the first 5 largest values of a DataFrame for each month

user_13613718 0
Viewed 1925

I'm trying to work on a dataframe with a lot of columns (505) and I want to select only the top 5 values for each month. You'll find below the link of an image of my DataFrame.

link photo

Here is the sample:

  Dates         1        2       3           4       5     6
2002-07-31  -31.710916  NaN  -5.208684  -29.773404  NaN -7.308558   
2002-08-31  -44.941351  NaN   3.665286  -23.987135  NaN 3.134669    
2002-09-30  -36.725548  NaN   4.114474  -19.536571  NaN -0.986986   
2002-10-31  -25.377286  NaN  -0.486158  -5.887594   NaN -0.787117   
2002-11-30  19.766328   NaN  -5.298877  -10.672174  NaN -21.057946  
2002-12-31  1.996514    NaN  -7.570497  -9.257122   NaN -19.630112  
2003-01-31  -0.366083   NaN -14.124492  -5.434475   NaN -8.053424   
2003-02-28  -17.869297  NaN -20.075997  1.009837    NaN -11.616974

How can I do it? I already tried with df.max(axis=1) but I would like to add 4 other values after the maximum. Thanks for your help

pandas time-series dataframe python
3 Answers
user_90241230

I assume that you want the max 5 columns for each row, since that is the way I interpret your question. The following picks the max 2 rows in your example input, since it has only 4 non-nan columns.

import io
import re
import pandas as pd


# First read in the data you supplied. 
data=io.StringIO(re.sub(" +","\t",
"""Dates         1        2       3           4       5     6
2002-07-31  -31.710916  NaN  -5.208684  -29.773404  NaN -7.308558
2002-08-31  -44.941351  NaN   3.665286  -23.987135  NaN 3.134669
2002-09-30  -36.725548  NaN   4.114474  -19.536571  NaN -0.986986
2002-10-31  -25.377286  NaN  -0.486158  -5.887594   NaN -0.787117
2002-11-30  19.766328   NaN  -5.298877  -10.672174  NaN -21.057946
2002-12-31  1.996514    NaN  -7.570497  -9.257122   NaN -19.630112
2003-01-31  -0.366083   NaN -14.124492  -5.434475   NaN -8.053424
2003-02-28  -17.869297  NaN -20.075997  1.009837    NaN -11.616974"""))
df = pd.read_csv(data,sep="\t")

# Then we preprocess the data, so it is in a long format instead of a wide
df = df.melt(id_vars='Dates',var_name='Column_name',value_name='Value')

# Finally extract the top 2 values for each date, but first set the index so the output knows what column the input came from
print(df.set_index('Column_name').groupby('Dates')['Value'].apply(lambda grp: grp.nlargest(2)))

and the output is 

Dates       Column_name
2002-07-31  3              -5.208684
            6              -7.308558
2002-08-31  3               3.665286
            6               3.134669
2002-09-30  3               4.114474
            6              -0.986986
2002-10-31  3              -0.486158
            6              -0.787117
2002-11-30  1              19.766328
            3              -5.298877
2002-12-31  1               1.996514
            3              -7.570497
2003-01-31  1              -0.366083
            4              -5.434475
2003-02-28  4               1.009837
            6             -11.616974
Name: Value, dtype: float64

It is tough to give a more suitable answer unless you become more explicit with exactly what output you desire.

user_40505100

You can try this:

df['Dates'] = pd.to_datetime(df['Dates'])
df = df.groupby(pd.Grouper(key='Dates', freq='1M'))
df2 = df.apply(lambda x: x.sort_values(['1', '2', '3', '4', '5', '6'], ascending=False))
df3 = df2.reset_index(drop=True)
print(df3.groupby(pd.Grouper(key='Dates', freq='1M')).head(5))

Output:

        Dates          1   2          3          4   5          6
0  2002-07-31 -31.710916 NaN  -5.208684 -29.773404 NaN  -7.308558
1  2002-08-31 -44.941351 NaN   3.665286 -23.987135 NaN   3.134669
2  2002-09-30 -36.725548 NaN   4.114474 -19.536571 NaN  -0.986986
3  2002-10-31 -25.377286 NaN  -0.486158  -5.887594 NaN  -0.787117
4  2002-11-30  19.766328 NaN  -5.298877 -10.672174 NaN -21.057946
5  2002-12-31   1.996514 NaN  -7.570497  -9.257122 NaN -19.630112
6  2003-01-31  -0.366083 NaN -14.124492  -5.434475 NaN  -8.053424
7  2003-02-28 -17.869297 NaN -20.075997   1.009837 NaN -11.616974
8  2003-02-28 -18.869297 NaN -20.075997   1.009837 NaN -11.616974
9  2003-02-28 -19.869297 NaN -20.075997   1.009837 NaN -11.616974
10 2003-02-28 -20.869297 NaN -20.075997   1.009837 NaN -11.616974
11 2003-02-28 -21.869297 NaN -20.075997   1.009837 NaN -11.616974
user_61683230
Related