Output sum rounded to the nearest even number using Python-CodePudding

I wish to take the sum of each row and round this value to the closest even number.

Data

id  type    Q1 22   Q2 22   Q3 22   Q4 22  
AA  hi      0.2     0.8     0.3     2.1      
AA  hello   0.2     0.7     0.3     1.7     
AA  ok      2       0.1     0       0.1

Desired

id  type    Q1 22   Q2 22   Q3 22   Q4 22  rounded_nearest_sum
AA  hi      0.2     0.8     0.3     2.1    4
AA  hello   0.2     0.7     0.3     1.7    2
AA  ok      2       0.1     0       0.1    2

Doing

df.loc[:,'rounded_nearest_sum'] = df.sum(axis=1)

I realize I have to incorporate i i.mod(2) into this script, however I am not sure how to incorporate. Any suggestion is helpful.

CodePudding user response：

Filter the quaters , add rowise and set to type intger. That truncates the decimal places

df.filter(regex='Q', axis=1).sum(1).astype(int)

CodePudding user response：

here is one way to do it

# filter columns that has 'Q' in their name and sum along the rows (across columns)
df['rounded_sum']=df.filter(like='Q').sum(axis=1)

# using np.where, check if integer part is even or odd
# if odd, add 1 else, keep the integer as is
df['rounded_sum']=np.where(df['rounded_sum'].astype(int)%2==1,
                           df['rounded_sum'].astype(int)  1, 
                           df['rounded_sum'].astype(int))
df

    id  type    Q1 22   Q2 22   Q3 22   Q4 22   rounded_sum
0   AA  hi        0.2     0.8     0.3     2.1      4
1   AA  hello     0.2     0.7     0.3     1.7      2
2   AA  ok        2.0     0.1     0.0     0.1      2

CodePudding user response：

You can divide by 2, round, then multiply by 2. To take the row sums, use sum(numeric_only=True) to exclude non-numeric values.

import pandas as pd

k = ['id', 'type',  'Q1 22',   'Q2 22',   'Q3 22',   'Q4 22']
d = [
    ['AA','hi',   0.2, 0.8, 0.3  ,2.1],
    ['AA','hello',0.2, 0.7, 0.3  ,1.7],
    ['AA','ok',   2  , 0.1, 0    ,0.1],
]

df = pd.DataFrame(d, columns=k).set_index('id')

df['rounded_nearest_sum'] = df.sum(axis=1, numeric_only=True).divide(2).round().multiply(2).astype(int)

This will give you the data frame:

     type  Q1 22  Q2 22  Q3 22  Q4 22  rounded_nearest_sum
id                                                        
AA     hi    0.2    0.8    0.3    2.1                    4
AA  hello    0.2    0.7    0.3    1.7                    2
AA     ok    2.0    0.1    0.0    0.1                    2