python - DataFrame - 嵌套字典中的表中的表
问题描述
我使用python 3。
这是我的数据结构:
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']
},
...
}
我设法创建了一个表,该表显示从第一个嵌套字典(以 开头'Vendor'
)的键创建的列。行名称是'HexaPlex x50'
。其中一列包含带有数字的计算机,即嵌套字典:
{'someName001': '12.72.9',
'someName002': '12.73.9',
'someName003': '12.73.9'}
我希望能够在 column 下的单元格中的表内有键值对'Computers'
,实际上是一个嵌套表。
ATM它看起来像这样:
表格应该看起来像这样
我怎样才能做到这一点?
此外,我想为 BIOS 版本低于最新版本的数字或单元格着色。
我还面临这样一个问题,在一种情况下,包含计算机的字典太大以至于即使我设置了pd.set_option('display.max_colwidth', -1)
. 这看起来像这样:
解决方案
正如评论中已经强调的那样,熊猫不支持“子数据框”。为了 KISS,我建议复制这些行(或管理两个单独的表......如果确实需要)。
您提到的问题中的答案(将熊猫数据框单元格中的字典解析为新的行单元格(新列))会为每个(行本地)“计算机名称”生成新的(框架范围的)列。考虑到您的域模型,我怀疑这是您的目标。
pandas 的缩写可以通过使用另一个输出引擎来规避,例如制表(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
您还可以使用一些groupBy(..).apply(..)
+ 字符串魔术来生成一个简单地隐藏重复项的字符串表示:
# 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 | | |
+----+--------------+------------+----------------+---------------+-----------------+---------------+-------------+------------+--------------+--------------+
样式输出可以通过新的样式 API生成,该 API仍然是临时的并且正在开发中:
同样,您可以使用一些逻辑来“合并”列中连续的冗余值(快速示例,我假设更多的努力可能会产生更好的输出):
上述示例的代码
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())