I have a df with 6 columns (category 1, 2, ..., 6), all with values either "I", "II", "III", or "IV". These string values are the categories that applies to each column. I want to make another column ("Score), that, based on the values for each column, assign a value to each column value, sum and take the average.
I want to assign 100 to values of I, 70 to II, 35 to III, and 0 to IV. I then want to sum all these for the row, and take the average. Is there anyway to assign these values and perform the sum and average without creating more than 1 columns?
CodePudding user response:
You can create a dictionary to map the values and use np.mean
to get the average:
d = {'I': 100, 'II': 70, 'III': 35, 'IV': 0}
df['Average Value'] = df.apply(lambda x: np.mean(x.map(d)), axis = 1)