[FIXED] Problem while retrieving indices in a loop

Issue

I am trying to run a loop to find the difference between an excel column (lets say A column) from a fixed set of values [125 150 175 200] for each element in that column A. When I find the diff, I then want to find the minimum values of difference and need to find the index of those values.

The code is here:

Ref = pd.ExcelFile ('Current parametric sweep_reference.xlsx')

print(Ref.sheet_names) 
for Sheet in Ref.sheet_names: 

Ref = pd.read_excel("Current parametric sweep_reference.xlsx",sheet_name=Sheet)
tempdiff = [125, 150, 175, 200] 
numbdiff = len(tempdiff)
values = np.zeros(numbdiff)
Tchipavg=list(Ref["Temperature (degC), Tchipcenter"])
Time =list(Ref["Time (s) (s)"])

index = list(Tchipavg).index(np.max(Tchipavg))
Time = Time[:index]

for j in range(0,numbdiff):

        diff =np.array([x-tempdiff[j] for x in Tchipavg[:index-1]])
        values[j] = min(abs(diff))
        min_index, min_value = min(enumerate(diff), key = operator.itemgetter(1))
        print(min_index, min_value)

print(values)

When I print values, it indeed gives the minimum values of difference but I am struggling to find the indices which i have to use to find values in another column, lets say column B. Can you point out what is the mistake here?

Data example:

df = pd.DataFrame([[0, 95.68 ], [1, 137.04], [2, 149.41], [3 , 158.25 ], [4, 165.28 ], [5 , 127.31 ], [6, 119.80 ], columns=['Time', 'Temp'])

The output should give indices of minimum diff in each delta T (tempdiff) case, for example, in the answer by @Jezrael, there are 4 values in array for 4 tempdiff [125, 150, 175, 200]. The output gives minimum of these 4. Instead I just need to find minimum value of all arrays for same tempdiff. for example it would be something like this:

values = [2.31  0.59 9.72 34.72]
indices = [5    2    4    4]

Solution

In pandas is best avoid loops, if necessary, so created vectorized solution.

---

If need subtract list by all values in DataFrame with absolute values and get indices use numpy - convert values to arrays, subtract with broadcasting, get absolute values and last get indices by numpy.argmin:

Ref = pd.DataFrame([[0, 95.68 ], [1, 137.04], [2, 149.41], [3 , 158.25 ],
                   [4, 165.28 ], [5 , 127.31 ], [6, 119.80 ]], columns=['Time', 'Temp'])

tempdiff = [125, 150, 175, 200] 

arr = Ref["Temp"].to_numpy()
a = np.abs(arr[:, None] - np.array(tempdiff))
print (a)

[[ 29.32  54.32  79.32 104.32]
 [ 12.04  12.96  37.96  62.96]
 [ 24.41   0.59  25.59  50.59]
 [ 33.25   8.25  16.75  41.75]
 [ 40.28  15.28   9.72  34.72]
 [  2.31  22.69  47.69  72.69]
 [  5.2   30.2   55.2   80.2 ]]

idx = np.argmin(a, axis=0)
print (idx)
[5 2 4 4]

values = a[idx, range(a.shape[1])]
print (values)
[ 2.31  0.59  9.72 34.72]

Answered By - jezrael