Get the column and row names in pandas in python

Question

For each row in the pandas dataframe I want to find the cell/cells with the minimum value and return its row and column name separately. I also want to check if that minimum value is less than one.

E.g.,

NAMES, Oil, Fat, Salt
Salad,  0.2, 0.1,  0.8
Bread,  0.1, 0.9,  0.1
Rice,    1,   1,  1

Output of the above dataframe:

['Salad', 'Fat']
[['Bread', 'Oil'], ['Bread', 'Salt']]
<No output because the minimum is not less than 1>

My current code looks like follows,

pairs = df.set_index('NAMES').apply(lambda row: [[row.name, l] for l in row[row == row.min()].index], axis=1).values.tolist()

Please help me.

Answer 1

Use

In [2224]: dff = df.set_index('NAMES')

In [2225]: dff.where(dff<1).eq(dff.min(1), axis=0).apply(
                    lambda x: [[x.name, v] for v in x[x].index], axis=1).values.tolist()
Out[2225]: [[['Salad', ' Fat']], [['Bread', ' Oil'], ['Bread', ' Salt']], []]

Use filter if you need to remove empty lists

In [2230]: vals = dff.where(dff<1).eq(dff.min(1), axis=0).apply(
             lambda x: [[x.name, v] for v in x[x].index], axis=1).values.tolist()

In [2231]: filter(None, vals)
Out[2231]: [[['Salad', ' Fat']], [['Bread', ' Oil'], ['Bread', ' Salt']]]

Or,

In [2235]: vals2 = dff.where(dff<1).eq(dff.min(1), axis=0).apply(
               lambda x: [[x.name, v] for v in x[x].index], axis=1)

In [2236]: [x for x in vals2 if x]
Out[2236]: [[['Salad', ' Fat']], [['Bread', ' Oil'], ['Bread', ' Salt']]]

Answer 2

I think you need add new condition:

pairs = (df.set_index('NAMES')
          .apply(lambda row: [[row.name, l] for l in row[(row == row.min()) & 
                                                         (row < 1)].index], axis=1)
          .values.tolist())
print (pairs)
[[['Salad', 'Fat']], [['Bread', 'Oil'], ['Bread', 'Salt']], []]

Then use this solutions:

pairs = [x for x in pairs if x]
print (pairs)
[[['Salad', 'Fat']], [['Bread', 'Oil'], ['Bread', 'Salt']]]

---

#for python 3 add list
pairs = list(filter(None, pairs))
print (pairs)
[[['Salad', 'Fat']], [['Bread', 'Oil'], ['Bread', 'Salt']]]

---

pairs = [x for x in pairs if x != []]
print (pairs)
[[['Salad', 'Fat']], [['Bread', 'Oil'], ['Bread', 'Salt']]]