Convert the list of values to the desired table assuming:

- Each subgroup begins with the word total
- The Last row has the name of the Level 1 group (i.e. Total Revenue)

**Code**

Process input data as a data frame

import math
import pandas as pd
from tabulate import tabulate
def grouping(df):
''' Convert tablular values into nested dictionary
Assumptions
Subgroup begin with word Total
Overall Group is the last row
'''
# Empty Dataframe
result = []
# Empty data frame for subgroup
subgroup_df = pd.DataFrame({'Level 1': pd.Series(dtype='str'),
'Level 2': pd.Series(dtype='str'),
'Level 3': pd.Series(dtype='str'),
'Values': pd.Series(dtype='float')})
subgroup_total = 0
for index, (account, value) in enumerate(zip(df['Account'], df['Values']), start = 1):
if index == len(df):
continue
if math.isclose(subgroup_total, value, abs_tol = 0.02):
# current value equals sum of previous rows in subgroup
subgroup_df['Level 2'] = account
result.append(subgroup_df)
# New empty dataframe for subgroup
subgroup_df = pd.DataFrame({'Level 1': pd.Series(dtype='str'),
'Level 2': pd.Series(dtype='str'),
'Level 3': pd.Series(dtype='str'),
'Values': pd.Series(dtype='float')})
subgroup_total = 0
else:
# within group
subgroup_df.loc[len(subgroup_df.index)] = ["", "", account, value]
subgroup_total += value
result_df = pd.concat(result, ignore_index = True)
result_df['Level 1'] = account
return result_df

**Example Usage**

# Usage
# Get data from excel (workbook test.xlsx with worksheet name test
df = pd.read_excel('test.xlsx', 'test')
# Generate table from nested dictionary
table = grouping(df)
print(tabulate(table, headers='keys', tablefmt='psql'))

**Output**

+----+---------------+----------------+-----------+----------+
| | Level 1 | Level 2 | Level 3 | Values |
|----+---------------+----------------+-----------+----------|
| 0 | Total Revenue | Total Service | Service A | 10.79 |
| 1 | Total Revenue | Total Products | Product A | 4.93 |
| 2 | Total Revenue | Total Products | Product B | 4.81 |
| 3 | Total Revenue | Total Products | Product C | 4.18 |
| 4 | Total Revenue | Total Products | Product D | 4.52 |
| 5 | Total Revenue | Total Food | Food A | 5.09 |
| 6 | Total Revenue | Total Food | Food B | 5.6 |
| 7 | Total Revenue | Total Food | Food C | 5.12 |
| 8 | Total Revenue | Total Food | Food D | 5.24 |
| 9 | Total Revenue | Total Food | Food E | 5.19 |
| 10 | Total Revenue | Total Food | Food F | 5.75 |
+----+---------------+----------------+-----------+----------+