Python將串列附加到字典中。串列被分成單獨的字符而不是整個

我有一個電子表格。它具有識別號碼，然后是與這些號碼相關聯的多個代碼。例如;

等等...

我正在嘗試遍歷資訊并創建以 ID 作為鍵并以代碼串列作為值的字典。我的問題是這些值都被分成了單獨的字符。IE; {1：['A'，'B'，'C'，'1'，'2'，'3'，'4'，'C'，'B'，'A'，'1'，'2 ', '3', '4']}

我的代碼是

from openpyxl import *

FilePath = "File.xlsx"

wb = load_workbook(filename=FilePath)


Sheet = wb.active
Sheet1 = wb['Sheet1']
Sheet2 = wb['Sheet2']
Dict1 = {}
UnitPlace = Sheet1.iter_cols(min_row=2, max_row=2, min_col=18, max_col=110)
Dict = {}
def add_values_in_dict(sample_dict, key, list_of_values):
    if key not in sample_dict:
        sample_dict[key] = list()
    sample_dict[key].extend(list_of_values)
    return sample_dict

def read():
    for SID2 in Sheet2['A']:
        UnitInSheet2 = Sheet2['D' (str(SID2.row))].value
        Dict1 = add_values_in_dict(Dict, SID2.value, UnitInSheet2)
        print (Dict1)
        for SID1 in Sheet1['D']:
            Rows = SID1.row
read()

uj5u.com熱心網友回復：

更新：使用 Openpyxl 讀取 Excel 檔案

我們可以使用Openpyxl迭代行。然后我們將 ID 代碼存盤為字典，其中ID鍵Code是值串列。

from openpyxl import load_workbook


def read_file_openpyxl(file_path):
    wb = load_workbook(filename=file_path)
    sheet_1 = wb['Sheet1']
    frequency = {}
    for row in sheet_1.values:
        if type(row[0]) == int:
            id = row[0]
            code = row[1]
            if id in frequency:
                frequency[id].append(code)
            else:
                frequency[id] = [code]
    return frequency


if __name__ == "__main__":
    FilePath = "dummy.xlsx"
    print(read_file_openpyxl(FilePath))

輸出：

{1: ['ABC1234', 'CBA1234'], 2: ['ABS1234', 'DEF3456']}

使用 Pandas 的替代解決方案：

或者，您可以使用pandas.read_excel方法來讀取 excel 檔案。

import pandas as pd


def read_data(file_path):
    dataset = pd.read_excel(file_path)
    ids = list(dataset["ID"])
    codes = list(dataset["Code"])
    frequency = {}
    for i in range(len(ids)):
        id = ids[i]
        code = codes[i]
        if id in frequency:
            frequency[id].append(code)
        else:
            frequency[id] = [code]
    return frequency


if __name__ == "__main__":
    FilePath = "dummy.xlsx"
    freq = read_data(FilePath)
    print(freq)

輸出：

{1: ['ABC1234', 'CBA1234'], 2: ['ABS1234', 'DEF3456']}

解釋：

• 首先，我們使用pandas.read_excel方法讀取 excel 檔案。
• 然后我們將excel 表的行與行ids分開。codes
• 我們使用一個名為的字典frequency來存盤code每個唯一的出現id。

參考：

• Openpyxl 上的檔案
• pandas.read_excel 方法的檔案

uj5u.com熱心網友回復：

我更喜歡串列理解，以下是我的解決方案

# Import openpyxl
# Note: openpyxl package provides both read and write capabilities to excel
import openpyxl
from openpyxl.utils import get_column_letter



# Class definitions should use CamelCase convention based on pep-8 guidelines
class CustomOpenpyxl:

    # Initialize the class with filename as only argument
    def __init__(self, _my_file_name):
        assert _my_file_name.split('.')[-1] == 'xlsx', 'Input file is not xlsx'
        self.my_filename = _my_file_name
        self.my_base_wb = openpyxl.load_workbook(self.my_filename, read_only=False)
        # following line will get the names of worksheets in the workbook
        self.ws_names_in_my_base_wb = self.my_base_wb.sheetnames
        # following line will get the number of worksheets in the workbook
        self.num_ws_in_my_base_wb = len(self.my_base_wb.sheetnames)
        # following line will set the last worksheet in the workbook as active
        self.my_base_active_ws = self.my_base_wb.active
        
    # Method to set a specific worksheet as active where column names are in row#1
    # Argument to this method is: - worksheet name
    def active_ws(self, _ws_name):
        # if the worksheet name exists in the workbook
        if self.if_ws_in_wb(_ws_name):
            self.my_base_active_ws = self.my_base_wb[_ws_name]
            self.my_base_active_ws_max_row = self.my_base_active_ws.max_row
            self.my_base_active_ws_max_col = self.my_base_active_ws.max_column
            self.my_base_active_ws_col_titles = [_col_name[0].value for _col_name in self.my_base_active_ws.iter_cols()]
        else:
            print('Worksheet {} not found in workbook'.format(_ws_name))
    
    # Create a dictionary from excel cell values
    # No arguments to this method
    def create_dict_from_values(self):
        # Create an empty dictionary
        _my_dict = dict()
        # get the unique keys (numeric) from the first column of the worksheet
        _my_keys = set([_row[0] for _row in self.my_base_active_ws.values if str(_row[0]).isdigit()])
        # Iterate over the keys and add the values as a list
        for _ in _my_keys:
            # using list comprehension add the values to the key or keys
            _my_dict[_] = [_row[1] for _row in self.my_base_active_ws.values if _row[0] == _]
        return _my_dict
        
########################################################################################################################################################################

from Automation._Ashish_Samarth_Custom_Classes._under_progress_custom_xl_openpyxl_pkg import CustomOpenpyxl

# Create a new sub class and inherit methods from custom_pandas and CustomOpenpyxl
class SO( CustomOpenpyxl):
    # Initialize this class with the filename in question
    def __init__(self, _my_file_path, _my_file_name):
        # This is an important and needed step
        # Initialize the inherited class with the arguments needed for that class
        CustomOpenpyxl.__init__(self, _my_file_name)

_my_file_path = os.getcwd()
_my_file_name = 'Book1.xlsx'

# Instantiate an object using the newly created class in this code block
# So that this object gets access to all methods in the class
_my_src_obj = SO(_my_file_path, _my_file_name)
print(_my_src_obj.create_dict_from_values())
####################################################################PRINT Result################################################################################

{1: ['ABC1234', 'CBA1234'], 2: ['ABS1234', 'DEF3456']}

Process finished with exit code 0

標籤：Python 字典 打开pyxl

上一篇：在Python中使用for回圈迭代字典時如何從第二個鍵開始

下一篇：如何從嵌套字典中查找公共鍵值對