How to find the average of columns col3, col4, col5 in the below given dataframe and add it as a new column called 'average' as shown in the required output dataframe using pandas.
Input dataframe:
di = {'col1': ['abc', 'def', 'ghi', 'jkl'], 'col2': ['123', None, '456', '789'], 'col3': ['1', '2', '5',None], 'col4': ['4', '7', None, '8'], 'col5': ['9', None, '3', '8']} df = pd.DataFrame(di, dtype=object) col1 col2 col3 col4 col5 0 abc 123 1 4 9 1 def None 2 7 None 2 ghi 456 5 None 3 3 jkl 789 None 8 8 Required Output:
col1 col2 col3 col4 col5 average 0 abc 123 1 4 9 4.66 1 def None 2 7 None 4.5 2 ghi 456 5 None 3 4 3 jkl 789 None 8 8 8 2 Answers
Select columns by names or by positions by iloc, convert to float because None is converted to NaN and last get mean per rows by axis=1:
cols = ['col3','col4','col5'] df['average'] = df[cols].astype(float).mean(axis=1) df['average'] = df.iloc[:, 2:].astype(float).mean(axis=1) print (df) col1 col2 col3 col4 col5 average 0 abc 123 1 4 9 4.666667 1 def None 2 7 None 4.500000 2 ghi 456 5 None 3 4.000000 3 jkl 789 None 8 8 8.000000 If not known columns (but i am skipping the first one because of desired output):
df = pd.DataFrame(di).fillna(pd.np.nan) df['average']=df.apply(lambda row: pd.to_numeric(pd.Series(row.tolist()[2:]),errors='coerce').mean(),axis=1) print(df) Output:
col1 col2 col3 col4 col5 average 0 abc 123 1 4 9 4.666667 1 def NaN 2 7 NaN 4.500000 2 ghi 456 5 NaN 3 4.000000 3 jkl 789 NaN 8 8 8.000000 
