How to replace 'Zero' by 'One' for particular row in data frame

Question

I've this dataframe:df1

                    DP1       DP2       DP3      DP4      DP5      DP6    DP7     DP8  DP9  DP10
OP1             43239.0   46962.0   55858.0   9128.0  30372.0   5932.0  667.0   663.0  0.0   NaN
OP2               146.0      73.0   16647.0   5596.0   1493.0   7175.0   45.0   438.0  NaN   NaN
OP3            266279.0    1189.0       1.0  10939.0  17799.0   4702.0  235.0     NaN  NaN   NaN
OP4            360547.0   56943.0  142271.0  38217.0   1141.0   6757.0    NaN     NaN  NaN   NaN
OP5            380497.0   17946.0   19376.0      0.0   3974.0      NaN    NaN     NaN  NaN   NaN
OP6              6151.0   16525.0   17046.0  11532.0      NaN      NaN    NaN     NaN  NaN   NaN
OP7            142026.0   21999.0     820.0      NaN      NaN      NaN    NaN     NaN  NaN   NaN
OP8             76860.0  102580.0       NaN      NaN      NaN      NaN    NaN     NaN  NaN   NaN
OP9              6210.0       NaN       NaN      NaN      NaN      NaN    NaN     NaN  NaN   NaN
OP10                NaN       NaN       NaN      NaN      NaN      NaN    NaN     NaN  NaN   NaN
Total         1281955.0  264217.0  252019.0  75412.0  54779.0  24566.0  947.0  1101.0  0.0   0.0
Variance       160244.0   37745.0   42003.0  15082.0  13695.0   89.0  474.0  1101.0  NaN  -0.0
Mack's Sigma      400.0     194.0     205.0    123.0    117.0     90.0   22.0    33.0  NaN  -0.0

Variance       160244.0   37745.0   42003.0  15082.0  13695.0   89.0  474.0  1101.0  NaN  -0.0

I would like to Find the minimum value of last two entry of Variance row. I would like to last two entries and finding minimum , like in variance last two entries are 474.0 and 1101.0 and that should be added in Nan place.

Output look like

Variance       160244.0   37745.0   42003.0  15082.0  13695.0   89.0  474.0  1101.0  474.0 -0.0

I've tried this code:

minValuesObj = min(df1.loc('Variance'))

RavinderSingh13 · Answer 1 · 2021-04-12T06:30:19.850

Could you please try following, using loc and discrete element concept.

import pandas as pd
df.loc['Variance','DP9'] = df.loc['Variance', ['DP7','DP8']].min()
df

Output will be as follows:

                    DP1       DP2       DP3      DP4      DP5      DP6    DP7     DP8    DP9  DP10
OP1             43239.0   46962.0   55858.0   9128.0  30372.0   5932.0  667.0   663.0    0.0   NaN
OP2               146.0      73.0   16647.0   5596.0   1493.0   7175.0   45.0   438.0    NaN   NaN
OP3            266279.0    1189.0       1.0  10939.0  17799.0   4702.0  235.0     NaN    NaN   NaN
OP4            360547.0   56943.0  142271.0  38217.0   1141.0   6757.0    NaN     NaN    NaN   NaN
OP5            380497.0   17946.0   19376.0      0.0   3974.0      NaN    NaN     NaN    NaN   NaN
OP6              6151.0   16525.0   17046.0  11532.0      NaN      NaN    NaN     NaN    NaN   NaN
OP7            142026.0   21999.0     820.0      NaN      NaN      NaN    NaN     NaN    NaN   NaN
OP8             76860.0  102580.0       NaN      NaN      NaN      NaN    NaN     NaN    NaN   NaN
OP9              6210.0       NaN       NaN      NaN      NaN      NaN    NaN     NaN    NaN   NaN
OP10                NaN       NaN       NaN      NaN      NaN      NaN    NaN     NaN    NaN   NaN
Total         1281955.0  264217.0  252019.0  75412.0  54779.0  24566.0  947.0  1101.0    0.0   0.0
Variance       160244.0   37745.0   42003.0  15082.0  13695.0     89.0  474.0  1101.0  474.0  -0.0
Mack's Sigma      400.0     194.0     205.0    123.0    117.0     90.0   22.0    33.0    NaN  -0.0

