python - 使用 Excel Pandas 中的浮点值填充字典时出现问题

Question

我正在使用 Excel 电子表格来填充字典。然后我使用这些值将另一个数据帧的值乘以引用，但当我尝试时它会给我错误。我决定将 Excel 电子表格制作成我的字典以避免错误，但我还没有成功。我这样做是因为字典最终会变得很长，并且编辑键及其值太乏味了。我正在使用 Python 2.7

import pandas as pd

#READ EXCEL FILE
df = pd.read_excel("C:/Users/Pedro/Desktop/dataframe.xls")

#Store the keys with its value in a dictionary. This will become df2
d = {"M1-4":0.60,"M1-5/R10":0.85,"C5-3":0.85,"M1-5/R7-3":0.85,"M1-4/R7A":0.85,"R7A":0.85,"M1-4/R6A":0.85,"M1-4/R6B":0.85,"R6A":0.85,"PARK":0.20,"M1-6/R10":0.85,"R6B":0.85,"R9":0.85,"M1-5/R9":0.85}

#Convert the dictionary to an Excel spreadsheet
df5 = pd.DataFrame.from_dict(d, orient='index')
df5.to_excel('bob_dict.xlsx')

#populatethe dictionary from the excel spreadsheet
df2 = pd.read_excel("C:/Users/Pedro/Desktop/bob_dict.xlsx")
#Convert dtframe back to a dictionary
dictionary = df2.to_dict(orient='dict')
#Pass the dictionary as reference 

b = df.filter(like ='Value').values
c = df.filter(like ='ZONE').replace(dictionary).astype(float).values

df['pro_cum'] = ((c * b).sum(axis =1))

运行此命令时，我收到 ValueError:无法将 R6B 字符串转换为 float 。

c = df.filter(like ='ZONE').replace(d).astype(float).values

但是如果我用原始字典替换区域值，它运行时不会出现错误。

输入:df

HP    ZONE           Value  ZONE1       Value1
3     R7A           0.7009  M1-4/R6B    0.00128
2     R6A           0.5842  M1-4/R7A    0.00009
7     M1-6/R10      0.1909  M1-4/R6A    0.73576
9     R6B           0.6919  PARK        0.03459
6     PARK          1.0400  M1-4/R6A    0.33002
9.3   M1-4/R6A      0.7878  PARK        0.59700
10.6  M1-4/R6B      0.0291  R6A         0.29621
11.9  R9            0.0084  M1-4        0.00058
13.2  M1-5/R10      0.0049  M1-4        0.65568
14.5  M1-4/R7A      0.0050  C5-3        0.00096
15.8  M1-5/R7-3     0.0189  C5-3        1.59327
17.1  M1-5/R9       0.3296  M1-4/R6B    0.43918
18.4  C5-3          0.5126  R6B         0.20835
19.7  M1-4          0.5126  PARK        0.22404

Answer 1

字典d之外的某些值存在问题(错误为R6B，但可能还有更多值)，因此不可能转换为 float 。

您可以找到此值:

#create Series from all Zone columns
vals = df.filter(like ='ZONE').replace(d).stack()
#for non numeric return NaNs, so filtering return problematic values
out = vals[pd.to_numeric(vals, errors= 'coerce').isnull()].unique()
print (out)

然后添加到字典d以避免此错误。

<小时/>

示例:

print (df)
      HP       ZONE   Value     ZONE1   Value1
0    3.0        R7A  0.7009  M1-4/R6B  0.00128
1    2.0        R6A  0.5842  M1-4/R7A  0.00009
2    7.0   M1-6/R10  0.1909  M1-4/R6A  0.73576
3    9.0        R6B  0.6919      PARK  0.03459
4    6.0       PARK  1.0400  M1-4/R6A  0.33002
5    9.3   M1-4/R6A  0.7878      PARK  0.59700
6   10.6   M1-4/R6B  0.0291       R6A  0.29621
7   11.9         R9  0.0084      M1-4  0.00058
8   13.2   M1-5/R10  0.0049      M1-4  0.65568
9   14.5   M1-4/R7A  0.0050      C5-3  0.00096
10  15.8  M1-5/R7-3  0.0189      C5-3  1.59327
11  17.1    M1-5/R9  0.3296  M1-4/R6B  0.43918
12  18.4       C5-3  0.5126       R6B  0.20835
13  19.7       M1-4  0.5126     PARK1  0.22404 <- added PARK1 for testing

d = {"M1-4":0.60,"M1-5/R10":0.85,"C5-3":0.85,"M1-5/R7-3":0.85,"M1-4/R7A":0.85,"R7A":0.85,"M1-4/R6A":0.85,"M1-4/R6B":0.85,"R6A":0.85,"PARK":0.20,"M1-6/R10":0.85,"R6B":0.85,"R9":0.85,"M1-5/R9":0.85}

vals = df.filter(like ='ZONE').replace(d).stack()
out = vals[pd.to_numeric(vals, errors= 'coerce').isnull()].unique()
print (out)
['PARK1']