Is there a hierarchy inferring algorithm available in python

Question

We receive a chart of accounts with more than a thousand rows, and we spend hours categorizing this data by adding its values to hierarchies (trial and error). We are unable to achieve this using account names because they can be written in a variety of languages and formats. Exists a ready-made algorithm in Python that can infer hierarchy based on the values given?

The sample input and output are shown below; I've coloured them to help you understand.

I am aware that no algorithm can ever be 100% accurate, but it would still be a success if we could classify 70% to 80% of the accounts. If anyone has a suggestion for how to do this, I can also code in VBA.

I've tried running cumulative sums and differences, but I can't seem to find steps ahead.

Answer 1

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 |
+----+---------------+----------------+-----------+----------+