CSV Manipulation in Python Origin Destination Matrix Formulation

Question

I have a csv which containts the name and lat/lng location information of the Underground Stations in London. It looks like this:

Station Lat Lng
Abbey Road  51.53195199 0.003737786
Abbey Wood  51.49078408 0.120286371
Acton   51.51688696 -0.267675543
Acton Central   51.50875781 -0.263415792
Acton Town  51.50307148 -0.280288296

I wish to transform this csv to create an origin destination matrix of all the possible combinations of these stations. There are 270 stations, thus there are 72,900 possible combinations.

Ultimately I wish to turn this matrix into a csv with the following format

O_Station   O_lat   O_lng   D_Station   D_lat   D_lng
Abbey Road  51.53195199 0.003737786 Abbey Wood  51.49078408 0.120286371
Abbey Road  51.53195199 0.003737786 Acton   51.51688696 -0.267675543
Abbey Road  51.53195199 0.003737786 Acton Central   51.50875781 -0.263415792
Abbey Wood  51.49078408 0.120286371 Abbey Road  51.53195199 0.003737786
Abbey Wood  51.49078408 0.120286371 Acton   51.51688696 -0.267675543
Abbey Wood  51.49078408 0.120286371 Acton Central   51.50875781 -0.263415792
Acton   51.51688696 -0.267675543    Abbey Road  51.53195199 0.003737786
Acton   51.51688696 -0.267675543    Abbey Wood  51.49078408 0.120286371
Acton   51.51688696 -0.267675543    Acton Central   51.50875781 -0.263415792

The first step would be to pair any station using a loop with all of the other possible stations. I would then need to remove the 0 combinations where an origin and destination were the same station.

Ive tried using the NumPy function column_stack. However this gives a strange result.

import csv
import numpy
from pprint import pprint
numpy.set_printoptions(threshold='nan')

with open('./London stations.csv', 'rU') as csvfile:
    reader = csv.DictReader(csvfile)
    Stations = ['{O_Station}'.format(**row) for row in reader]
print(Stations)
O_D = numpy.column_stack(([Stations],[Stations]))
pprint(O_D)

OUTPUT

Stations =

['Abbey Road', 'Abbey Wood', 'Acton', 'Acton Central', 'Acton Town']

O_D =

array([['Abbey Road', 'Abbey Wood', 'Acton', 'Acton Central', 'Acton Town',
        'Abbey Road', 'Abbey Wood', 'Acton', 'Acton Central', 'Acton Town']], 
      dtype='|S13')

I am ideally looking for more suitable function and finding it hard to locate it in the Numpy manual.

Answer 1

This is an incomplete answer, but I would skip numpy and head right into pandas:

csv_file = '''Station Lat Lng
Abbey Road  51.53195199 0.003737786
Abbey Wood  51.49078408 0.120286371
Acton   51.51688696 -0.267675543
Acton Central   51.50875781 -0.263415792
Acton Town  51.50307148 -0.280288296'''

This is tough since it isn't really comma-delimited, otherwise we could just call pandas.read_csv():

names = [' '.join(x.split()[:-2]) for x in stations]
lats = [x.split()[-2] for x in stations]
lons = [x.split()[-1] for x in stations]

stations_dict = {names[i]: (lats[i], lons[i]) for i, _ in enumerate(stations)}

df = pd.DataFrame(stations_dict).T    # Transpose it
df.columns = ['Lat', 'Lng']
df.index.name = 'Station'

So we end up with df.head() yielding:

                       Lat           Lng
Station
Abbey Road     51.53195199   0.003737786
Abbey Wood     51.49078408   0.120286371
Acton          51.51688696  -0.267675543
Acton Central  51.50875781  -0.263415792
Acton Town     51.50307148  -0.280288296

Getting the permutations might mean we need to not have the Stations as the index... Not sure for the moment. Hopefully this helps a bit!

Answer 2

When working with tabular data like this I prefer to use pandas. It makes controlling your data structure simple.

import pandas as pd

#read in csv
stations = pd.read_csv('london stations.csv', index_col = 0)

#create new dataframe
O_D = pd.DataFrame(columns = ['O_Station','O_lat','O_lng','D_Station','D_lat','D_lng'])

#iterate through the stations

new_index= 0
for o_station in stations.index:
    for d_station in stations.index:
        ls = [o_station,stations.Lat.loc[o_station],stations.Lng.loc[o_station],d_station, stations.Lat.loc[d_station], stations.Lng.loc[d_station]]
        O_D.loc[new_index] = ls
        new_index+=1

#remove double stations
O_D = O_D[O_D.O_Station != O_D.D_Station]

This should do the trick for your data transform.