Euclidean Distance Matrix Using Pandas
<p>I have a .csv file that contains city, latitude and longitude data in the below format:</p>

<pre><code>CITY|LATITUDE|LONGITUDE
A|40.745392|-73.978364
B|42.562786|-114.460503
C|37.227928|-77.401924
D|41.245708|-75.881241
E|41.308273|-72.927887
</code></pre>

<p>I need to create a distance matrix in the below format (please ignore the dummy values):</p>

<pre><code>         A         B         C         D         E   
A  0.000000  6.000000  5.744563  6.082763  5.656854  
B  6.000000  0.000000  6.082763  5.385165  5.477226  
C  1.744563  6.082763  0.000000  6.000000  5.385165
D  6.082763  5.385165  6.000000  0.000000  5.385165  
E  5.656854  5.477226  5.385165  5.385165  0.000000  
</code></pre>

<p>I have loaded the data into a pandas dataframe and have created a cross join as below:</p>

<pre><code>import pandas as pd
df_A = pd.read_csv('lat_lon.csv', delimiter='|', encoding="utf-8-sig")
df_B = df_A
df_A['key'] = 1
df_B['key'] = 1 
df_C = pd.merge(df_A, df_B, on='key')  
</code></pre>

<ul>
<li>Can you please help me create the above matrix structure?</li>
<li>Also, is it possible to avoid step involving cross join?</li>
</ul>

<pre><code>for i in df["CITY"]:
    for j in df["CITY"]:
        row = df[df["CITY"] == j][["LATITUDE", "LONGITUDE"]]
        latitude = row["LATITUDE"].tolist()[0]
        longitude = row["LONGITUDE"].tolist()[0]
        df.loc[df['CITY'] == i, j] = ((df["LATITUDE"] - latitude)**2 + (df["LONGITUDE"] - longitude)**2)**0.5

df = df.drop(["CITY", "LATITUDE", "LONGITUDE"], axis=1)
</code></pre>

<p>This works</p>

python pandas dataframe