Reading a pandas dataframe with longitude and latitude as column and index

Question

I have a csv file that is read as pd dataframe (see below) and I am trying to read a list of stations (27612, 25022, 26198 ...) that are in this longitude/latitude matrix and record their longitude and latitude as another list dataframe like the second one below. I tried to use loc or at but did not work well and when I loop them, it seems taking forever.

Input:

    -126.125    -125.875    -125.625    -125.375    -125.125    -124.875    -124.625    -124.375
49.375  27612   27613   27614   27615   27616   27617   27618   27619
49.125  27376   27377   27378   27379   27380   27381   27382   27383
48.875  27140   27141   27142   27143   27144   27145   27146   27147
48.625  26904   26905   26906   26907   26908   26909   26910   26911
48.375  26668   26669   26670   26671   26672   26673   26674   26675
48.125  26432   26433   26434   26435   26436   26437   26438   26439
47.875  26196   26197   26198   26199   26200   26201   26202   26203
47.625  25960   25961   25962   25963   25964   25965   25966   25967
47.375  25724   25725   25726   25727   25728   25729   25730   25731
47.125  25488   25489   25490   25491   25492   25493   25494   25495
46.875  25252   25253   25254   25255   25256   25257   25258   25259
46.625  25016   25017   25018   25019   25020   25021   25022   25023
46.375  24780   24781   24782   24783   24784   24785   24786   24787
46.125  24544   24545   24546   24547   24548   24549   24550   24551
45.875  24308   24309   24310   24311   24312   24313   24314   24315

1   27612   -126.125    49.375
2   27376   -125.875    49.125
3   27140   -125.625    48.875

Answer 1

melt() is what you are looking for.

import io
import pandas as pd

data ="""   -126.125    -125.875    -125.625    -125.375    -125.125    -124.875    -124.625    -124.375
49.375  27612   27613   27614   27615   27616   27617   27618   27619
49.125  27376   27377   27378   27379   27380   27381   27382   27383
48.875  27140   27141   27142   27143   27144   27145   27146   27147
48.625  26904   26905   26906   26907   26908   26909   26910   26911
48.375  26668   26669   26670   26671   26672   26673   26674   26675
48.125  26432   26433   26434   26435   26436   26437   26438   26439
47.875  26196   26197   26198   26199   26200   26201   26202   26203
47.625  25960   25961   25962   25963   25964   25965   25966   25967
47.375  25724   25725   25726   25727   25728   25729   25730   25731
47.125  25488   25489   25490   25491   25492   25493   25494   25495
46.875  25252   25253   25254   25255   25256   25257   25258   25259
46.625  25016   25017   25018   25019   25020   25021   25022   25023
46.375  24780   24781   24782   24783   24784   24785   24786   24787
46.125  24544   24545   24546   24547   24548   24549   24550   24551
45.875  24308   24309   24310   24311   24312   24313   24314   24315"""

df = pd.read_csv(io.StringIO(data), delimiter="\t")

df = df.melt(id_vars=['Unnamed: 0'], var_name='long', value_name='station')
df.rename(columns={'Unnamed: 0':'lat'}, inplace=True)

Output:

lat long    station
0   49.375  -126.125    27612
1   49.125  -126.125    27376
2   48.875  -126.125    27140
3   48.625  -126.125    26904
4   48.375  -126.125    26668
... ... ... ...
115 46.875  -124.375    25259
116 46.625  -124.375    25023
117 46.375  -124.375    24787
118 46.125  -124.375    24551
119 45.875  -124.375    24315