Context of the Problem
I am running a discrete event simulation where at the end of each event I store the state of the system in a row of a dataframe. Each simulation run has a clock which determines which events are run and when, all the simulation have the same initial clock (0) and the same end clock (simulation end). but the number of rows for the dataframes may be different because the simulation has several stochastic components. The clock column is then converted to timestamp and use as index of the dataframe.
Whenever one does a simulation, it is good practice to run it many times and then average over all the replicates, doing so in this setup is a bit complicated because each simulation produced a dataframe which different indexes.
So far this is the solution I found:
# Auxiliary functions def get_union_index(dfs): index_union = dfs.index for df in dfs[1:]: index_union = index_union.union(df.index) return pd.Series(index_union).drop_duplicates().reset_index(drop=True) def interpolate(df, index, method='time'): aux = df.astype(float) aux = aux.reindex(index).interpolate(method=method).fillna(method='ffill').fillna(method='bfill') return aux # Simulation dfs =  replicates = 30 seeds = list(range(40, 40 + replicates)) dfs = [simulate(seed=seed, **parameters) for seed in seeds] # Main Code union_index = get_union_index(dfs) dfs_interpolates = [interpolate(df, union_index) for df in dfs] df_concat = pd.concat(dfs_interpolates) by_row_index = df_concat.groupby(df_concat.index) # Averaging df_means = by_row_index.mean() df_std = by_row_index.std()
First, it is necessary to combine all the indexes, then this combined index is used to re-index all the dataframes and the
nan values are filled using interpolation.
Is there a native pandas function that could simplify this?
(If not 1) Is there an alternative way to combine the datasets directly, since the majority of the index are disjoint,
union_indexhas a length of approximately
len(df) * len(dfs)which is actually huge.
Which should be the best interpolation method to use in the
interpolate? Since each row is an event and only a few variables are changed per event, from one row to the next only a few columns are modified, making it possible to have several consecutive rows with identical values in several columns (but not all).