Python groupby ID and increment the associated column

Question

I have the following data in a pandas data frame. The data is sampled at 50khz hence the 'microsec' filed has to incremented for each ID group by 50.

Data ----ID ---- microsec

0.304 ----1 ---- 1530348553000
0.276 ----1 ----15303485530000
0.276 ----1 ----15303485530000
0.276 ----2 ----15303490090000
0.276 ----2 ----15303490090000
0.304 ----2 ----15303490090000
0.276 ----3 ----15303553530000
1.359 ----3 ----15303753680000
1.443 ----3 ----15303753680000

Output Required

Data ----ID ---- microsec

0.304 ----1 ---- 1530348553000
0.276 ----1 ----15303485530050
0.276 ----1 ----15303485530100
0.276 ----2 ----15303490090000
0.276 ----2 ----15303490090050
0.304 ----2 ----15303490090100
0.276 ----3 ----15303553530000
1.359 ----3 ----15303753680050
1.443 ----3 ----15303753680100

CODE

import numpy as np
from itertools import chain

lens = list(map(len, df['Data'].str.split('|')))
df['microsec'] = pd.DatetimeIndex ( df['DateTime'] ).astype ( np.int64 )// 10 ** 9

df['Data'] = df['Data'].str.replace(',','.')

res = pd.DataFrame({'ID': np.repeat(df['ID'], lens),
                    'microsec': np.repeat(df['microsec']*10000, lens),
                    'Data': list(chain.from_iterable(df['Data'].str.split('|')))    
                   })

res[['Data']] = res[['Data']].astype(float)
res.to_csv('samplefile.txt', index=False)

What I tried

df_groups = res.groupby('MeasurementID')
for MeasurementID,microsec in df_groups:
     microsec = microsec*50
     print(microsec)

But I did not achieve my desired output. Please let me know where I am doing wrong.

It seems need `groupby + cumsum`, check [this](https://stackoverflow.com/q/41808951) — jezrael, Jul 04 '18 at 11:21
Please read the [help files](http://stackoverflow.com/help/how-to-ask). This question is not well written. — Tom Zych, Jul 04 '18 at 11:22
Hello Nrithya, could you edit your question by providing an actual question: explain to us what is the result you would like to achieve. Thank you. — Ivan, Jul 04 '18 at 11:24
@TomZych : I was still in process of editing , and I didnt realise it was posted. Thank you though :) — Nrithya M, Jul 04 '18 at 11:42

ajit samudrala · Accepted Answer · 2018-07-04T15:21:00.630

1

I think below code should work.

def func(col,base):
    for i in range(len(col)):
        col.iloc[i]=col.iloc[i]+base
        base=base+50
    return col

df.groupby(['Data','ID'])['microsec'].transform(lambda x:func(x,0))

edited Jul 04 '18 at 15:21

answered Jul 04 '18 at 14:30

ajit samudrala

50
8

Python groupby ID and increment the associated column

1 Answers1