2

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

            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 am trying to find a way to iterate over each row and multiply only the top 2 values times k = 3. The results should be in a new column of the existing df. Any suggestion or hint is highly appreciated!

Thanks!

clu
  • 117
  • 1
  • 6
  • I am really sorry I forgot to say that the new column to be created is the sum of the top 2 values multiplied times 3. For the first row '12/12/2016' I should get (38*3)+(8*3) = 138 Again apologies for not having specified this earlier. – clu Oct 22 '18 at 15:05
  • 1
    yes! sorry for the confusion, definitely (38*3)+(10*3) = 144 – clu Oct 22 '18 at 15:24

3 Answers3

4

Using update after groupby + nlargest 

df.update(df.stack().groupby(level=0).nlargest(2).mul(k).reset_index(level=0,drop=True).unstack())
df
Out[1036]: 
               c1    c2  c3     c4
12/12/2016  114.0  30.0   1    8.0
12/11/2016  132.0  12.0  17  138.0
12/10/2016   39.0   6.0   2   21.0
12/09/2016    9.0  48.0  13   78.0
BENY
  • 317,841
  • 20
  • 164
  • 234
2

nlargest

df.assign(newcol=df.apply(sorted, 1).iloc[:, -2:].sum(1) * 3)

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

partition

df.assign(newcol=np.partition(df, -2)[:, -2:].sum(1) * 3)

            c1  c2  c3  c4  newcol
12/12/2016  38  10   1   8     144
12/11/2016  44  12  17  46     270
12/10/2016  13   6   2   7      60
12/09/2016   9  16  13  26     126
piRSquared
  • 285,575
  • 57
  • 475
  • 624
2

With df.where + df.rank

n = 2
k = 3
df.where(df.rank(1, method='dense') <= len(df.columns)-n, df*k)

             c1  c2  c3   c4
12/12/2016  114  30   1    8
12/11/2016  132  12  17  138
12/10/2016   39   6   2   21
12/09/2016    9  48  13   78

To address your update you could still use where + rank, though it seems less suitable than it was for the above manipulation.

df['new_col'] = df.where(df.rank(1, method='dense') >= len(df.columns)-n, df*0).sum(1)*k

            c1  c2  c3  c4  new_col
12/12/2016  38  10   1   8      144
12/11/2016  44  12  17  46      270
12/10/2016  13   6   2   7       60
12/09/2016   9  16  13  26      126
ALollz
  • 57,915
  • 7
  • 66
  • 89