I'm trying to find the column names of each column in a pandas dataframe where the value is greater than that of another column.
For example, if I have the following dataframe:
A B C D threshold
0 1 3 3 1 2
1 2 3 6 1 5
2 9 5 0 2 4
For each row I would like to return the names of the columns where the values are greater than the threshold, so I would have:
0: B, C
1: C
2: A, B
Any help would be much appreciated!
If you want a large increase in speed you can use NumPy's vectorized where
function.
s = np.where(df.gt(df['threshold'],0), ['A, ', 'B, ', 'C, ', 'D, ', ''], '')
pd.Series([''.join(x).strip(', ') for x in s])
0 B, C
1 C
2 A, B
dtype: object
There is more than an order of magnitude speedup vs @jezrael and MaxU solutions when using a dataframe of 100,000 rows. Here I create the test DataFrame first.
n = 100000
df = pd.DataFrame(np.random.randint(0, 10, (n, 5)),
columns=['A', 'B', 'C', 'D', 'threshold'])
%%timeit
>>> s = np.where(df.gt(df['threshold'],0), ['A, ', 'B, ', 'C, ', 'D, ', ''], '')
>>> pd.Series([''.join(x).strip(', ') for x in s])
280 ms ± 5.29 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
%%timeit
>>> df1 = df.drop('threshold', 1).gt(df['threshold'], 0)
>>> df1 = df1.apply(lambda x: ', '.join(x.index[x]),axis=1)
3.15 s ± 82.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
%%timeit
>>> x = df.drop('threshold',1)
>>> x.T.gt(df['threshold']).agg(lambda c: ', '.join(x.columns[c]))
3.28 s ± 145 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
You can use:
df1 = df.drop('threshold', 1).gt(df['threshold'], 0)
df1 = df1.apply(lambda x: ', '.join(x.index[x]),axis=1)
print (df1)
0 B, C
1 C
2 A, B
dtype: object
Similar solution:
df1 = df.drop('threshold', 1).gt(df['threshold'], 0).stack().rename_axis(('a','b'))
.reset_index(name='boolean')
a = df1[df1['boolean']].groupby('a')['b'].apply(', '.join).reset_index()
print (a)
a b
0 0 B, C
1 1 C
2 2 A, B
you can do it this way:
In [99]: x = df.drop('threshold',1)
In [100]: x
Out[100]:
A B C D
0 1 3 3 1
1 2 3 6 1
2 9 5 0 2
In [102]: x.T.gt(df['threshold']).agg(lambda c: ', '.join(x.columns[c]))
Out[102]:
0 B, C
1 C
2 A, B
dtype: object
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With