I have a csv file that looks like

I'm trying to subtract the latter from the first value with the same identifier so that it would eventually turn out something like

To do this, I use this

df = pd.read_csv(DF, usecols=['Time', 'OpenIA'])
df['Time'] = pd.to_datetime(df['Time'])
df['Time'] = df['Time'].dt.ceil("S", 0)
b = df.drop_duplicates(subset=['Time'], keep='last') - df.drop_duplicates(subset=['Time'], keep='first')

But instead of the expected I get

CodePudding user response：

You can use groupby.first/last:

g = df.groupby('Time', sort=False)
out = (g.first()-g.last()).reset_index()

output:

                  Time  OpenIA
0  2022-07-15 10:00:23       0
1  2022-07-15 10:01:11       2
2  2022-07-15 10:01:33      -1

CodePudding user response：

try this

df.groupby('Time').agg(diff=('OpenIA', lambda x: x[-1]-x[0]) )