Pandas Find Best Match & Show Result in Table

user_2100039 0
Viewed 101

I have a data table that looks like this below and I'm trying to find the closest match of each column for the years 1991-2019 to the current year (2020). In the end, i need a table like the following showing the result of the find process.

Data table (daty):

      soi     ao   nao   pna       epo       wpo
Date                                            
1990 -0.7 -0.210  1.18 -0.46 -0.285904  0.763053
1991 -1.5 -0.112  0.61  1.15 -0.418762  0.850752
1992  0.1 -0.640 -0.28 -1.13  0.679149 -0.788247
1993 -0.7 -0.361 -0.41 -0.97 -1.228830 -0.447579
1994 -1.6 -0.084 -1.14 -2.02  0.193085 -0.912737
1995  0.3 -0.549  0.45  1.22 -0.658217  0.621721
1996  0.6 -1.140 -0.69 -0.27  0.394934 -1.161684
1997 -1.4  0.195  0.74  0.22  0.138353  0.414802
1998  1.0 -1.050 -1.80  0.42  0.254834  0.563291
1999 -0.1  0.059  0.50  0.33  0.176403  0.587210
2000  0.9  0.395 -0.06 -1.21 -0.303799 -0.809344
2001  0.2 -0.707 -0.49  0.04  0.429597  1.082077
2002 -0.6 -0.043 -0.54  0.60 -0.117497 -0.012490
2003 -0.1  0.467  0.16  0.59  0.172270 -0.503114
2004 -0.3  0.855  0.52 -0.11  0.611507 -0.055418
2005  0.4  0.802  0.76  1.32  0.117412  0.628558
2006 -0.6  0.606 -1.43  0.31 -0.439056 -0.666251
2007  0.2  0.179  0.85  1.52 -0.091188  0.887357
2008  1.2 -0.327  1.14  0.89 -0.146648 -0.444564
2009  0.3  0.875  1.62  1.03  0.747285  0.223464
2010  2.2 -0.865 -0.62  1.07 -0.322557  0.002758
2011  1.0  0.665  0.67 -0.39  1.035278  1.146269
2012  0.2  0.772 -0.43 -0.39 -0.006886  0.560083
2013  0.3 -0.461  0.38  0.41  0.451967  0.600924
2014 -0.7  0.102  1.72  0.78 -0.343152 -0.453856
2015 -1.6 -0.165 -0.49 -0.80  0.394162 -1.161164
2016  1.2  0.781  0.74  0.11  0.104103  0.189280
2017  0.6 -0.492 -0.45 -0.32  0.084595 -0.268721
2018 -0.9  0.585  1.78  1.16 -1.217344 -1.947052
2019 -1.2  0.306 -0.01  1.62 -0.006176  0.676129
2020  0.9  0.631  1.11  0.59 -0.398920 -0.165507

The final result for example needs to look like this:

  index     0  
0   soi  2010  
1    ao  2009  
2   nao  2018  
3   pna  2019  
4   epo  2011  
5   wpo  2011

I've tried using argsort in this form but i cannot figure out how to extract the best match maybe in a loop for each column and create the result in the table form. daty.iloc[(daty['soi']-input).abs().argsort()[:1]]. Thank you for any help!

pandas find python
2 Answers

Do you mean:

df.iloc[:-1].sub(df.iloc[-1]).abs().idxmin()

Output:

soi    2000
ao     2006
nao    2008
pna    2003
epo    1991
wpo    2017
dtype: int64
user_42384080

you can do that as follows:

df.idxmax(axis='index')

For your data set this evaluates to:

soi    2010
ao     2009
nao    2018
pna    2019
epo    2011
wpo    2011
dtype: int64

To test this, I created the dataframe from your post like:

from io import StringIO
infile= StringIO(
"""Date  soi     ao   nao   pna       epo       wpo
1990 -0.7 -0.210  1.18 -0.46 -0.285904  0.763053
1991 -1.5 -0.112  0.61  1.15 -0.418762  0.850752
1992  0.1 -0.640 -0.28 -1.13  0.679149 -0.788247
1993 -0.7 -0.361 -0.41 -0.97 -1.228830 -0.447579
1994 -1.6 -0.084 -1.14 -2.02  0.193085 -0.912737
1995  0.3 -0.549  0.45  1.22 -0.658217  0.621721
1996  0.6 -1.140 -0.69 -0.27  0.394934 -1.161684
1997 -1.4  0.195  0.74  0.22  0.138353  0.414802
1998  1.0 -1.050 -1.80  0.42  0.254834  0.563291
1999 -0.1  0.059  0.50  0.33  0.176403  0.587210
2000  0.9  0.395 -0.06 -1.21 -0.303799 -0.809344
2001  0.2 -0.707 -0.49  0.04  0.429597  1.082077
2002 -0.6 -0.043 -0.54  0.60 -0.117497 -0.012490
2003 -0.1  0.467  0.16  0.59  0.172270 -0.503114
2004 -0.3  0.855  0.52 -0.11  0.611507 -0.055418
2005  0.4  0.802  0.76  1.32  0.117412  0.628558
2006 -0.6  0.606 -1.43  0.31 -0.439056 -0.666251
2007  0.2  0.179  0.85  1.52 -0.091188  0.887357
2008  1.2 -0.327  1.14  0.89 -0.146648 -0.444564
2009  0.3  0.875  1.62  1.03  0.747285  0.223464
2010  2.2 -0.865 -0.62  1.07 -0.322557  0.002758
2011  1.0  0.665  0.67 -0.39  1.035278  1.146269
2012  0.2  0.772 -0.43 -0.39 -0.006886  0.560083
2013  0.3 -0.461  0.38  0.41  0.451967  0.600924
2014 -0.7  0.102  1.72  0.78 -0.343152 -0.453856
2015 -1.6 -0.165 -0.49 -0.80  0.394162 -1.161164
2016  1.2  0.781  0.74  0.11  0.104103  0.189280
2017  0.6 -0.492 -0.45 -0.32  0.084595 -0.268721
2018 -0.9  0.585  1.78  1.16 -1.217344 -1.947052
2019 -1.2  0.306 -0.01  1.62 -0.006176  0.676129
2020  0.9  0.631  1.11  0.59 -0.398920 -0.165507"""
)

df= pd.read_fwf(infile).set_index('Date')
user_69251850
Related