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.

Jan 15 in Others 211 views

## 1 answer to this question.

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

# Generate table from nested dictionary
table = grouping(df)

```

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 |
+----+---------------+----------------+-----------+----------+```
• 63,180 points

## Is there a color code for transparent in HTML?

There is no Transparent color code, but ...READ MORE

## Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

## Is there a way in Microsoft Excel to give specific bins different bin widths when making a histogram plot?

Excel built-in histogram tool only allows equal ...READ MORE

## Is there a coalesce-like function in Excel?

To have Excel analyze the formula as ...READ MORE

## how do i change string to a list?

suppose you have a string with a ...READ MORE

## how can i randomly select items from a list?

You can also use the random library's ...READ MORE

+1 vote

## how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE