python - 如何将 csv 转换为 python pandas 中的嵌套 json？

Question

我有一个这样的 csv:

    Art        Category  LEVEL 2    LEVEL 3 LEVEL 4 LEVEL 5 Location
0   PRINTMAKING VISUAL  CONTEMPORARY    2D  NaN NaN NaN
1   PAINTING    VISUAL  CONTEMPORARY    2D  NaN NaN NaN
2   AERIAL  VISUAL  CONTEMPORARY    2D  PHOTOGRAPHY AERIAL  NaN
3   WILDLIFE    VISUAL  CONTEMPORARY    2D  PHOTOGRAPHY WILDLIFE    NaN
4   NATURE  VISUAL  CONTEMPORARY    2D  PHOTOGRAPHY NATURE  NaN

艺术和类别将在那里，但从 l1 到 l6 的级别可以为空。我想要实现的是这样的:

art: PRINTMAKING
category: VISUAL
tags: [CONTEMPORARY, 2D]

关卡基本上是存储在数组中的特定艺术的标签。

我是 python 的新手，到目前为止我已经编写了以下代码。我怎样才能做到这一点。

import pandas as pd
import json
data = pd.read_excel("C:\\Users\\Desktop\\visual.xlsx")
rec = {}
rec['art'] = data['Art']
rec['category'] = data['Category']
rec['tags'] = data['LEVEL 2'] + ',' + data['LEVEL 3'] + ',' + data['LEVEL 4'] + ',' + data['LEVEL 5']

我想这不是正确的做法。

Answer 1

要将 tags 的值转换为没有 NaN 的列表，请使用:

df['tags'] = df.filter(like='LEVEL').apply(lambda x: x.dropna().tolist(), axis=1)
#alternative, should be faster
#df['tags'] = [[y for y in x if isinstance(y, str)] for x in
#                 df.filter(like='LEVEL').values]

d = df[['Art','Category','tags']].to_dict(orient='records')

[{
    'Art': 'PRINTMAKING',
    'Category': 'VISUAL',
    'tags': ['CONTEMPORARY', '2D']
}, {
    'Art': 'PAINTING',
    'Category': 'VISUAL',
    'tags': ['CONTEMPORARY', '2D']
}, {
    'Art': 'AERIAL',
    'Category': 'VISUAL',
    'tags': ['CONTEMPORARY', '2D', 'PHOTOGRAPHY', 'AERIAL']
}, {
    'Art': 'WILDLIFE',
    'Category': 'VISUAL',
    'tags': ['CONTEMPORARY', '2D', 'PHOTOGRAPHY', 'WILDLIFE']
}, {
    'Art': 'NATURE',
    'Category': 'VISUAL',
    'tags': ['CONTEMPORARY', '2D', 'PHOTOGRAPHY', 'NATURE']
}]