Search code examples
pythonpandaspivot

Make a complicated pivot table using pandas

I have a dataframe like that

df = pd.DataFrame(data={'rem': [2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
       2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
       2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
       2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 3,
       3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3,
       3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3,
       3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3,
       3, 3], 'rp': [22., 22., 22., 22., 22., 22., 22., 22., 22., 22., 22., 22., 22.,
       22., 22., 22., 22., 22., 22., 22., 22., 22., 22., 22., 22., 22.,
       22., 22., 22., 22., 22., 22., 22., 22., 22., 22., 27., 27., 27.,
       27., 27., 27., 27., 27., 27., 27., 27., 27., 27., 27., 27., 27.,
       27., 27., 27., 27., 27., 27., 27., 27., 27., 27., 27., 27., 27.,
       27., 27., 27., 27., 27., 27., 27., 27., 27., 27., 27., 27., 27.,
       27., 27., 27., 27., 27., 27., 27., 27., 27., 22., 22., 22., 22.,
       22., 22., 22., 22., 22., 22., 22., 22., 22., 22., 22., 22., 22.,
       22., 22., 22., 22., 22., 22., 22., 22., 22., 22., 27., 27., 27.,
       27., 27., 27., 27., 27., 27., 27., 27., 27., 27., 27., 27., 27.,
       27., 27., 27., 27., 27., 27., 27., 27., 27., 27., 27., 27., 27.,
       27., 27., 27., 27., 27., 27., 27., 27., 27., 27., 27., 27., 27.], 'road': ['92', '92', '92', '92', '01', '01', '01', '01', '17', '24', '24',
       '28', '28', '28', '51', '51', '51', '58', '58', '61', '61', '63',
       '76', '80', '80', '80', '83', '83', '83', '88', '88', '88', '92',
       '92', '94', '94', '01', '01', '01', '01', '01', '17', '17', '17',
       '17', '24', '24', '24', '24', '24', '24', '28', '28', '28', '28',
       '51', '51', '51', '58', '58', '58', '58', '58', '58', '61', '63',
       '76', '76', '76', '80', '80', '83', '83', '83', '83', '83', '83',
       '83', '88', '88', '88', '88', '88', '92', '92', '92', '94', '94',
       '01', '01', '01', '17', '24', '24', '28', '51', '51', '51', '58',
       '58', '63', '63', '80', '80', '83', '83', '88', '88', '88', '92',
       '92', '92', '94', '94', '01', '01', '01', '01', '01', '17', '17',
       '24', '24', '24', '24', '24', '28', '28', '28', '51', '51', '58',
       '58', '58', '58', '58', '58', '58', '61', '63', '76', '76', '76',
       '80', '83', '83', '83', '83', '83', '88', '88', '88', '88', '92',
       '92', '92'], 'vrp': [ 647.,  647.,  651.,  651.,  323.,  341.,  344., 2566.,  336.,
        371.,  415.,  385.,  391.,  728.,  512.,  561.,  574.,  488.,
       1785.,  676.,  682.,  712.,  588.,  595.,  598.,  600.,  605.,
        621.,  628.,  634.,  635., 2571.,  231.,  653.,  640.,  643.,
        319.,  323.,  341.,  345., 2566.,  180.,  334.,  400., 2048.,
        371.,  414.,  415.,  420.,  423., 1393.,  382.,  385.,  388.,
        391.,  514.,  561.,  562.,  301.,  481.,  492.,  496.,  540.,
       1785.,  682.,  712.,  582.,  585.,  588.,  595.,  598.,  605.,
        611.,  625.,  626.,  628., 1566., 2586.,  443.,  634.,  635.,
       2571., 2603.,  231.,  650., 1314.,  643.,  640.,  319.,  323.,
        341.,  400.,  414.,  415.,  382.,  512.,  561.,  574.,  301.,
       1785.,  311.,  712.,  595.,  598.,  625.,  628.,  634.,  635.,
       2571.,  231.,  650., 1314.,  640.,  643.,  319.,  323.,  341.,
        345., 2566.,  180., 1429.,  414.,  415.,  420.,  423., 1393.,
        382.,  388.,  391.,  561.,  562.,  301.,  480.,  481.,  488.,
        492.,  540., 1785.,  682.,  311.,  582.,  585.,  588.,  600.,
        611.,  621.,  625.,  628., 1566.,  443.,  634.,  635., 2571.,
        231.,  650., 1314.], 'naim': [2., 2., 1., 1., 6., 2., 3., 4., 2., 1., 3., 1., 2., 1., 6., 2., 6.,
       3., 6., 1., 2., 3., 1., 3., 3., 2., 1., 1., 3., 3., 1., 6., 3., 1.,
       1., 2., 3., 6., 2., 2., 4., 6., 3., 3., 6., 1., 3., 3., 6., 3., 6.,
       2., 1., 2., 2., 2., 2., 2., 2., 2., 2., 3., 3., 6., 2., 3., 3., 3.,
       1., 3., 3., 1., 3., 3., 1., 3., 6., 4., 2., 3., 1., 6., 6., 3., 3.,
       4., 2., 1., 3., 6., 2., 3., 3., 3., 2., 6., 2., 6., 2., 6., 6., 3.,
       3., 3., 3., 3., 3., 1., 6., 3., 3., 4., 1., 2., 3., 6., 2., 2., 4.,
       6., 4., 3., 3., 6., 3., 6., 2., 2., 2., 2., 2., 2., 3., 2., 3., 2.,
       3., 6., 2., 6., 3., 3., 1., 2., 3., 1., 3., 3., 6., 2., 3., 1., 6.,
       3., 3., 4.], 'type': ['way', 'arrived', 'way', 'arrived', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'way', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected', 'rejected',
       'rejected', 'rejected', 'rejected'], 'overall': [  1.,   1.,   1.,   5.,   1.,   1.,   2.,  19.,   9.,   5.,   4.,
         3.,   1.,   2.,   5.,  14.,  15.,   6.,   2.,   1.,   1.,  10.,
         1.,   1.,  10.,  20.,   5.,  10.,  14.,   6.,  10.,   5.,   1.,
        10.,   3.,   5.,   8.,   4.,   8.,  18.,  30.,  20.,   1.,  13.,
        10.,  15.,  20.,   3.,  40.,   1.,  45.,  20.,  15.,  20.,  16.,
         3.,   5.,  10.,   9.,  73., 105.,  20.,  15.,  30.,  11.,  10.,
        40.,   9.,  13.,  17.,   5.,  30.,  10.,  15.,   4.,  25.,  10.,
        50.,  10.,   5.,   2.,   5.,  40.,  15.,  15.,  15.,   2.,   3.,
         7.,  10.,   1.,   1.,   5.,   4.,   6.,   8.,   2.,  15.,   4.,
         3.,  12.,   1.,  12.,  10.,  15.,   7.,   5.,   2.,   3.,   1.,
         1.,  11.,   2.,   5.,   7.,  15.,   5.,   2.,   1.,  10.,   4.,
        10.,   4.,  10.,   5.,   5.,  20.,   6.,  10.,   3.,   5.,   4.,
        13.,  12.,   3.,  40.,   5.,  15.,   3.,   3.,  10.,  10.,   3.,
         9.,  10.,  10.,  15.,   4.,  10.,   5.,  15.,   1.,   5.,   7.,
         9.,   4.]}) 

