如何用Python将Excel整行合并为列表元素并转为字典？

Hey there! Let's fix up your code to achieve what you're looking for. Right now, you're reading the entire file as a single string, which makes it hard to work with individual rows. Let's use the csv module properly (since your code imports it but doesn't use it yet) to parse the data into rows, then convert those into lists and dictionaries.

First: Convert Each Row to a List Element #

The csv.reader class will iterate over each row in your file and return them as lists. Here's how to modify your function to collect all rows into a single list (where each element is a row from the file):

import csv

def getMedalStats():
    fLocation = "C://TEMP//"
    print("Assumed file location is at: ", fLocation)
    fName = input("Please enter a file name with its extension (ex. XXX.csv): ")
    
    # Use csv.reader to parse rows instead of reading the whole string
    with open(fLocation + fName, 'r', newline='') as fin:
        reader = csv.reader(fin)
        # Convert the reader object to a list of rows
        rows_list = list(reader)
    
    return rows_list

def main():
    eventList = getMedalStats()
    # Print each row as a list element
    for row in eventList:
        print(row)
    # Or print the entire list if you want to see it all at once
    # print(eventList)

if __name__ == "__main__":
    main()

Key Changes: #

• Used with open(...) to handle the file properly (it closes automatically when done)
• Used csv.reader to parse each row into a list
• Converted the reader to a list of rows, so each element in rows_list is a full row from your file

Second: Convert Rows to Dictionaries #

If your CSV file has a header row (like column names), you can use csv.DictReader to map each row to a dictionary where the keys are the header names and values are the row entries. Here's how:

import csv

def getMedalStatsAsDict():
    fLocation = "C://TEMP//"
    print("Assumed file location is at: ", fLocation)
    fName = input("Please enter a file name with its extension (ex. XXX.csv): ")
    
    with open(fLocation + fName, 'r', newline='') as fin:
        # DictReader uses the first row as headers
        reader = csv.DictReader(fin)
        # Convert to list of dictionaries
        rows_dict_list = list(reader)
    
    return rows_dict_list

def main():
    eventDictList = getMedalStatsAsDict()
    # Print each dictionary row
    for row in eventDictList:
        print(row)
    # Access specific values using header keys, e.g.:
    # print(eventDictList[0]["MedalType"])

if __name__ == "__main__":
    main()

Note for Excel (.xlsx) Files: #

If you're working with actual .xlsx files (not CSV), the csv module won't work. You'll need to install and use the pandas library, which makes this even easier. Here's a quick example:

First install pandas:

pip install pandas

Then use it to read the Excel file:

import pandas as pd

def getExcelData():
    fLocation = "C://TEMP//"
    print("Assumed file location is at: ", fLocation)
    fName = input("Please enter a file name with its extension (ex. XXX.xlsx): ")
    
    # Read Excel file into a DataFrame
    df = pd.read_excel(fLocation + fName)
    
    # Convert to list of rows (each row is a list)
    rows_list = df.values.tolist()
    # Convert to list of dictionaries
    rows_dict_list = df.to_dict('records')
    
    return rows_list, rows_dict_list

def main():
    eventList, eventDictList = getExcelData()
    print("Rows as lists:")
    print(eventList)
    print("\nRows as dictionaries:")
    print(eventDictList)

if __name__ == "__main__":
    main()

This should cover both your needs—converting rows to list elements and dictionaries. Let me know if you run into any issues with specific file formats or data!

内容的提问来源于stack exchange，提问作者Ivan Hou