DataFrame - table in table from nested dictionary

Question

I use python 3.

This is my data structure:

dictionary = {
    'HexaPlex x50': {
        'Vendor': 'Dell  Inc.',
        'BIOS Version': '12.72.9',
        'Newest BIOS': '12.73.9',
        'Against M & S': 'Yes',
        'W10 Support': 'Yes',
        'Computers': {
            'someName001': '12.72.9',
            'someName002': '12.73.9',
            'someName003': '12.73.9'
        },
        'Mapped Category': ['SomeOtherCategory']
    },
    ...
}

I have managed to create a table that displays columns created from keys of the first nested dictionary (which starts with 'Vendor'). The row name is 'HexaPlex x50'. One of the columns contains computers with a number, i.e. the nested dictionary:

{'someName001': '12.72.9',
 'someName002': '12.73.9',
 'someName003': '12.73.9'}

I would like to be able to have the key values pairs inside the table in the cell under column 'Computers', in effect a nested table.

ATM it looks like this:

The table should look somewhat like this

How can I achieve this?

Further, I would like to color the numbers or the cell that has a lower BIOS version than the newest one.

I also face the problem that in one case the dictionary that contains the computers is so large that it gets abbreviated even though I have set pd.set_option('display.max_colwidth', -1). This looks like so:

You really shouldn't name dictionaries `dict`, it overrides a builtin — user3483203, Jun 25 '18 at 15:50
I think I found an answer https://stackoverflow.com/questions/39640936/parsing-a-dictionary-in-a-pandas-dataframe-cell-into-new-row-cells-new-columns except in my case I want the dictionary to appear under one table header not split into many, like in the question that was answered there. — user637338, Jul 09 '18 at 08:15
Im not sure how you want these results achieved. `pandas` doesn't work like an excel spreadsheet.. It seems like you want the cells merged, but thats not really panda-like behavior. You could try to set the indexes of all your other columns to the same values, which would lead to grouping these values together, or replicating the rows `x` times (in this case, 3 times) and having three equal rows for each Computers value. But apart from that, Im not sure what output you expect — rafaelc, Jul 10 '18 at 00:01
How do you feel about creating a new column next to `Computers` and divide that row into three new rows where the rows only differ from that new column? — Mert Karakas, Jul 16 '18 at 10:21
@MertKarakas Thank you for the suggestion, but this would not make the table nicer to look at. The table is a beast as it is, I would like to make it easier to read. — user637338, Jul 18 '18 at 09:09
I have added a feature request in github, maybe someone finds the time to contribute. https://github.com/pandas-dev/pandas/issues/21964 — user637338, Jul 18 '18 at 09:10
@MichaelHoff Thank you very much for the effort, but your suggestion would inflate the already unreadable table to user will not even bother to read it kind of table. I have created a feature request, maybe it finds interest. Thank you. — user637338, Jul 18 '18 at 09:13
@user637338 I see you argument against inflation. Alternatively, as also highlighted in the answer, you could just manage the extra-information in a separate table and join them when necessary. I do not see sub-dataframes coming in the near future, due to .. a lot of reasons actually. Anyway, maybe I am wrong about this. Good luck with your feature request! — Michael Hoff, Jul 18 '18 at 09:44
This is the unbelievable useful answer in the feature request: embedded lists are supported, though not in a first class way; JSON ExtensionArray type already supports nesting; you are actually asking about sparse displays, you can do this with a MultiIndex — user637338, Jul 18 '18 at 12:24
@user637338 feel free to answer your own question if you found a working solution :) — Michael Hoff, Jul 18 '18 at 21:39

Michael Hoff · Answer 1 · 2018-07-14T13:05:07.927

As already emphasized in the comments, pandas does not support "sub-dataframes". For the sake of KISS, I would recommend duplicating those rows (or to manage two separate tables... if really necessary).

The answers in the question you referred to (parsing a dictionary in a pandas dataframe cell into new row cells (new columns)) result in new (frame-wide) columns for each (row-local) "computer name". I doubt that this is what you aim for, considering your domain model.

The abbreviation of pandas can be circumvented by using another output engine, e.g. tabulate (Pretty Printing a pandas dataframe):

# standard pandas output
       Vendor BIOS Version Newest BIOS Against M & S W10 Support     Computer Location      ...          Category4     Category5     Category6     Category7     Category8     Category9     Category0
0  Dell  Inc.      12.72.9     12.73.9           Yes         Yes  someName001  12.72.9      ...       SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory
1  Dell  Inc.      12.72.9     12.73.9           Yes         Yes  someName002  12.73.9      ...       SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory
2  Dell  Inc.      12.73.9     12.73.9           Yes         Yes  someName003  12.73.9      ...       SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory

[3 rows x 17 columns]

# tabulate psql (with headers)
+----+------------+----------------+---------------+-----------------+---------------+-------------+------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+
|    | Vendor     | BIOS Version   | Newest BIOS   | Against M & S   | W10 Support   | Computer    | Location   | Category1    | Category2    | Category3    | Category4    | Category5    | Category6    | Category7    | Category8    | Category9    | Category0    |
|----+------------+----------------+---------------+-----------------+---------------+-------------+------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------|
|  0 | Dell  Inc. | 12.72.9        | 12.73.9       | Yes             | Yes           | someName001 | 12.72.9    | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory |
|  1 | Dell  Inc. | 12.72.9        | 12.73.9       | Yes             | Yes           | someName002 | 12.73.9    | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory |
|  2 | Dell  Inc. | 12.73.9        | 12.73.9       | Yes             | Yes           | someName003 | 12.73.9    | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory |
+----+------------+----------------+---------------+-----------------+---------------+-------------+------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+

