Problem while retrieving indices in a loop-CodePudding

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]

CodePudding user response：

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]