python - 按列名和多索引向多索引数据框添加值
问题描述
我仍然对 Pandas 中多索引的工作方式感到很困惑。我创建了一个多索引,如下所示:
import pandas as pd
import numpy as np
arrays = [np.array(['pearson', 'pearson', 'pearson', 'pearson', 'spearman', 'spearman',
'spearman', 'spearman', 'kendall', 'kendall', 'kendall', 'kendall']),
np.array(['PROFESSIONAL', 'PROFESSIONAL', 'STUDENT', 'STUDENT',
'PROFESSIONAL', 'PROFESSIONAL', 'STUDENT', 'STUDENT',
'PROFESSIONAL', 'PROFESSIONAL', 'STUDENT', 'STUDENT']),
np.array(['r', 'p', 'r', 'p', 'rho', 'p', 'rho', 'p', 'tau', 'p', 'tau', 'p'])]
tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=['correlator', 'expertise', 'coeff-p'])
然后我用它们制作了一个空的DataFrame并添加了一个列名“pair”:
results_df = pd.DataFrame(index=index)
results_df.columns.names = ['pair']
填充了一些玩具数据(results_df['attr1-attr2'] = [1,2,3,4,5,6,7,8,9,10,11,12]
),它看起来像这样:
pair attr1-attr2
correlator expertise coeff-p
pearson PROFESSIONAL r 1
p 2
STUDENT r 3
p 4
spearman PROFESSIONAL rho 5
p 6
STUDENT rho 7
p 8
kendall PROFESSIONAL tau 9
p 10
STUDENT tau 11
p 12
但是,我想从字典中添加值,而不是假人。对于每个 attr-attr 对,字典中的条目如下所示:
'attr-attr': {
'pearson': {
'STUDENT': {
'r': VALUE,
'p': VALUE
},
'PROFESSIONAL': {
'r': VALUE,
'p': VALUE
}
},
'spearman': {
'STUDENT': {
'r': VALUE,
'p': VALUE
},
'PROFESSIONAL': {
'r': VALUE,
'p': VALUE
}
}
'kendall': {
'STUDENT': {
'r': VALUE,
'p': VALUE
},
'PROFESSIONAL': {
'r': VALUE,
'p': VALUE
}
}
}
下面的实际示例数据供您使用:
correlations = {'NormNedit-NormEC_TOT': {'pearson': {'PROFESSIONAL': {'r': 0.13615071018351657, 'p': 0.0002409555504769095}}, 'spearman': {'STUDENT': {'rho': 0.10867061294616957, 'p': 0.003437711066527592}, 'PROFESSIONAL': {'tau': 0.08185775947238913, 'p': 0.003435247172206748}}, 'kendall': {'STUDENT': {'tau': 0.08185775947238913, 'p': 0.003435247172206748}}}, 'NormLiteral-NormEC_TOT': {'pearson': {'PROFESSIONAL': {'r': 0.13615071018351657, 'p': 0.0002409555504769095}, 'STUDENT': {'tau': 0.08185775947238913, 'p': 0.003435247172206748}}, 'spearman': {'STUDENT': {'rho': 0.10867061294616957, 'p': 0.003437711066527592}, 'PROFESSIONAL': {'r': 0.13615071018351657, 'p': 0.0002409555504769095}}, 'kendall': {'STUDENT': {'tau': 0.08185775947238913, 'p': 0.003435247172206748}}}, 'NormHTra-NormEC_TOT': {'pearson': {'STUDENT': {'r': 0.13615071018351657, 'p': 0.0002409555504769095}}, 'spearman': {'STUDENT': {'rho': 0.10867061294616957, 'p': 0.003437711066527592}, 'PROFESSIONAL': {'r': 0.13615071018351657, 'p': 0.0002409555504769095}}, 'kendall': {'STUDENT': {'tau': 0.08185775947238913, 'p': 0.003435247172206748}}}, 'NormScatter-NormEC_TOT': {'pearson': {'STUDENT': {'r': 0.13615071018351657, 'p': 0.0002409555504769095}}, 'spearman': {'STUDENT': {'rho': 0.10867061294616957, 'p': 0.003437711066527592}, 'PROFESSIONAL': {'tau': 0.08185775947238913, 'p': 0.003435247172206748}}, 'kendall': {'PROFESSIONAL': {'tau': 0.08185775947238913, 'p': 0.003435247172206748}}}, 'NormCrossS-NormEC_TOT': {'pearson': {'STUDENT': {'r': 0.13615071018351657, 'p': 0.0002409555504769095}, 'PROFESSIONAL': {'rho': 0.10867061294616957, 'p': 0.003437711066527592}}, 'spearman': {'STUDENT': {'rho': 0.10867061294616957, 'p': 0.003437711066527592}, 'PROFESSIONAL': {'rho': 0.10867061294616957, 'p': 0.003437711066527592}}, 'kendall': {'PROFESSIONAL': {'tau': 0.08185775947238913, 'p': 0.003435247172206748}}}, 'NormPdur-NormEC_TOT': {'pearson': {'STUDENT': {'r': 0.13615071018351657, 'p': 0.0002409555504769095}, 'PROFESSIONAL': {'rho': 0.10867061294616957, 'p': 0.003437711066527592}}, 'spearman': {'STUDENT': {'rho': 0.10867061294616957, 'p': 0.003437711066527592}}, 'kendall': {'PROFESSIONAL': {'tau': 0.08185775947238913, 'p': 0.003435247172206748}}}}
所以对于每个'attr-attr'(最上面的键)作为列名,我想将它的值添加到多索引中的相应行。但是,我似乎无法找到一种有效的方法来做到这一点。缺失值应该是np.nan
. 我尝试循环使用字典并使用query()[]
,但这没有用。
for attr, attr_d in correlations.items():
for correl, correl_d in attr_d.items():
for split, split_d in correl_d.items():
results_df.query(f"correlator == {correl} and expertise == {split} and coeff_p == 'p'")[attr] = split_d['p']
results_df.query(f"correlator == {correl} and expertise == {split} and coeff_p != 'p'")[attr] = split_d['r'] if 'r' in split_d else split_d['rho'] if 'rho' in split_d else split['tau']
> pandas.core.computation.ops.UndefinedVariableError: name 'pearson' is not defined
我知道数据相对复杂,所以如果有不清楚的地方,请告诉我。
解决方案
您可以调整Wouter Overmeire 对这个问题的回答,从嵌套字典中创建一个多索引数据框:
d = correlations
df = pd.DataFrame.from_dict({(i,j,k): d[i][j][k]
for i in d.keys()
for j in d[i].keys()
for k in d[i][j].keys()
}, orient='index').stack()
然后,如果您希望列来自嵌套字典的最高级别(attr-attr
级别),您可以取消堆叠结果:
df = df.unstack(level=0)
注意:我认为您的示例数据有错误,其中'PROFESSIONAL': {'STUDENT': ...
. 如果这不是一个错误,我只是误解了一些东西,请告诉我。
推荐阅读
- javascript - 为什么JS中的字符串替换不能替换所有字符
- r - 用于条件独立测试的 PC 算法中的“indepTest”错误
- jquery - 使用位置固定时的css问题
- dialogflow-es - 将对话流代理链接到 Google Cloud Console 项目并继承我分配给该项目的所有团队成员?
- python - IndexError:标量变量 NumPy 的索引无效
- sql - C3P0、rawConnectionOperation() 和 java.lang.IllegalArgumentException
- python - 32 位系统平台的时间戳超出范围
- docker - “kafka.zookeeper.ZooKeeperClientTimeoutException:等待连接超时”仅在列出主题期间
- javascript - 在局部视图中动态加载内容
- java - 安装带有依赖项的 RestEasy?