Python: how to select columns with condition?

Question

I have a dataframe like the following:

df
    id          d1         d2          d3         a1    a2       a3
0   474     0.000243    0.000243    0.001395    bank    bank    atm
1   964     0.000239    0.000239    0.000899    bank    bank    bank
2   4823    0.000472    0.000472    0.000834    fuel    fuel    fuel
3   7225    0.002818    0.002818    0.023900    bank    bank    fuel
4   7747    0.001036    0.001036    0.001415    dentist dentist bank

I want to select the minimum between d1, d2 and d3 and the corresponding a1, a2 or a3.

df
    id      d          a
0  474  0.000243     bank
1  964  0.000239     bank
2 4823  0.000472     fuel
3 7225  0.002818     bank
4 7747  0.001036     dentist

Answer 1

If want select columns by lists get column name by DataFrame.idxmin, rename columns and then use DataFrame.lookup in DataFrame.assign for new columns:

col1 = ['d1','d2','d3']
col2 = ['a1','a2','a3']

pos = df[col1].idxmin(axis=1).map(dict(zip(col1, col2)))

df = df[['id']].assign(d = df[col1].min(axis=1), a = df.lookup(df.index, pos))
print (df)
     id         d        a
0   474  0.000243     bank
1   964  0.000239     bank
2  4823  0.000472     fuel
3  7225  0.002818     bank
4  7747  0.001036  dentist

Answer 2

You could use pd.wide_to_long here to get a long format dataframe, specifying [d,a] as stubnames. Then groupby the id and index taking the idxmin of d:

df = (pd.wide_to_long(df, stubnames=['d','a'], suffix= '\d+', i='id', j='j')
        .reset_index().drop('j',1))
df = df.loc[df.groupby('id').d.idxmin().values]

---

print(df)

     id         d        a
0   474  0.000243     bank
1   964  0.000239     bank
2  4823  0.000472     fuel
3  7225  0.002818     bank
4  7747  0.001036  dentist

---

Where taking pd.wide_to_long as above gives the dataframe as:

pd.wide_to_long(df, stubnames=['d','a'], suffix= '\d+', i='id', j='j')

              d        a
id   j                   
474  1  0.000243     bank
964  1  0.000239     bank
4823 1  0.000472     fuel
7225 1  0.002818     bank
7747 1  0.001036  dentist
474  2  0.000243     bank
964  2  0.000239     bank
4823 2  0.000472     fuel
7225 2  0.002818     bank
7747 2  0.001036  dentist
474  3  0.001395      atm
964  3  0.000899     bank
4823 3  0.000834     fuel
7225 3  0.023900     fuel
7747 3  0.001415     bank

Where we just need to group in the id and find the index of the minimum values.