Find minimum across multiple worksheet using pandas

Question

How can I find the minimum values among multiple worksheets for each index across total worksheet

suppose,

  worksheet 1

    index    A   B   C
       0     2   3   4.28
       1     3   4   5.23
    worksheet 2

    index    A   B   C
        0    9   6   5.9
        1    1   3   4.1

    worksheet 3

    index    A   B   C
        0    9   6   6.0
        1    1   3   4.3
 ...................(Worksheet 4,Worksheet 5)...........
by comparing C column, I want an answer, where dataframe looks like

index      min(c)
    0       4.28
    1       4.1

Answer 1

from functools import reduce

reduce(np.fmin, [ws1.C, ws2.C, ws3.C])

index
0    4.28
1    4.10
Name: C, dtype: float64

---

This generalizes nicely with a comprehension

reduce(np.fmin, [w.C for w in [ws1, ws2, ws3, ws4, ws5]])

---

If you must insist on your column name

from functools import reduce

reduce(np.fmin, [ws1.C, ws2.C, ws3.C]).to_frame('min(C)')

       min(C)
index        
0        4.28
1        4.10

---

You can also use pd.concat on a dictionary and use pd.Series.min with the level=1 parameter

pd.concat(dict(enumerate([w.C for w in [ws1, ws2, ws3]]))).min(level=1)
# equivalently
# pd.concat(dict(enumerate([w.C for w in [ws1, ws2, ws3]])), axis=1).min(1)

index
0    4.28
1    4.10
Name: C, dtype: float64

Note:

dict(enumerate([w.C for w in [ws1, ws2, ws3]]))

is another way of saying

{0: ws1.C, 1: ws2.C, 2: ws3.C}