>我有一个这样的数据帧:
| col1 | d
-------------------------------------------
0 | A | {'student99': [[0.83, "nice"]]}
1 | B | {'student99': [[0.84, "great"], [0.89, "cool"]]}
2 | C | {'student98': [[0.85, "amazing"]], 'student97': [[0.9, "crazy"]]}
我正在尝试转换为数据帧,例如:
| col1 | student | grade | comment
---------------------------------------------
0 | A | student99| 0.83 | nice
1 | B | student99| 0.84 | great
2 | B | student99| 0.89 | cool
3 | C | student98| 0.85 | amazing
4 | C | student97| 0.9 | crazy
如您所见,我需要d
列拆分为student
、grade
和comment
列,我需要根据d
列中的键数(如上面的 C 行(和每个键的列表数量(如上面的 B 行(将行拆分为一些行。
我该怎么做?
在评论之后,我注意到数据以下一个格式作为 JSON 到达(我将其转换为数据帧(:
{"A": {"d" : {'student99': [[0.83, "nice"]]}},
"B": {"d" : {'student99': [[0.84, "great"], [0.89, "cool"]]},
"C": {"d" : {'student98': [[0.85, "amazing"]], 'student97': [[0.9, "crazy"]]}
}
我们可以对pd.Series
执行explode
,然后重新创建数据帧join
它回来
s=df.pop('d').apply(pd.Series).stack().explode()
df_add=pd.DataFrame({'student':s.index.get_level_values(1),
'grade':s.str[0].values,
'comment':s.str[1].values},
index=s.index.get_level_values(0))
df=df.join(df_add,how='right')
df
Out[169]:
col1 student grade comment
0 A student99 0.83 nice
1 B student99 0.84 great
1 B student99 0.89 cool
2 C student98 0.85 amazing
2 C student97 0.90 crazy
@YOBEN_S的解决方案很棒;这是对更快解决方案的尝试:
from itertools import product, chain
#chain.... is long... flatten is shorter
#and still gets the point accross
flatten = chain.from_iterable
#flatten the product of each key,value pair
#in the dictionary
m = [(first, flatten(product([key], value) for key, value in last.items()))
for first, last in emp]
#flatten again
phase2 = flatten(product(first, last) for first, last in m)
#at this point we have
#the column entry("A","B",...)
#and the flattened entries in the dict
#so, unpacking solves this
phase3 = [(first,second, *last) for first, (second,last) in phase2]
result = pd.DataFrame(phase3, columns = ["col1","student","grade","comment"])
result
col1 student grade comment
0 A student99 0.83 nice
1 B student99 0.84 great
2 B student99 0.89 cool
3 C student98 0.85 amazing
4 C student97 0.90 crazy