Simple Split Apply Combine, custom function

Question

I am using the split-apply-combine pattern in pandas to create a new column, which measures the difference between two time stamps.

The following is a simplified example of my problem.

Say, I have this df

df = pd.DataFrame({'ssn_start_utc':pd.date_range('1/1/2011', periods=6, freq='D'),  'fld_id':[100,100,100,101,101,101], 'task_name': ['sowing','fungicide','insecticide','combine',''combine','sowing']})
df

I want to group by fld_id and apply a function which creates a column measuring difference between two timestamps for each row. Such as this

def pasttime(group):
    val = group['ssn_start_utc'] - group['ssn_start_utc'][0]
    

    # why group['ssn_start_utc'][0] ? 
    # Because it measures time difference for each row respective to first row of each group/ particular to *sowing* entry respective to each group. I have moved all *sowing* entries to first row of df for each group 
    
    return val

df["PastTime"] =df.groupby('fld_id',group_keys=False).apply(pasttime)

the resultant column df should look like this

df_new = pd.DataFrame({'ssn_start_utc':pd.date_range('1/1/2011', periods=6, freq='D'),  'fld_id':[100,100,100,101,101,101], 'task_name': ['sowing','fungicide','insecticide','combine',''combine','sowing'], 'pasttime' :[ 0 days, 1 days, 2 days, 3 days, -1 days, 0 days] })

df_new

I get a error KeyError: 0

I have also tried using groupby:

df['pasttime'] = df.groupby(['fld_id'])['ssn_start_utc'].transform( df['ssn_start_utc'] - df.loc[df['name']=='sowing','ssn_start_utc'].values[0])

How to apply a custom group function and have the desired df?

#Apply function which moves "sowing" entries to first position for each group ```def moverow(group): group = pd.concat([group[group.Productname=='sowing'],group[group.Productname!='sowing']]) How can I add this function also to my above function? I am using groupby command twice right now. — yash agarwal, Jul 29 '21 at 07:22

jezrael · Accepted Answer · 2021-07-29T07:25:48.693

In your function is possible match first value by position with Series.iat:

def pasttime(group):
    val = group['ssn_start_utc'] - group['ssn_start_utc'].iat[0]
    return val

df["PastTime"] =df.groupby('fld_id',group_keys=False).apply(pasttime)

Fatser alternative is use GroupBy.first with GroupBy.transform:

s = df.groupby('fld_id')['ssn_start_utc'].transform('first')
df['pasttime'] = df['ssn_start_utc'].sub(s)

If need subtrat sowing rows per groups use same solution like above, only first replace not matched datetimes to NaNs by Series.where:

m = df['task_name']=='sowing'
s = df['ssn_start_utc'].where(m).groupby(df['fld_id']).transform('first')
df['pasttime1'] = df['ssn_start_utc'].sub(s)
print (df)
  ssn_start_utc  fld_id    task_name PastTime pasttime pasttime1
0    2011-01-01     100       sowing   0 days   0 days    0 days
1    2011-01-02     100    fungicide   1 days   1 days    1 days
2    2011-01-03     100  insecticide   2 days   2 days    2 days
3    2011-01-04     101      combine   0 days   0 days   -2 days
4    2011-01-05     101      combine   1 days   1 days   -1 days
5    2011-01-06     101       sowing   2 days   2 days    0 days

Simple Split Apply Combine, custom function

1 Answers1