Finding the frequency that each column is a row minimum

user_4998865 0
Viewed 189

I have a dataframe that looks like:

       A     B     C     D
 0   1.2     0   1.1   3.2
 1   2.3   2.2   2.2   2.5
 2   1.1   1.5     0   1.7
 3     0   1.1   1.4   1.2
 4   3.3   3.0   1.7   1.7
 5   1.1   1.0   2.2   2.5
 6   5.0   5.0   5.0   5.0

I would like to find the frequency that each column contains the row's minimum. So in some format:

B: 2               # rows 0, 5
A: 1               # row 3
C: 1               # row 2
(B, C): 1          # row 1
(C, D): 1          # row 4
(A, B, C, D): 1    # row 6

I am currently doing df.min(axis=1) and then looping through each row using df.iloc... but there has to be a better way.

In case it matters, I have a couple hundred columns, a couple thousand rows, and it represents a sample, so I have to perform the operation roughly a million times. I must be missing an obvious pandas or numpy method that will do this both pythonically and reasonably efficiently.

pandas dataframe python
3 Answers

Use, DataFrame.eq, DataFrame.min to create a boolean mask then use DataFrame.dot on df.columns and this mask then count the freq using Series.value_counts:

m = df.eq(df.min(axis=1), 0)
freq = m.dot(df.columns + ',').str.rstrip(',').value_counts()

# print(freq)
B          2
C,D        1
A          1
A,B,C,D    1
C          1
B,C        1
dtype: int64
user_128331660

IIUC,

  1. Transpose the dataset (making it easier with axis)
  2. Identify the location of min values in each column.
  3. Identify the column names for each min
  4. Value counts
df = df.T

result = (df.eq(df.min())
            .apply(lambda x:tuple(x.index[x]))
            .value_counts())
user_117478610

By using stack with unstack 

df.eq(df.min(1),0).stack().loc[lambda x : x].reset_index(level=1).groupby(level=0)['level_1'].agg(tuple).value_counts()
(B,)            2
(C, D)          1
(A,)            1
(B, C)          1
(A, B, C, D)    1
(C,)            1
Name: level_1, dtype: int64
user_79645270
Related