simple operations with python on excel file

Question

x , y , particle

23, 25, 0

12, 15, 0

54, 45, 0

32, 11, 1

21, 43, 1

43, 11, 1

There is a yield of 3 columns in excel. First column x, second column y, third column ID. If the particles have the same identification number(ID), I want to subtract x and y of particles with the same ID number. for example;

For columns with ID = 0, I want it to:

for x should be
23-12 = 11
11-54 = -43

for y should be

25-15 = 10

10-45 = -35

Here's the code I wrote for it. The first is working correctly for the same ID numbers, but I get a range error in the 2nd cycle. what could be the problem. How can I fix.

from pyexcel_ods import get_data,save_data

# -*- coding: utf-8 -*-

data = get_data("deneme.ods")

s,extract=0,0

for i in range(len(data[u'Sheet1'])-2):
if data[u'Sheet1'][i][2]==data[u'Sheet1'][i+1][2]:
    s+=1
    continue
else:
    for j in range(s+1):
        extract -= data[u'Sheet1'][j][1]
        extract = abs(extract)
    data[u'Sheet1'][i].append(extract)
    extract=0
    s=0

save_data('deneme1.ods',data)

Answer 1

if you can load your file into a dataframe, you can then do like this.

import pandas as pd
from operator import sub
from functools import reduce

d = [["x", "y", "particle"], [23, 25, 0],[12, 15, 0], [54, 45, 0], [32, 11, 1], [21, 43, 1], [43, 11, 1]]

df = pd.DataFrame(d[1:], columns=d[0])

my_sub = lambda e: reduce(sub, e)

df2 = df.pivot_table(index = "particle", values=["x", "y"], aggfunc=my_sub)
print(df2)