How do I convert GoogleSheets columns into a python key-value dictionary-CodePudding

I have this code

dictionariob = {"paz": 5, "felicidad": 2, "mejor": 3, "alivio": 4, "-": -3}
lista = ["felicidad", "paz", "día", "estoy", "isla", "alivio", "-"]

sum([dictionariob[i] for i in lista if i in dictionariob])

From which example, I get as result 8

I would like to have the "dictionariob" in a google sheets, in order to be able to easily add or remove more keys(nombre)/values(valor). But, I dont get the result expected once I switch it to a similar code as the previous shared it.

So, here is my GSheet: https://docs.google.com/spreadsheets/d/1-odw996EIUB9mo2Ad1fNh0y9QiXv7GU81COMj6g1Z-A/edit#gid=0

This is how I call it:

sheet_id = "1-odw996EIUB9mo2Ad1fNh0y9QiXv7GU81COMj6g1Z-A"
sheet_name = "a"
dicc = f"https://docs.google.com/spreadsheets/d/1-odw996EIUB9mo2Ad1fNh0y9QiXv7GU81COMj6g1Z-A/gviz/tq?tqx=out:csv&sheet=a"
dicc

import pandas as pd

diccionarioa=pd.read_csv(dicc,error_bad_lines=False, header=0)
diccionarioa.head(5)

This is how I try to convert it to dictionary:

diction = diccionarioa.to_dict()

and this is the same code that worked before, but replacing it with "diccionarioa" which refers to my google sheets, I get as result "0"..

lista = ["felicidad", "paz", "día", "estoy", "isla", "alivio", "-"]
sum([diction[i] for i in lista if i in diction])

What am I missing?

Thanks!!

CodePudding user response：

You can convert the dataframe to dictionary before doing sum:

import pandas as pd

sheet_id = "1-odw996EIUB9mo2Ad1fNh0y9QiXv7GU81COMj6g1Z-A"
sheet_name = "a"
dicc = f"https://docs.google.com/spreadsheets/d/1-odw996EIUB9mo2Ad1fNh0y9QiXv7GU81COMj6g1Z-A/gviz/tq?tqx=out:csv&sheet=a"

# get the dictionary from the dataframe here:
diccionarioa = pd.read_csv(dicc, header=0).set_index("nombre")["valor"].to_dict()

lista = ["felicidad", "paz", "día", "estoy", "isla", "alivio", "-"]
print(sum([diccionarioa[i] for i in lista if i in diccionarioa]))

Prints:

CodePudding user response：

When working with a dataframe you are able to sum up values by filtered columns straightforwardly (without converting to dict):

lista = ["felicidad", "paz", "día", "estoy", "isla", "alivio", "-"]
print(diccionarioa[diccionarioa['nombre'].isin(lista)]['valor'].sum())