Vwwxrf

I don't know how to do this without four nested for loops.

I'd like to apply a function to every possible combination of subsets for hour and day, return that value, and then pivot the data frame into a square matrix. However, these for loops seem unnecessary so I'm looking for a more efficient way to do this. The data I have is fairly large and takes a long time so any gain in speed would be beneficial.

I took a stab at compression lists but that seems excessive too.

Note: this code runs but will produce NA because all possible combinations are not available.

Sample data

dat = pd.DataFrame('day': 0: 10, 1: 10, 2: 10, 3: 11, 4: 11, 5: 13, 6: 14, 7: 14, 8: 14, 9: 15, 10: 16, 11: 16, 12: 16, 13: 17, 14: 17, 15: 18, 16: 19, 17: 20, 18: 20, 19: 20, 'hour': 0: 0, 1: 19, 2: 22, 3: 14, 4: 16, 5: 5, 6: 1, 7: 18, 8: 20, 9: 8, 10: 6, 11: 14, 12: 15, 13: 2, 14: 6, 15: 12, 16: 22, 17: 0, 18: 3, 19: 4, 'distance': 0: 1.2898851269657656, 1: 0.0, 2: 0.8371526423804061, 3: 0.8703856587273138, 4: 0.6257425922449789, 5: 0.0, 6: 0.0, 7: 0.0, 8: 1.2895328696587023, 9: 0.0, 10: 0.6875527848294374, 11: 0.0, 12: 0.0, 13: 0.9009031833559706, 14: 0.0, 15: 1.1040652963428623, 16: 0.0, 17: 0.0, 18: 0.0, 19: 0.0)

Code

def mean_diff(x, y):
 x = pd.Series(x)
 y = pd.Series(y)
 return x.mean() - y.mean()


dmat = pd.DataFrame()
for i in dat['hour'].unique():
 for j in dat['hour'].unique():
 for k in dat['day'].unique():
 for l in dat['day'].unique():
 x = dat[(dat['hour'] == i) & (dat['day'] == k)].distance
 y = dat[(dat['hour'] == j) & (dat['day'] == l)].distance

 # Calculate difference
 jds = mean_diff(x, y)

 # Build data frame and append
 outdat = pd.DataFrame('day_hour_a': f"k_i", 'day_hour_b': f"l_j", 'jds': [round(jds, 4)])
 dmat = dmat.append(outdat, ignore_index=True)

# Pivot data to get matrix
distMatrix = dmat.pivot(index='day_hour_a', columns='day_hour_b', values='jds')