Search code examples
python-3.xpandasloopspairwise

Select Paired Row, Col Pandas Values

I'm sure this is easy but finding or discovering the answer is not! I'm trying to select paired values from a df (5,5) size of rows and columns that represent latitude (rows: 5,10,15,etc) and longitude (cols: -98,-97,-96, etc). Suppose my df looks like this:

df:

  lat -98 -97 -96 -95 -94
0   5   6   7   8   9  10
1  10  11  12  13  14  15
2  15  16  17  18  19  20
3  20  21  22  23  24  25
4  25  26  27  28  29  30

To get the extracted pair iterating by single row and single col, I need the following:

dfnew:
0   6
1   12 
2   18
3   24
4   30

I've tried things like this below and different types of loops too numerous to show here:

df.iloc[0:5,1:6]

but this gives me all the rows and all the columns and I just need the single paired value. thank you,

Solution

  • IIUC, you could use numpy.diag:

    import numpy as np
out = pd.Series(np.diag(df.drop(columns='lat')), index=df.index)

    Output:

    0     6
1    12
2    18
3    24
4    30

    If you want a lat-long pair, then maybe:

    out = pd.DataFrame({'lat': df['lat'], 'long': np.diag(df.drop(columns='lat'))})

    Output:

       lat  long
0    5     6
1   10    12
2   15    18
3   20    24
4   25    30