I have a spreadsheet. It has Identifying numbers and then multiple codes associated to those numbers. For example;

etc...

I am trying to iterate through the informatoion and create dictionaries with ID as the Key and a list of Codes as the value. My prblem is that the values are all being split into the individual characters. i.e; {1: ['A', 'B', 'C', '1', '2', '3', '4', 'C', 'B', 'A', '1', '2', '3', '4']}

The code I have is

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()

CodePudding user response：

Alternatively, you can use pandas.read_excel method to read the excel file.

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)

Output:

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

Explanation:

• First, we read the excel file using pandas.read_excel method.
• Then we separated ids and codes with the rows of the excel sheet.
• We used a dictionary named frequency to store the occurrences of code for each unique id.

References:

• Documentation on pandas.read_excel method

CodePudding user response：

Your example seems incomplete. The read() function is never called!