What's the idiomatic way to perform an aggregate and rename operation in pandas

Question

For example, how do you do the following R data.table operation in pandas:

PATHS[,.( completed=sum(exists), missing=sum(not(exists)), total=.N, 'size (G)'=sum(sizeMB)/1024), by=.(projectPath, pipelineId)]

I.e. group by projectPath and pipelineId, aggregate some of the columns using possibly custom functions, and then rename the resulting columns.

Output should be a DataFrame with no hierarchical indexes, for example:

                      projectPath pipelineId completed missing size (G)
/data/pnl/projects/TRACTS/pnlpipe          0      2568       0 45.30824
/data/pnl/projects/TRACTS/pnlpipe          1      1299       0 62.69934

Answer 1

You can use groupby.agg:

df.groupby(['projectPath', 'pipelineId']).agg({
        'exists': {'completed': 'sum', 'missing': lambda x: (~x).sum(), 'total': 'size'},
        'sizeMB': {'size (G)': lambda x: x.sum()/1024}
    })

---

Sample run:

df = pd.DataFrame({
        'projectPath': [1,1,1,1,2,2,2,2],
        'pipelineId': [1,1,2,2,1,1,2,2],
        'exists': [True, False,True,True,False,False,True,False],
        'sizeMB': [120032,12234,223311,3223,11223,33445,3444,23321]
    })

df1 = df.groupby(['projectPath', 'pipelineId']).agg({
        'exists': {'completed': 'sum', 'missing': lambda x: (~x).sum(), 'total': 'size'},
        'sizeMB': {'size (G)': lambda x: x.sum()/1024}
    })
​
df1.columns = df1.columns.droplevel(0)
​
df1.reset_index()

---

Update: if you really want to customize the aggregation without using the deprecated nested dictionary syntax, you can always use groupby.apply and return a Series object from each group:

df.groupby(['projectPath', 'pipelineId']).apply(
    lambda g: pd.Series({
            'completed': g.exists.sum(),
            'missing': (~g.exists).sum(),
            'total': g.exists.size,
            'size (G)': g.sizeMB.sum()/1024 
        })
).reset_index()

Answer 2

I believe the new 0.20, more "idiomatic" way, is like this (where the second layer of the nested dictionary is basically replaced by an appended .rename method):

...( completed=sum(exists), missing=sum(not(exists)), total=.N, 'size (G)'=sum(sizeMB)/1024), by=.(projectPath, pipelineId)]... in R, becomes

EDIT: use as_index=False in pd.DataFrame.groupby() to prevent a MultiIndex in final df

df.groupby(['projectPath', 'pipelineId'], as_index=False).agg({
    'exists': 'sum', 
    'pipelineId': 'count', 
    'sizeMB': lambda s: s.sum() / 1024
}).rename(columns={'exists': 'completed', 
                   'pipelineId': 'total',
                   'sizeMB': 'size (G)'})

And then I might just add another line for the inverse of 'exists' -> 'missing':

df['missing'] = df.total - df.completed

---

As an example in a Jupyter notebook test, below there's a mock directory tree of 46 total pipeline paths imported by pd.read_csv() into a Pandas DataFrame, and I slightly modified the example in question to have random data in the form of DNA strings between 1,000-100k nucleotide bases, in lieu of creating Mb-size files. Non-discrete gigabases are still calculated though, using NumPy's np.mean() on the aggregated pd.Series object available within the df.agg call for elaboration of the process but lambda s: s.mean() would be the simpler way to do it.

e.g.,

df_paths.groupby(['TRACT', 'pipelineId']).agg({
    'mean_len(project)' : 'sum',
    'len(seq)' : lambda agg_s: np.mean(agg_s.values) / 1e9
}).rename(columns={'len(seq)': 'Gb',
                   'mean_len(project)': 'TRACT_sum'})

where "TRACT" was a category one-level higher to "pipelineId" in the dir tree, such that in this example you can see there's 46 total unique pipelines — 2 "TRACT" layers AB/AC x 6 "pipelineId"/"project"'s x 4 binary combinations 00, 01, 10, 11 (minus 2 projects which GNU parallel made into a third topdir; see below). So in the new agg the stats transformed the mean of project-level into the sums of all respective projects agg'd per-TRACT.

df_paths = pd.read_csv('./data/paths.txt', header=None, names=['projectPath'])
# df_paths['projectPath'] = 
df_paths['pipelineId'] = df_paths.projectPath.apply(
    lambda s: ''.join(s.split('/')[1:5])[:-3])
df_paths['TRACT'] = df_paths.pipelineId.apply(lambda s: s[:2])
df_paths['rand_DNA'] = [
    ''.join(random.choices(['A', 'C', 'T', 'G'], 
                           k=random.randint(1e3, 1e5)))
    for _ in range(df_paths.shape[0])
]
df_paths['len(seq)'] = df_paths.rand_DNA.apply(len)
df_paths['mean_len(project)'] = df_paths.pipelineId.apply(
    lambda pjct: df_paths.groupby('pipelineId')['len(seq)'].mean()[pjct])
df_paths