时间戳

首页 > 解决方案 > 将单元格范围转换为 Python 字典

python - 将单元格范围转换为 Python 字典

问题描述

我在 excel 中有一个 5 列的表，其中我希望最左列（“A”或 0）中的每一行作为键，而该行上的 4 个后续列作为值。

到目前为止，我有：

call_data = CallData.to_dict()
call_data.index('A').to_dict(orient='index')
print(call_data)

这使每个列标题成为键和低于 (28) 值的所有行。有什么办法可以改变这个？我已经用谷歌搜索了我的心。

这是 28 行表格的样子。A列是我想成为关键的那一个。

A     | B      |C      |D       |E 
Jan-15| 800    |6.47   |530,000 |1.6

编辑 3：

现在运行代码时出现此（新）错误：

Traceback (most recent call last):
  File "D:/Personal Files/Technical Development/PycharmProjects/Call Center Headcount Model/Call Center Headcount Model.py", line 52, in <module>
    dict_result = call_data.set_index('A').to_dict(orient='index')
  File "C:\anaconda3\envs\Enviroment\lib\site-packages\pandas\core\frame.py", line 4303, in set_index
    raise KeyError(f"None of {missing} are in the columns")
KeyError: "None of ['A'] are in the columns"

代码：

    SpreadSheetData = pd.read_excel(
    FilePath + 'Call Center Work Rules.xlsx',
    sheet_name='Data Agg.')

    call_data = SpreadSheetData
    dict_result = call_data.set_index('A').to_dict(orient='index')

这是 Fram 打印到精确的数据： Print(SpreadSheetData)

    Date    Calls  AVG Call Time  Total Call Time        Orders
0  2015-01-31  82034.0       6.471024    530843.967784  1.633581e+06
1  2015-02-28  78007.5       6.743146    526015.928294  6.534326e+05
2  2015-03-31  84425.5       6.608714    557943.983912  9.801489e+05
3  2015-04-30  71089.0       6.959075    494713.692979  8.478081e+05
4  2015-05-31  77414.0       6.732396    521181.717775  1.186931e+06
5  2015-06-30  86811.0       6.999769    607656.979210  1.356493e+06
6  2015-07-31  81995.0       7.051137    578157.977305  1.861042e+06
7  2015-08-31  62538.0       7.995746    500037.935402  1.116625e+06
8  2015-09-30  45677.5       7.885182    360175.378283  7.444169e+05
9  2015-10-31  52542.0       8.083627    424729.950838  1.000827e+06
10 2015-11-30  51352.0       7.608637    390718.717376  1.751447e+06
11 2015-12-31  58871.0       7.675463    451862.183771  2.251861e+06
12 2016-01-31  68664.0       7.745629    531845.879620  1.712810e+06
13 2016-02-29  74043.5       7.937032    587685.592567  6.851241e+05
14 2016-03-31  77155.0       8.221454    634326.306777  1.027686e+06
15 2016-04-30  60979.5       8.059682    491475.397091  8.889268e+05
16 2016-05-31  69163.0       7.885619    545393.085653  1.244498e+06
17 2016-06-30  90276.0       8.508960    768154.893931  1.422283e+06
18 2016-07-31  90854.5       8.506498    772853.621003  1.951303e+06
19 2016-08-31  72722.5       7.479822    543951.323850  1.170782e+06
20 2016-09-30  54339.0       7.215742    392096.181983  7.805211e+05
21 2016-10-31  68084.0       7.705519    524622.580078  1.049367e+06
22 2016-11-30  58564.0       7.066731    413856.014456  1.836393e+06
23 2016-12-31  63573.0       7.420257    471727.986085  2.361076e+06
24 2017-01-31  71665.5       7.527221    539442.083982  1.796311e+06
25 2017-02-28  62583.5       7.295476    456576.395666  7.185243e+05
26 2017-03-31  76385.5       7.317654    558962.634577  1.077786e+06
27 2017-04-30  69727.5       7.330732    511153.636251  9.322625e+05
28 2017-05-31  81249.5       7.247624    588865.803177  1.305168e+06

标签： pythonexcelpandasdataframe

解决方案

尝试这个：

dict_result = call_data.set_index('A').to_dict(orient='index')

例子：

call_data = pd.DataFrame([
    [2,2,3,4,5],
    [1,2,3,4,5],
    [6,2,3,4,5],
    [7,2,3,4,5]

], columns=['A', 'B', 'C', 'D', 'E'])


dict_result = call_data.set_index('A').to_dict(orient='index')

for k, v in dict_result.items():
    print(k, v)

2 {'B': 2, 'C': 3, 'D': 4, 'E': 5}
1 {'B': 2, 'C': 3, 'D': 4, 'E': 5}
6 {'B': 2, 'C': 3, 'D': 4, 'E': 5}
7 {'B': 2, 'C': 3, 'D': 4, 'E': 5}

如果您想寻找另一种格式，您可以通过以下方式查看更多选项：

print(help(call_data.to_dict))

在哪里可以看到更多细节：

  Parameters
    ----------
    orient : str {'dict', 'list', 'series', 'split', 'records', 'index'}
        Determines the type of the values of the dictionary.
    
        - 'dict' (default) : dict like {column -> {index -> value}}
        - 'list' : dict like {column -> [values]}
        - 'series' : dict like {column -> Series(values)}
        - 'split' : dict like
          {'index' -> [index], 'columns' -> [columns], 'data' -> [values]}
        - 'records' : list like
          [{column -> value}, ... , {column -> value}]
        - 'index' : dict like {index -> {column -> value}}

推荐阅读