I've got a CSV file containing the distance between centroids in a GIS-model in the next format:
InputID,TargetID,Distance
1,2,3050.01327866
1,7,3334.99565217
1,5,3390.99115304
1,3,3613.77046864
1,4,4182.29900892
...
...
3330,3322,955927.582933
It is sorted on origin (InputID
) and then on the nearest destination (TargetID
).
For a specific modelling tool I need this data in a CSV file, formatted as follows (the numbers are the centroid numbers):
distance1->1, distance1->2, distance1->3,.....distance1->3330
distance2->1, distance2->2,.....
.....
distance3330->1,distance3330->2....distance3330->3330
So no InputID's or TargetID's, just the distances with the origins on the rows and the destinations on the columns: (example for the first 5 origins/destinations)
0,3050.01327866,3613.77046864,4182.29900892,3390.99115304
3050.01327866,0,1326.94611797,1175.10254872,1814.45584129
3613.77046864,1326.94611797,0,1832.209595,3132.78725738
4182.29900892,1175.10254872,1832.209595,0,1935.55056767
3390.99115304,1814.45584129,3132.78725738,1935.55056767,0
I've built the next code, and it works. But it is so slow that running it will take days to get the 3330x3330 file. As I am a beginner in Python I think I am overlooking something...
import pandas as pd
import numpy as np
file=pd.read_csv('c:\\users\\Niels\\Dropbox\\Python\\centroid_distances.csv')
df=file.sort_index(by=['InputID', 'TargetID'], ascending=[True, True])
number_of_zones=3330
text_file = open("c:\\users\\Niels\\Dropbox\\Python\\Output.csv", "w")
for origin in range(1,number_of_zones):
output_string=''
print(origin)
for destination in range(1,number_of_zones):
if origin==destination:
distance=0
else:
distance_row=df[(df['InputID']==origin) & (df['TargetID'] == destination)]
# I guess this is the time-consuming part
distance=distance_row.iloc[0]['Distance']
output_string=output_string+str(distance)+','
text_file.write(output_string[:-1]+'\n') #strip last ',' of line
text_file.close()
Could you give me some hints to speed up this code?
IIUC, all you need is pivot
. If you start from a frame like this:
df = pd.DataFrame(columns="InputID,TargetID,Distance".split(","))
df["InputID"] = np.arange(36)//6 + 1
df["TargetID"] = np.arange(36) % 6 + 1
df["Distance"] = np.random.uniform(0, 100, len(df))
df = df[df.InputID != df.TargetID]
df = df.sort(["InputID", "Distance"])
>>> df.head()
InputID TargetID Distance
2 1 3 6.407198
3 1 4 43.037829
1 1 2 52.121284
4 1 5 86.769620
5 1 6 96.703294
and we know the InputID and TargetID are unique, we can simply pivot
:
>>> pv = df.pivot(index="InputID", columns="TargetID", values="Distance").fillna(0)
>>> pv
TargetID 1 2 3 4 5 6
InputID
1 0.000000 52.121284 6.407198 43.037829 86.769620 96.703294
2 53.741611 0.000000 27.555296 85.328607 59.561345 8.895407
3 96.142920 62.532984 0.000000 6.320273 37.809105 69.896308
4 57.835249 49.350647 38.660269 0.000000 7.151053 45.017780
5 72.758342 48.947788 4.212775 98.183169 0.000000 15.702280
6 32.468329 83.979431 23.578347 30.212883 82.580496 0.000000
>>> pv.to_csv("out_dist.csv", index=False, header=False)
>>> !cat out_dist.csv
0.0,52.1212839519,6.40719759732,43.0378290605,86.769620064,96.7032941473
53.7416111725,0.0,27.5552964592,85.3286070586,59.5613449796,8.89540736892
96.1429198049,62.5329836475,0.0,6.32027280686,37.8091052942,69.8963084944
57.8352492462,49.3506467609,38.6602692461,0.0,7.15105257546,45.0177800391
72.7583417281,48.9477878574,4.21277494476,98.183168992,0.0,15.7022798801
32.4683285321,83.9794307564,23.578346756,30.2128827937,82.5804959193,0.0
The reshaping section of the tutorial might be useful.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With