I recognise this nested approach isn't really how pandas is designed to work and there likely isn't any particularly fast solution, but I'd appreciate any help.
I have a pandas DataFrame, one column of which contains lists of integers. I would like to, for every row, find every (non-identical, e.g. not (1,1)) pair of integers in this list, and perform an operation on it. These lists are not necessarily the same length.
The extra detail is that each row contains a 3D vertex and these integers are IDs for another 3D vertex, stored in a separate DataFrame. For every row I want to find the angle between all possible pairs of hits, with the row's vertex as the origin, then average and do some other stuff to get "beta". Mathematically pretty simple but the number of rows I have to run this on is huge so I'd like to speed it up as much as possible.
I've tried two approaches.
Approach 1 - apply()
The first (non-vectorised) approach I've taken is to have a separate function which takes the row, makes a new 2-column DataFrame of the pairs of integers generated using itertools.combinations. I then use joins to get the vertex information and perform my operations. I then just use pd.DataFrame.apply().
Here's the simplified code without the actual calculations:
# Geometry df, map of id (cable) to vertex
geo = geo[["cable","x","y","z"]
def _beta_single(row):
# "cable" is the ID (integer)
cables = event["cable"]
pairs = [combo for combo in combinations(cables,2)]
pairs = pd.DataFrame(pairs, columns=["cable_1","cable_2"])
# Rename geo to have suffixes of vertex after merge
geo.columns = geo.columns.map(lambda x: str(x) + "_1")
# Get both hit locations
pairs = pairs.merge(geo, on="cable_1")
# Get rid of _1 suffix, add _2
geo.columns = geo.columns.map(lambda x: str(x)[:-2] + "_2")
pairs = pairs.merge(geo, on="cable_2")
# Perform calculations to get "beta" value (float)
row["beta"] = dostuff(pairs)
df = df.apply(_beta_single, axis=1)
This is VERY slow. There are probably some optimisations that could help but for >100k rows, 200C2 pairs, it looks to be on the order of a few hours to process.
Approach 2 - Columns Galore
The second approach is to make a new column in the df for every integer in the list like so:
nhits = df["cable"].str.len()
hit_cols = ["cable_%i" % (x+1) for x in range(max_nhits)]
# Convert cable column to list of lists
cable_lists = df["cable"].tolist()
# Make df of hits
df[hit_cols] = pd.DataFrame(cable_lists, index=df.index)
I then again find all possible combinations using itertools.combinations, but this time of all the possible column pairs like:
col_pairs = [combo for combo in combinations(range(1,(max_nhits+1)),2)]
and loop over these, merging the columns in the pair with the vertex map to get the two vertices:
for col_pair in col_pairs:
# Column suffix
s1 = "_%i" % col_pair[0]
s2 = "_%i" % col_pair[1]
cables_1 = df["cable" + s1]
cables_2 = df["cable" + s2]
geo_1 = pd.merge(cables_1, geo, left_on=("cable" + s1), right_on="cable")
geo_2 = pd.merge(cables_2, geo, left_on=("cable" + s2), right_on="cable")
beta = dostuff_vector(geo_1, geo_2)
Sorry for the pseudo code but the maths isn't really important here so it's clearer if I leave it out.
This method is definitely faster than the other, but still on the order of half an hour for the same size df as mentioned in approach 1.
Sorry for the long post, I just wanted to show what I've already played with. I guess what I'm looking for is a nice vectorised itertools-style thing. I've thought about with having a column of itertools.combinations objects but you hit a wall with the nested iteration. I've been advised using something like groupby in some form may be best, but I'm not really sure what that would look like in this case.