Great but if we have different columns then it wont work , we cant just specify the column name . Anyway Thank you for your time :) — Michael Scofield, Apr 12 '21 at 06:39
@MichaelScofield, just change the column names, by the way in iloc also it will same thing if you change column(places) even that will fail :) cheers and happy learning. — RavinderSingh13, Apr 12 '21 at 06:41

Mayank Porwal · Answer 2 · 2021-04-12T06:14:20.153

2

Use df.iloc and df.min:

df1.iloc[-2, -2] = df1.iloc[-2, -4:-2].min()

edited Apr 12 '21 at 06:14

answered Apr 12 '21 at 05:59

Mayank Porwal

33,470
8
37
58

@jezrael Updated my answer. Now its working. – Mayank Porwal Apr 12 '21 at 06:11
1

Your first solution was using `loc`. And I posted `iloc` before you. – Mayank Porwal Apr 12 '21 at 06:15
ya, it was `df1.iloc['Variance', -2:].min()` - unfortunately wrong. – jezrael Apr 12 '21 at 06:16
1

Ya, like you fixed your solution, I also did. – Mayank Porwal Apr 12 '21 at 06:16
If I delete my solution, will you be happy? – Mayank Porwal Apr 12 '21 at 06:20
my opinion - first delete and try find something else like posted in my answer. then edit answer. – jezrael Apr 12 '21 at 06:21

jezrael · Accepted Answer · 2021-04-12T06:11:19.307

Use DataFrame.iloc with set values by min (there is selected by position, it means for last previous label Variance use -2):

df1.iloc[-2, -2] = df1.iloc[-2, -4:-2].min()

Or is possible use Index.get_loc for position by label name:

pos = df1.index.get_loc('Variance')
df1.iloc[pos, -2] = df1.iloc[pos, -4:-2].min()

Or if need select by Variance use DataFrame.loc for seelct by labels, for dynamic columns names use indexing df1.columns:

df1.loc['Variance', df1.columns[-2]] = df1.loc['Variance', df1.columns[-4:-2]].min()

                    DP1       DP2       DP3      DP4      DP5      DP6    DP7  \
OP1             43239.0   46962.0   55858.0   9128.0  30372.0   5932.0  667.0   
OP2               146.0      73.0   16647.0   5596.0   1493.0   7175.0   45.0   
OP3            266279.0    1189.0       1.0  10939.0  17799.0   4702.0  235.0   
OP4            360547.0   56943.0  142271.0  38217.0   1141.0   6757.0    NaN   
OP5            380497.0   17946.0   19376.0      0.0   3974.0      NaN    NaN   
OP6              6151.0   16525.0   17046.0  11532.0      NaN      NaN    NaN   
OP7            142026.0   21999.0     820.0      NaN      NaN      NaN    NaN   
OP8             76860.0  102580.0       NaN      NaN      NaN      NaN    NaN   
OP9              6210.0       NaN       NaN      NaN      NaN      NaN    NaN   
OP10                NaN       NaN       NaN      NaN      NaN      NaN    NaN   
Total         1281955.0  264217.0  252019.0  75412.0  54779.0  24566.0  947.0   
Variance       160244.0   37745.0   42003.0  15082.0  13695.0     89.0  474.0   
Mack's Sigma      400.0     194.0     205.0    123.0    117.0     90.0   22.0   

                 DP8    DP9  DP10  
OP1            663.0    0.0   NaN  
OP2            438.0    NaN   NaN  
OP3              NaN    NaN   NaN  
OP4              NaN    NaN   NaN  
OP5              NaN    NaN   NaN  
OP6              NaN    NaN   NaN  
OP7              NaN    NaN   NaN  
OP8              NaN    NaN   NaN  
OP9              NaN    NaN   NaN  
OP10             NaN    NaN   NaN  
Total         1101.0    0.0   0.0  
Variance      1101.0  474.0  -0.0  
Mack's Sigma    33.0    NaN  -0.0

How to replace 'Zero' by 'One' for particular row in data frame

3 Answers3