Introduction

While searching for a better way .., I will summarize it in this way for the time being. ** (If you know a better way, please let me know !!) **

Thing you want to do

① Basic As shown in the figure below, there is Excel that stores 3D coordinate points, and I want to link numbers with the same coordinates. In the example below, the coordinates of S / N-2 and the coordinates of S / N-3 (highlighted in light blue) are exactly the same, so they are judged to be "the same coordinates!".

② Application As the specification of the target I am working on this time, it is difficult for the coordinates to match exactly. Therefore, I would like to judge that they match with a certain amount of Margin. For example, the coordinates of S / N-8 and the coordinates of S / N-9 (highlighted in yellow) match ** almost **, so please judge that they are also "same coordinates".

This time, I couldn't think of another good idea for this "application". .. If you want to extract only the exact match, I thought that you should combine the X coordinate, Y coordinate, and Z coordinate as a character string and judge whether they match.

The resulting code looks like this

First, create a function that can be judged including margin. In the following cases, the error ± 100 is ignored, and True is returned if the three coordinates match with margin included.

``````
def coordinates_check(ax,ay,az,bx,by,bz):
margin=100
flag = 0
result=False

if ax-margin < bx < ax+margin:
flag = flag +1
if ay-margin < by < ay+margin:
flag = flag +1
if az-margin < bz < az+margin:
flag = flag +1

if flag >2:
result = True

return result
``````

Use this function to read the lines of the excel file from above. The start number of the second for statement is devised so that the readings are not duplicated.

``````df_test = pd.read_excel('sample.xlsx')

for i in range(len(df_test)):
ax= df_test.loc[i,"X coordinate"]
ay= df_test.loc[i,"Y coordinate"]
az= df_test.loc[i,"Z coordinate"]
k = i+1
for j in range(k,len(df_test)):
bx = df_test.loc[j,"X coordinate"]
by = df_test.loc[j,"Y coordinate"]
bz = df_test.loc[j,"Z coordinate"]
if coordinates_check(ax,ay,az,bx,by,bz):
print("Matched:",df_test.loc[i,"S/N"],"-->",df_test.loc[j,"S/N"])
``````

As a result, it is done well.

I wish I had a small amount of data ..

The data I want to use this time is over 10,000 lines, and it took ** 3 hours ** to execute the above code ... It was around this time that I thought there should be a better way. If you know a good way, please help!

Recommended Posts