List index out of range when turning CSV into dict

[ranbarr]

Im trying to make a CSV file into a dict but I keep getting IndexError: list index out of range..
The CSV looks like this:
Num Country From To Participants Sports Gold Silver Bronze Total
1 AFG 1936 2016 103 7 2 2
2 ALB 1972 2016 43 7
3 ALG 1964 2016 366 20 5 4 8 17
4 ASA 1988 2016 27 8
5 AND 1976 2016 33 8
6 ANG 1980 2016 163 11
8 ARG 1900 2016 1708 34 21 25 28 74
9 ARM 1996 2016 118 13 2 5 9 16
10 ARU 1988 2016 32 10
12 AUS 1896 2016 3579 37 146 167 191 504
13 AUT 1896 2016 1484 32 25 40 42 107
14 AZE 1996 2016 180 18 7 12 25 44
15 BAH 1952 2016 162 9 6 2 6 14
16 BRN 1984 2016 90 8 1 1 1 3

and Im trying to get this output:

Output:
{'AFG': {'Gold': 0, 'Silver': 0, 'Bronze': 2, 'Total': 2}}

(not only for one country but for all the countries)
This is my try:

def summer_olympic_stats(file):
    summer_olympic_medals = {}
    with open("summer_olympics_countries.csv", "r") as my_csv:
        data = my_csv.read().split("\n") 


        Country_index = 0
        Gold_index = 0
        Silver_index = 0
        Bronze_index = 0
        Total_index = 0

        headings = data[0].split(",")
        
        for heading in headings:
            if "Country" == heading:
                Country_index = headings.index(heading)
            elif "Gold" == heading:
                Gold_index = headings.index(heading)
            elif "Silver" == heading:
                Silver_index = headings.index(heading)
            elif "Bronze" == heading:
                Bronze_index = headings.index(heading)
            elif "Total" == heading:
                Total_index = headings.index(heading)

        for line in data[1:]:
            line = line.split(",") 
            
           
            summer_olympic_medals.update({
                line[Country_index] : {
                    "Gold": line[Gold_index],
                    "Silver": line[Silver_index],
                    "Bronze": line[Bronze_index],
                    "Total": line[Total_index]
                }
            
            })
        
        return summer_olympic_medals

And I get the error list index out of range..
I tried using slicing but that didnt helped
Appreciate any help! TY!

[ibreeden]

headings = data[0].split(",")

You are splitting the line on comma's, but there are no comma's in your csv file.

[ranbarr]

headings = data[0].split(",")

You are splitting the line on comma's, but there are no comma's in your csv file.

Gotcha... I removed it but now this is the output I get:

Output:
{'1': {'Gold': '1', 'Silver': '1', 'Bronze': '1', 'Total': '1'},

[jefsummers]

Bigger question - for example Albania. It has 2 numbers after the year, are they gold and silver? silver and bronze? gold and bronze? You have no way of knowing with your current csv

[ranbarr]

sorry for that, adding a better example:

Output:
Num	Country	From	To	Participants	Sports	Gold	Silver	Bronze	Total

12	AUS	1896	2016	3579	        37	146	167	191	504
13	AUT	1896	2016	1484	        32	25	40	42	107
14	AZE	1996	2016	180	        18	7	12	25	44
15	BAH	1952	2016	162	        9	6	2	6	14
16	BRN	1984	2016	90	        8	1	1	1	3

adding a photo as well:
https://ibb.co/dQyMbqt

[perfringo]

Obviously this ain't csv file on the image. This is spreadsheet, probably Excel. While saving in Excel as csv it inserts separator defined in language settings and also preserves empty cells (like: something,,else,,).

So some clarity is needed - what kind of file there actually is.

[ranbarr]

Obviously this ain't csv file on the image. This is spreadsheet, probably Excel. While saving in Excel as csv it inserts separator defined in language settings and also preserves empty cells (like: something,,else,,).

So some clarity is needed - what kind of file there actually is.

It's an Excel but I saved it as CSV

[perfringo]

It's an Excel but I saved it as CSV

If it is saved as CSV UTF-8 (Comma delimited) (.csv) it should have commas (or other separator) between values. Sample data you provided doesn't have commas. Empty values should be also be present between commas.

[jefsummers]

Is there a reason you aren't leaving it in .xls, using Pandas to read the Excel file, and then calculating your total by something like

df[total]=df[gold]+df[silver]+df[bronze]

[ranbarr]

Is there a reason you aren't leaving it in .xls, using Pandas to read the Excel file, and then calculating your total by something like

df[total]=df[gold]+df[silver]+df[bronze]

Yeah its for my homework and they are not allowing us to use pandas or any csv modules