Matching value to column index pandas

user_18766270 0
Viewed 41

I have dataframe that looks like the following:

   Given    Y1     eY1    Y2     eY2    Y3     eY3    Y4     eY4    Y5
0   0.45  0.25  0.3550  0.39  0.4200  0.43  0.5950  0.65  0.7175  0.74
1   0.39  0.15  0.2400  0.27  0.5025  0.58  0.7675  0.83  0.8600  0.87
2   0.99  0.30  0.4875  0.55  0.7225  0.78  0.9075  0.95  0.9800  0.99
3   0.58  0.23  0.2825  0.30  0.5550  0.64  0.7075  0.73  0.8725  0.92
4    NaN  0.25  0.3625  0.40  0.6175  0.69  0.8100  0.85  0.9250  0.95

My goal is simple: try to match the "given" value in each row to the closest column index (columns are sorted in ascending order and output the closest column index to a new column. I have been stuck on this for some time and would greatly appreciate any help/starting tips.

(for any "Nan" values in Given, I am outputting "none")

Thank you!

pandas dataframe numpy python
1 Answers

First subtract all columns without Given by column Given by DataFrame.sub with absolute values and then use DataFrame.idxmin if not missing values in Given:

df1 = df.drop('Given', 1).sub(df['Given'], axis=0).abs()
print (df1)
     Y1     eY1    Y2     eY2    Y3     eY3    Y4     eY4    Y5
0  0.20  0.0950  0.06  0.0300  0.02  0.1450  0.20  0.2675  0.29
1  0.24  0.1500  0.12  0.1125  0.19  0.3775  0.44  0.4700  0.48
2  0.69  0.5025  0.44  0.2675  0.21  0.0825  0.04  0.0100  0.00
3  0.35  0.2975  0.28  0.0250  0.06  0.1275  0.15  0.2925  0.34
4   NaN     NaN   NaN     NaN   NaN     NaN   NaN     NaN   NaN

df['new'] = np.where(df['Given'].isna(), None, df1.idxmin(axis=1))
print (df)
   Given    Y1     eY1    Y2     eY2    Y3     eY3    Y4     eY4    Y5   new
0   0.45  0.25  0.3550  0.39  0.4200  0.43  0.5950  0.65  0.7175  0.74    Y3
1   0.39  0.15  0.2400  0.27  0.5025  0.58  0.7675  0.83  0.8600  0.87   eY2
2   0.99  0.30  0.4875  0.55  0.7225  0.78  0.9075  0.95  0.9800  0.99    Y5
3   0.58  0.23  0.2825  0.30  0.5550  0.64  0.7075  0.73  0.8725  0.92   eY2
4    NaN  0.25  0.3625  0.40  0.6175  0.69  0.8100  0.85  0.9250  0.95  None
user_29010020
Related