Read a csv file and add your data into a dictionary

Question

I have a code where I will have a function that will pass the data from a csv file to a dictionary. The process will be to take the films with their respective notes and add to a dictionary relative to each user, and to add these dictionaries to a main dictionary.

import csv

path = 'C:\\Users\\Rafael\\Desktop\\DataMining\\RECOMMENDATION\\Movie_Ratings.csv'
data = {}

def loadCSV():
    file = open(path, newline='')
    reader = csv.reader(file)
    header = next(reader)
    header.remove('')
    n = 0  
    for u in header:
        n += 1
        data[u] = {}
        for line in reader:
            data[u][line[0]] = line[n]
loadCSV()
print(data)

However, when running my code...

{'Patrick C': {'Alien': '', 'Avatar': '4', 'Blade Runner': '5', 'Braveheart': '4', 'Dodgeball': '5', 'Forest Gump': '4', 'Gladiator': '', 'Jaws': '', 'Kazaam': '2', 'Lord of the Rings': '4', 'Napolean Dynamite': '3', 'Old School': '3', 'Pootie Tang': '', 'Pulp Fiction': '', 'Scarface': '', 'Shawshank Redemption': '5', 'Snakes on a Plane': '4', 'Spiderman': '4', 'Star Wars': '5', 'The Dark Knight': '4', 'The Happening': '1', 'The Matrix': '3', 'Toy Story': '4', 'Village': '', 'You Got Mail': '1'}, 'Heather': {}, 'Bryan': {}, 'Patrick T': {}, 'Thomas': {}, 'aaron': {}, 'vanessa': {}, 'greg': {}, 'brian': {}, 'ben': {}, 'Katherine': {}, 'Jonathan': {}, 'Zwe': {}, 'Erin': {}, 'Chris': {}, 'Zak': {}, 'Matt': {}, 'Josh': {}, 'Amy': {}, 'Valerie': {}, 'Gary': {}, 'Stephen': {}, 'Jessica': {}, 'Jeff': {}}

...Only the first user receives the movie notes. Please HELP!!

Answer 1

Your code didn’t work because you are trying to repeat reading the file:

for u in header:
    # ...
    for line in reader:

The first time will work, but the second time, reader has already been consumed. For your code to work as is, you would have to "rewind" the file, or store the content in a list.

Answer 2

As the structure of the CSV has not been informed, I will assume that it is structured like this:

Reading a file is always incremental, when csv.Reader() is called, the return is an iterator and not a list. In this case, the iterator works using an internal counter to access the lines of the file, if the file has 100 lines, when the is finished running the counter will be at 100, but if you try to run another is, the counter will start at 100 and not 0, why only the first user has reviews.

So to read the file data, the code needs to be changed this way:

import csv

path = 'C:\\Users\\Rafael\\Desktop\\DataMining\\RECOMMENDATION\\Movie_Ratings.csv'
data = []

def loadCSV():
    file = open(path, newline='')
    reader = csv.reader(file)
    header = next(reader)
    header.remove('')

    for line in reader:
        item = {
            "filme": "",
            "avaliacoes": []
        }

        for index, valor in enumerate(line):
            if index == 0:
                # Primeiro item da lista = nome do filme
                item["filme"] = valor
            else:
                # Demais itens = avaliacao de usuario
                """
                Como o nome do usuario ta no header, na mesma sequencia
                em que os itens são acessados, basta passar o index - 1,
                para saber de quem foi a nota
                """
                item["avaliacoes"].append({
                    "usuario": header[index - 1],
                    "nota": valor
                })

        data.append(item)

loadCSV()
print(data)

The result of the above code appears like this:

[
  {
    "filme": "Alien",
    "avaliacoes": [
      {
        "usuario": "Patrick C",
        "nota": 1
      },
      {
        "usuario": "Heather",
        "nota": 2
      },
      {
        "usuario": "Bryan",
        "nota": 3
      },
    ]
  },
  {
    "filme": "Avatar",
    "avaliacoes": [
      {
        "usuario": "Patrick C",
        "nota": 3
      },
      {
        "usuario": "Heather",
        "nota": 2
      },
      {
        "usuario": "Bryan",
        "nota": 1
      },
    ]
  },
  ...
]