check if an element is a neighbor and then create a new column

user_18212173 0
Viewed 80

I have a dataframe which consists of the five columns "ID", "Name", "pos_x", "pos_y" and "Volume", something like this:

ID | Name | pos_x | pos_y | volume
1  |  A   |  1    |  1.5  |  10
2  |  A   |  3.5  |  3    |   6
3  |  A   |  4    |  4    |   8
4  |  A   |  4.5  |  4.5  |   9
5  |  A   |  5    |  6    |  10
1  |  B   |  1.2  |  1.2  |   4
3  |  B   |  4.3  |  4.4  |   8
4  |  B   |  4.5  |  4.2  |   7
2  |  C   |  3    |  3.3  |   9
3  |  C   |  4.2  |  4.1  |  10

I would now like to create a new column in the dataframe ("volume_avg") in which the average of the volume of all ID`s is calculated, which lie within a radius of 2.5 mm to the respective ID.

My idea was to first run through each ID for each name (because the name + ID is the primary key) and then look at the position x and y for each ID. With an if loop I would then check if the ID is a neighbor or not.

So the conditions would be:

if pos_x (current ID) < pos_x (all other IDs) - 2.5 OR > pos_x (all other IDs) + 2.5

else if pos_y (current ID) < pos_y (all other IDs) - 2.5 OR > pos_y (all other IDs) + 2.5

then calculate sum(volume)/count(ID's)

otherwise nothing

Unfortunately I don't know how to write these considerations in a python code.... I would be glad if you can help me. Thanks a lot.

David

if-statement for-loop python
2 Answers

I'm interpreting the conditions in the if and the else portion to be rectangular limits on the (x,y) coordinates. If this is incorrect please let me know and I can adjust the logic in the if/else.

import pandas as pd

# create the structure of example dataframe
df = pd.DataFrame([[1, 'A', 1, 1.5, 10],
                   [2, 'A', 3.5, 3, 6],
                   [3, 'A', 4, 4, 8],
                   [4, 'A', 4.5, 4.5, 9],
                   [5 ,'A' , 5 , 6 , 10],
                   [1 ,'B' , 1.2 , 1.2 , 4],
                   [3 ,'B' , 4.3 , 4.4 , 8],
                   [4 ,'B' , 4.5 , 4.2 , 7],
                   [2 ,'C' , 3 , 3.3 , 9],
                   [3 ,'C' , 4.2 , 4.1 , 10],
                   ],
                  columns=['ID', 'Name', 'pos_x', 'pos_y', 'volume'])

# create volume average with rows that meet criterion
volume_avgs = []
for g, grouper in df.groupby('Name'):
    for base_row in grouper.iterrows():
        v_sum = 0
        v_count = 0
        for row in grouper.iterrows():
            if row[1].ID == base_row[1].ID: # skip the item itself
                continue
            elif abs(row[1].pos_x - base_row[1].pos_x) < 2.5 or abs(row[1].pos_y - base_row[1].pos_y) < 2.5:
                 v_sum += row[1].volume
                 v_count += 1
        volume_avgs.append([base_row[1].ID, g, v_sum / v_count if v_count > 0 else 0, v_count])

df_vol_avgs = pd.DataFrame(volume_avgs, columns=['ID', 'Name', 'volume_avg', 'volume_count'])

merged_df = df.merge(df_vol_avgs, on=['ID', 'Name'])
merged_df

Be aware that if the actual data are large this probably won't scale very well. Particularly to groups with a lot of data because of the double for loop. Also if you want a None or Nan if the total number of nearby points to average is 0 you can change the if/else from

v_sum / v_count if v_count > 0 else 0

to

v_sum / v_count if v_count > 0 else None

for the None case and similarly for Nan, say using numpy.nan.

user_31641000

I have found a method that works but, however, isn't the cleanest and can probably be optimized in many ways.

First, we import pandas and create the dataframe:

import pandas as pd
ID = [1,2,3,4,5,1,3,4,2,3]
name = ["A","A","A","A","A","B","B","B","C","C"]
pos_x = [1,3.5,4,4.5,5,1.2,4.3,4.5,3,4.2]
pos_y = [1.5,3,4,4.5,6,1.2,4.4,4.2,3.3,4.1]
volume = [10,6,8,9,10,4,8,7,9,10]
dict = {"ID": ID, "name": name, "pos_x":pos_x, "pos_y":pos_y,"volume":volume}
df = pd.DataFrame(dict)

Then, we declare an empty list to store our results and iterate over the dataframe:

values = []
for i in df.itertuples():
    posX = i.pos_x
    posY = i.pos_y
    counter = 0
    volume_counter = 0
    for j in df.itertuples():
        if i == j:
            pass
        elif abs(j.pos_x - posX) <= 2.5 and abs(j.pos_y - posY) <= 2.5:
            counter += 1
            volume_counter += j.volume
        else:
            pass
    values.append(round(volume_counter/counter,2))

For each row of the dataframe, we get the x and y position. We also set counter and volume_counter to 0. Counter counts how many items fulfill the condition of being within 2.5 mm from other positions, and volume_counter adds up the volumes of the rows that fulfill the condition.

Then, we iterate again for every row in the dataframe. The first if clause discards the value if it is itself (if you want to include the volume of every object in the dataframe you can simply delete this clause), the elif clause checks if x_pos and y_pos are within an absolute distance of 2.5 mm.

Finally, we append the total volume divided by the number of items (I've also rounded up the result to 2 decimal places, but you can also remove it if you want more decimals). This gives us the following list:

[6.33, 8.12, 8.43, 8.29, 8.4, 8.33, 8.43, 8.57, 7.75, 8.14]

We can therefore join the calculated list to the original dataframe.

df["volume_avg"] = values
df

The output is as follows:

   ID name pos_x  pos_y  volume volume_avg
0   1   A   1.0     1.5     10  6.33
1   2   A   3.5     3.0     6   8.12
2   3   A   4.0     4.0     8   8.43
3   4   A   4.5     4.5     9   8.29
4   5   A   5.0     6.0     10  8.40
5   1   B   1.2     1.2     4   8.33
6   3   B   4.3     4.4     8   8.43
7   4   B   4.5     4.2     7   8.57
8   2   C   3.0     3.3     9   7.75
9   3   C   4.2     4.1     10  8.14

I hope this helps. I am sure that there are cleaner ways to do this, and I'd also appreciate feedback on the proposed solution!

user_182116090
Related