Best way to make a dictionary out of one column?

Question

I have an excel file that contains only 1 column, but all of the headers and values are in that same column.

As a practice Python project, I'm trying to use Openpyxl and Pandas to lay out the data how I want it.

i.e. It's a list of sports stats, that would be best displayed with the team names on the y-axis, and the stat categories in the x-axis.

However, the way the data is laid out in the column is:

Stat Category Name (Goals as example)
Team 1 Goals value
Team 1 Name
Team x Goals value
Team x Name
Stat Category 2 Name (Shots as example)
Team 1 Stats value
Team 1 Name

First, I created another column in excel that stores the character values of the column with the data in it.

Next, I created the variables 'statvalues' and 'charlength' that store the values of each column:

statvalues = sheet["A1:A266"]
charlength = sheet["B1:B266"]

Next, I created two empty lists, 'statvalueslist' and 'charlengthvaluelist', then appended the values in 'statvalues' and 'charlength' to their respective lists:

for row in statvalues:
    for cell in row:
        statcellvalue = cell.value
        statvalueslist.append(statcellvalue)

for row in charlength:
    for cell in row:
        charlengthvalue = cell.value
        charlengthvaluelist.append(charlengthvalue)

Then, I created a dictionary with the 'statcellvalue' as the key and 'charlengthvalue' as the value.

LiveStats_dict = dict(zip(statvalueslist, charlengthvaluelist))

for x in LiveStats_dict:
    print(x, LiveStats_dict[x])

This works, but gets rid of duplicates, and I'm really not sure where to go next.

What would be the best way to identify a key in a dictionary, then take the above key and change it to a value associated with that key? Or is there an easier way to get where I want?

Thanks!

Best way to make a dictionary out of one column?

0 Answers