Creating a new column with column names in dataframe based on maximum value of two columns-CodePudding

I have a dataframe as follows:

Col1    Val1    Val2
A      1        0
B      2        3
C      0        4
D      3        2

I need the following output:

Col1    Val1    Val2    Type
A       1       0       Val1
B       2       3       Val2
C       0       4       Val2
D       3       2       Val1

The column Type basically refers to where the maximum of Val1 and Val2 are.

I am not sure how to approach this.

CodePudding user response：

you can do it with :


df['Type'] = df.apply(lambda x: 'Val1' if x.Val1 > x.Val2 else 'Val2', axis=1)

Special case : if you want to return None when Val1 == Val2


def get_max_col(x):
    if x.Val1 > x.Val2:
        return 'Val1'
    elif x.Val1 == x.Val2:
        return None
    else:
        return 'Val2'


df['Type'] = df.apply(get_max_col, axis=1)

CodePudding user response：

(df['Val1'] >= df['Val2']).map({True: 'Val1', False: 'Val2'}

In [43]: df = pd.DataFrame(np.random.randint(0, 20, (10_000, 2)), columns=['val1', 'val2'])
    ...: %timeit (df['val1'] >= df['val2']).map({True: 'val1', False: 'val2'})
    ...: %timeit df.apply(lambda x: 'val1' if x.val1 >= x.val2 else 'val2', axis=1)
    ...: %timeit df.loc[:, ['val1', 'val2']].idxmax(axis=1)
1.27 ms ± 45.3 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)
123 ms ± 836 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
5.73 ms ± 95.9 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

CodePudding user response：

Run:

df['Type'] = df.iloc[:, 1:].idxmax(axis=1)

This code works regardless of the number of columns and their names.

iloc[:, 1:] is to "filter out" column 0.

If you want just these 2 columns only, alternative choices are:

df['Type'] = df.iloc[:, 1:3].idxmax(axis=1)

df['Type'] = df[['Val1', 'Val2']].idxmax(axis=1)