How to speed up code search by data frame takes hours

Question

I have a CSV file containing the distance between the centroids in the GIS model in the following 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 by start ( InputID ) and then by the nearest destination ( TargetID ).

For a specific modeling tool, I need this data in a CSV file formatted as follows (numbers are numbers in the center):

 distance1->1, distance1->2, distance1->3,.....distance1->3330 distance2->1, distance2->2,..... ..... distance3330->1,distance3330->2....distance3330->3330 

Thus, there are no InputID or TargetIDs, but only distances with roots in the rows and destinations in the columns: (example for the first 5 sources / 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 built the following code and it works. But it is so slow that it will take several days to start to get the 3330x3330 file. Since I'm new to Python, I think I'm missing 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 tips to speed up this code?

Answer 1

IIUC, you need a total pivot . If you start with a frame as follows:

 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 that InputID and TargetID are unique, we can just 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 may be helpful.