# first pivot 
df_2_res_opti = pd.pivot_table(
    df.query("type == 'rejected'").rename(columns={'overall': 'overall_1'}),
    values=["rem", "rp", "overall_1"],
    index=["road", "vrp", "naim"],
    columns=["rem", "rp"],
    aggfunc={"overall_1": np.sum},
)

# second pivot
df_2_res_in_vrp = pd.pivot_table(
    df.query("type == 'arrived'").rename(columns={'overall': 'overall_2'}),
    values=["rem", "rp", "overall_2"],
    index=["road", "vrp", "naim"],
    columns=["rem", "rp"],
    aggfunc={"overall_2": np.sum},
)

# third pivot
df_2_res_to_vrp = pd.pivot_table(
    df.query("type == 'way'").rename(columns={'overall': 'overall_3'}),
    values=["rem", "rp", "overall_3"],
    index=["road", "vrp", "naim"],
    columns=["rem", "rp"],
    aggfunc={"overall_3": np.sum},
)

# fourth pivot
df_2_res_overall = pd.pivot_table(
    df.rename(columns={'overall': 'overall_4'}),
    values=["rem", "rp", "overall_4"],
    index=["road", "vrp", "naim"],
    columns=["rem", "rp"],
    aggfunc={"overall_4": np.sum},
)

# overall pivot
df_new = pd.concat([df_2_res_in_vrp, df_2_res_to_vrp, df_2_res_opti, df_2_res_overall], axis=0)

enter image description here

But I want to add a columns inside each overal_* that will be a sum of each row for rem level i.e. for overall_1 it would be new column 2_sum that is a sum of rem 2 and 3_sum that is a sum of rem 3 and the same for each overall_*.

enter image description here

I don't know if it is clear. Also may be it is possible to write this pivot in one line of code or so than concatinating several dataframes as I did.

Solution

  • Unfortunately, I don't see an easy way to do this other than adding more pd.pivot_tables and concatenating them. Pd.pivot_tables does has an argument for margins which gives row sum, but not exactly in the same structure as your requirement. Let me know if you find a better way.

    See the below code that just does it for your first pivot, i.e. "overall_1" and you can expand it to others:

    df2 = pd.pivot_table(
    df,
    values=["overall"],
    index=["road", "vrp", "naim"],
    columns=["rem"],
    aggfunc={"overall": np.sum},
) # pivot just on "rem" columns

# rename multi-index columns names
df2.columns = df2.columns.set_levels(['rem_'+str(x) for x in df2.columns.levels[1]], level=1) # rename levl1
df2.columns =  df2.columns.set_levels(['overall_1'], level=0) # rename level 0
df2.columns = pd.MultiIndex.from_tuples([x + ("",) for x in df2.columns], names=list(df2.columns.names) + [df_2_res_opti.columns.names[-1]]) # add empty level for "rp"

# combine to original and sort
df_2_res_opti[df2.columns] = df2 # "merge" operation
idx = np.array([float(x[1][-1] + ".5") if isinstance(x[1], str) else x[1] for x in df_2_res_opti.columns]).argsort() # hacky way to sort
df_2_res_opti[df_2_res_opti.columns[idx]]
df_2_res_opti

    Output:

    enter image description here