# tabulate psql
+---+------------+---------+---------+-----+-----+-------------+---------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+
| 0 | Dell  Inc. | 12.72.9 | 12.73.9 | Yes | Yes | someName001 | 12.72.9 | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory |
| 1 | Dell  Inc. | 12.72.9 | 12.73.9 | Yes | Yes | someName002 | 12.73.9 | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory |
| 2 | Dell  Inc. | 12.73.9 | 12.73.9 | Yes | Yes | someName003 | 12.73.9 | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory | SomeCategory |
+---+------------+---------+---------+-----+-----+-------------+---------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+

# tabulate plain
    Vendor      BIOS Version    Newest BIOS    Against M & S    W10 Support    Computer     Location    Category1     Category2     Category3     Category4     Category5     Category6     Category7     Category8     Category9     Category0
 0  Dell  Inc.  12.72.9         12.73.9        Yes              Yes            someName001  12.72.9     SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory
 1  Dell  Inc.  12.72.9         12.73.9        Yes              Yes            someName002  12.73.9     SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory
 2  Dell  Inc.  12.73.9         12.73.9        Yes              Yes            someName003  12.73.9     SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory  SomeCategory

You could also use some groupBy(..).apply(..) + string magic to produce a string representation which simply hides the duplicates:

# tabulate + merge manually
+----+--------------+------------+----------------+---------------+-----------------+---------------+-------------+------------+--------------+--------------+
|    | Type         | Vendor     | BIOS Version   | Newest BIOS   | Against M & S   | W10 Support   | Computer    | Location   | Category1    | Category2    |
|----+--------------+------------+----------------+---------------+-----------------+---------------+-------------+------------+--------------+--------------|
|  0 | HexaPlex x50 | Dell  Inc. | 12.72.9        | 12.73.9       | Yes             | Yes           | someName001 | 12.72.9    | SomeCategory | SomeCategory |
|    |              |            | 12.72.9        |               |                 |               | someName002 | 12.73.9    |              |              |
|    |              |            | 12.73.9        |               |                 |               | someName003 | 12.73.9    |              |              |
+----+--------------+------------+----------------+---------------+-----------------+---------------+-------------+------------+--------------+--------------+

Styled output can be generated via the new Styling API which is still provisional and under development:

Again, you can use some logic to 'merge' consecutively redundant values in a column (quick example, I assume some more effort could result in much nicer output):

Code for the above examples

import pandas as pd
from tabulate import tabulate
import functools

def pprint(df, headers=True, fmt='psql'):
    # https://stackoverflow.com/questions/18528533/pretty-printing-a-pandas-dataframe
    print(tabulate(df, headers='keys' if headers else '', tablefmt=fmt))

df = pd.DataFrame({
        'Type': ['HexaPlex x50'] * 3,
        'Vendor': ['Dell  Inc.'] * 3,
        'BIOS Version': ['12.72.9', '12.72.9', '12.73.9'],
        'Newest BIOS': ['12.73.9'] * 3,
        'Against M & S': ['Yes'] * 3,
        'W10 Support': ['Yes'] * 3,
        'Computer': ['someName001', 'someName002', 'someName003'],
        'Location': ['12.72.9', '12.73.9', '12.73.9'],
        'Category1': ['SomeCategory'] * 3,
        'Category2': ['SomeCategory'] * 3,
        'Category3': ['SomeCategory'] * 3,
        'Category4': ['SomeCategory'] * 3,
        'Category5': ['SomeCategory'] * 3,
        'Category6': ['SomeCategory'] * 3,
        'Category7': ['SomeCategory'] * 3,
        'Category8': ['SomeCategory'] * 3,
        'Category9': ['SomeCategory'] * 3,
        'Category0': ['SomeCategory'] * 3,
    })

print("# standard pandas print")
print(df)

print("\n# tabulate tablefmt=psql (with headers)")
pprint(df)
print("\n# tabulate tablefmt=psql")
pprint(df, headers=False)
print("\n# tabulate tablefmt=plain")
pprint(df, fmt='plain')

def merge_cells_for_print(rows, ls='\n'):
    result = pd.DataFrame()
    for col in rows.columns:
        vals = rows[col].values
        if all([val == vals[0] for val in vals]):
            result[col] = [vals[0]]
        else:
            result[col] = [ls.join(vals)]
    return result

print("\n# tabulate + merge manually")
pprint(df.groupby('Type').apply(merge_cells_for_print).reset_index(drop=True))

# https://pandas.pydata.org/pandas-docs/stable/style.html
# https://pandas.pydata.org/pandas-docs/version/0.22.0/generated/pandas.io.formats.style.Styler.apply.html#pandas.io.formats.style.Styler.apply

def highlight_lower(ref, col):
    return [f'color: {"red" if hgl else ""}' for hgl in col < ref]

def merge_duplicates(col):
    vals = col.values
    return [''] + ['color: transparent' if curr == pred else ''  for pred, curr in zip(vals[1:], vals)]

with open('only_red.html', 'w+') as f:
    style = df.style
    style = style.apply(functools.partial(highlight_lower, df['Newest BIOS']),
                        subset=['BIOS Version'])
    f.write(style.render())

with open('red_and_merged.html', 'w+') as f:
    style = df.style
    style = style.apply(functools.partial(highlight_lower, df['Newest BIOS']),
                        subset=['BIOS Version'])
    style = style.apply(merge_duplicates)
    f.write(style.render())

DataFrame - table in table from nested dictionary

1 Answers1

Linked