0

I am not able to structure my ideas with this thing. I hope you could help me. I have a financial report like this:

CONSOLIDATED BALANCE SHEETS - USD ($) $ in Millions Sep. 28, 2019 Sep. 29, 2018
0                                     Current assets:            NaN           NaN
1                           Cash and cash equivalents          48844         25913
2                               Marketable securities          51713         40388
3                            Accounts receivable, net          22926         23186
4                                         Inventories           4106          3956
5                        Vendor non-trade receivables          22878         25809
6                                Other current assets          12352         12087
7                                Total current assets         162819        131339
8                                 Non-current assets:            NaN           NaN
9                               Marketable securities         105341        170799
10                 Property, plant and equipment, net          37378         41304
11                           Other non-current assets          32978         22283
12                           Total non-current assets         175697        234386
13                                       Total assets         338516        365725
14                               Current liabilities:            NaN           NaN
15                                   Accounts payable          46236         55888
16                          Other current liabilities          37720         33327
17                                   Deferred revenue           5522          5966
18                                   Commercial paper           5980         11964
19                                          Term debt          10260          8784
20                          Total current liabilities         105718        115929
21                           Non-current liabilities:            NaN           NaN
22                                          Term debt          91807         93735
23                      Other non-current liabilities          50503         48914
24                      Total non-current liabilities         142310        142649
25                                  Total liabilities         248028        258578
26                      Commitments and contingencies                             
27                              Shareholders’ equity:            NaN           NaN
28  Common stock and additional paid-in capital, $...          45174         40201
29                                  Retained earnings          45898         70400
30      Accumulated other comprehensive income/(loss)           -584         -3454
31                         Total shareholders’ equity          90488        107147
32         Total liabilities and shareholders’ equity         338516        365725

Which is a pandas Dataframe read it from excel. I want to - with some algorithm - get this output:

CONSOLIDATED BALANCE SHEETS - USD ($) $ in Millions Sep. 28, 2019 Sep. 29, 2018
0                          Cash and cash equivalents          48844         25913
1                               Total current assets         162819        131339
2                 Property, plant and equipment, net          37378         41304
3                           Total non-current assets         175697        234386
4                                       Total assets         338516        365725
5                                   Accounts payable          46236         55888
6                          Total current liabilities         105718        115929
                                          Total debt         108047        114483
7                      Total non-current liabilities         142310        142649
8                                  Total liabilities         248028        258578
9                         Total shareholders’ equity          90488        107147

Basically the thing is, with a given key values, search in the first column of the DataFrame and return every matching row. With only one dataframe is easy, because the key values are exactly the same as the values searched. But actually is not like this. I have thousands of reports in which the values searched are slightly different. e.g: key value = Cash, values in the df = Cash and Cash equivalents, key value = net sales, value in the df = net revenue What have I tried so far? I've tried fuzzywuzzy module but sometimes it doesn't work fine. Any ideas?

Improve this question
2
  • 1
    Looked at [fuzzwuzzy]{github.com/seatgeek/fuzzywuzzy), and it seems to output a ratio for each pair of matched strings. (1) Are there any criteria to decide if a string should be included or excluded from the output? (2) Is there any threshold fuzzy 'match` ratio say >=0.50 that you may have found worked better, (3) Is it possible to know how dissimilar the other reports would be as compared to the one posted in the question i.e. would the same phrases or words appear in other reports? Commented Jul 7, 2020 at 10:53
  • Thank you. This is a good starting point! Commented Jul 7, 2020 at 11:05

1 Answer 1

Reset to default
0

One way to deal with this kind of search is to add a classification name to make it easier to narrow down. If you want to know the total of current assets, you can extract 'Class 1' as current assets, 'flg' as total, and It's a good idea to use the You can also use str.contains() to perform fuzzy searches. Note: Column names have been changed in the creation of the code.

df.replace('NaN', np.NaN, inplace=True)
df.rename(columns={'CONSOLIDATED BALANCE SHEETS - USD ($) $ in Millions':'accounts','Sep. 28, 2019':'this_year','Sep. 29, 2018':'last_year'}, inplace=True)
df['NO'] = np.arange(len(df))
df['Class1'] = df['accounts'][df.isnull().any(axis=1)]
df['Class1'] = df['Class1'].fillna(method='ffill')
df['flg'] = np.where(df['accounts'].str.contains(r'^(Total)'), 'total', 'items')

