Finding the difference in one column in a Pandas GroupBy

Question

I am trying to calculate the time difference between samples that a satellite takes at the same location. I have data currently in a Pandas DataFrame that has latitude, longitude, and time of the sample. Here is a snapshot of the data (it has some extra columns that can be ignored):

       JulianDay      LatSp       LonSp        AltSp        LandMask
34     2.459581e+06  19.699432 -105.036661   410.853638         1
35     2.459581e+06  20.288866 -105.201204  1378.320140         1
36     2.459581e+06  20.808230 -105.350132   271.934574         1
39     2.459581e+06  22.415461 -105.698367   -16.805644         1
40     2.459581e+06  22.948721 -105.799142   164.985525         1

The data though does not need to be exactly at the same location. The resolution is of ~11kmx11km square (0.1x0.1 degrees). So I get an approximate latitude and longitude with the following:

specular_df['approx_LatSp'] = round(specular_df['LatSp'],1)
specular_df['approx_LonSp'] = round(specular_df['LonSp'],1)

The final step (which takes 2 hours for a small sample of the data that I need to run), is to group the data into the given squares and calculate the time difference between each sample inside the square. For this, my intuition points me toward groupby, but then I am not sure how to get the time difference without using a for loop. This for loop is the part that takes two hours. Here is the code I have written for now:

grouped = specular_df.groupby(['approx_LatSp', 'approx_LonSp'])

buckets = pd.DataFrame(columns=['bucket_LatSp', 'bucket_LonSp', 'SpPoints', 'Revisits', 'MaxRevisit'])

for key in tqdm(list(grouped.groups.keys())):
    group = grouped.get_group(key)
    times = group['JulianDay'].tolist()
    times = sorted(times + [sim_end, sim_start])
    diff = [t - s for s, t in zip(times, times[1:])]
    temp = {'bucket_LatSp': key[0], 'bucket_LonSp': key[1], 'SpPoints': group.to_dict(), 'Revisits': diff, 'MaxRevisit': max(diff)}
    buckets = buckets.append(temp, ignore_index=True)

A couple of notes here. The time difference between samples is what is known as Revisit (I store a list of time differences in the revisit column). Since this is just data from a simulation, if there are only two data points in a square and they are close together it could lead to a revisit time that is short (eg simulation of 3 days, samples happen during the first two hours. The difference will be (at most) 2 hours, when in truth it should be closer to 3 days). For this reason I add the simulation start and end in order to get a better approximation of the maximum time difference between samples.

The part that I am stuck on is how to compress this for loop whilst still getting similar data (If it is fast enough I wouldn't need the SpPoints column anymore as that is just storing the precise time, and location of each point in that square).

Any suggestions would be greatly appreciated!

Answer 1

I was recently able to resolve this by removing one of my constraints. In the original post I noted that I need to include the start and end time to make up for low simulation time. The actual solution to low simulation time is quite simple; increase the simulation time. I also add a condition that revisit is only counted if it is longer than a couple of hours. With that, you get some pretty reasonable estimates.

Calculating revisit is still a bit painful though. I include below the code that I used for this, but the difficult part came from this StackOverflow post.

# Round lat and long and then use groupby to throw them all in similar buckets
specular_df['approx_LatSp'] = round(specular_df['LatSp'],1)
specular_df['approx_LonSp'] = round(specular_df['LonSp'],1)

# Calculate time difference
specular_df.sort_values(by=['approx_LatSp', 'approx_LonSp', 'JulianDay'], inplace=True)
specular_df['revisit'] = specular_df['JulianDay'].diff()

# Correct for borders
specular_df['revisit'].mask(specular_df.approx_LatSp != specular_df.approx_LatSp.shift(1), other=np.nan, inplace=True)
specular_df['revisit'].mask(specular_df.approx_LonSp != specular_df.approx_LonSp.shift(1), other=np.nan, inplace=True)

# Get max revisit and store in new DF
indeces = specular_df.groupby(['approx_LatSp', 'approx_LonSp'])['revisit'].transform(max) == specular_df['revisit']

max_rev_area_df = specular_df[indeces]
max_rev_area_df['revisit'].mask(max_rev_area_df['revisit'] < 0.04, other=np.nan, inplace=True)