df

|    | accounts                                          |   this_year |   last_year |   NO | Class1                        | flg   |
|---:|:--------------------------------------------------|------------:|------------:|-----:|:------------------------------|:------|
|  0 | Current assets:                                   |         nan |         nan |    0 | Current assets:               | items |
|  1 | Cash and cash equivalents                         |       48844 |       25913 |    1 | Current assets:               | items |
|  2 | Marketable securities                             |       51713 |       40388 |    2 | Current assets:               | items |
|  3 | Accounts receivable, net                          |       22926 |       23186 |    3 | Current assets:               | items |
|  4 | Inventories                                       |        4106 |        3956 |    4 | Current assets:               | items |
|  5 | Vendor non-trade receivables                      |       22878 |       25809 |    5 | Current assets:               | items |
|  6 | Other current assets                              |       12352 |       12087 |    6 | Current assets:               | items |
|  7 | Total current assets                              |      162819 |      131339 |    7 | Current assets:               | total |
|  8 | Non-current assets:                               |         nan |         nan |    8 | Non-current assets:           | items |
|  9 | Marketable securities                             |      105341 |      170799 |    9 | Non-current assets:           | items |
| 10 | roperty, plant and equipment, net                 |       37378 |       41304 |   10 | Non-current assets:           | items |
| 11 | Other non-current assets                          |       32978 |       22283 |   11 | Non-current assets:           | items |
| 12 | Total non-current assets                          |      175697 |      234386 |   12 | Non-current assets:           | total |
| 13 | Total assets                                      |      338516 |      365725 |   13 | Non-current assets:           | total |
| 14 | Current liabilities:                              |         nan |         nan |   14 | Current liabilities:          | items |
| 15 | Accounts payable                                  |       46236 |       55888 |   15 | Current liabilities:          | items |
| 16 | Other current liabilities                         |       37720 |       33327 |   16 | Current liabilities:          | items |
| 17 | Deferred revenue                                  |        5522 |        5966 |   17 | Current liabilities:          | items |
| 18 | Commercial paper                                  |        5980 |       11964 |   18 | Current liabilities:          | items |
| 19 | Term debt                                         |       10260 |        8784 |   19 | Current liabilities:          | items |
| 20 | Total current liabilities                         |      105718 |      115929 |   20 | Current liabilities:          | total |
| 21 | Non-current liabilities:                          |         nan |         nan |   21 | Non-current liabilities:      | items |
| 22 | Term debt                                         |       91807 |       93735 |   22 | Non-current liabilities:      | items |
| 23 | Other non-current liabilities                     |       50503 |       48914 |   23 | Non-current liabilities:      | items |
| 24 | Total non-current liabilities                     |      142310 |      142649 |   24 | Non-current liabilities:      | total |
| 25 | Total liabilities                                 |      248028 |      258578 |   25 | Non-current liabilities:      | total |
| 26 | Commitments and contingencies                     |         nan |         nan |   26 | Commitments and contingencies | items |
| 27 | Shareholders’ equity:                             |         nan |         nan |   27 | Shareholders’ equity:         | items |
| 28 | Common stock and additional paid-in capital, $... |       45174 |       40201 |   28 | Shareholders’ equity:         | items |
| 29 | Retained earnings                                 |       45898 |       70400 |   29 | Shareholders’ equity:         | items |
| 30 | Accumulated other comprehensive income/(loss)     |        -584 |       -3454 |   30 | Shareholders’ equity:         | items |
| 31 | Total shareholders’ equity                        |       90488 |      107147 |   31 | Shareholders’ equity:         | total |
| 32 | Total liabilities and shareholders’ equity        |      338516 |      365725 |   32 | Shareholders’ equity:         | total |

EX:str.contains()

df[df['accounts'].str.contains('Accounts payable')]

    accounts    this_year   last_year   NO  Class1  flg
15  Accounts payable    46236.0 55888.0 15  Current liabilities:    items
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.