I’m building a list of lists using values which have been inputted on a google spreadsheet with gspread. This final list of lists will then populate the sheet using worksheet.update()
The list of lists is as follows:
[['Date', 'Description', 'Income', 'Expense', 'Currency', 'Exchange Rate', 'Bank account', 'Converted amount', 'Converted currency', 'Total expenses £', 'Total expenses €'],
['2024-06-06', 'Gardener', '', '500', '£', '1.191', 'bank name', 595.5, '€'],
['2024-07-15', 'Keys cut', '', '16', '£', '1.1902', 'bank name', 19.0432, '€'],
['2024-07-15', 'Skip hire', '', '300', '£', '1.1902', 'bank name', 357.06, '€']]
I want to sum all the strings that would be under the column “Expense”, so ‘500’, ’16’ and ‘300’. I also want to sum the floats under the column “Converted amount”, so 595.5, 19.0432 and 357.06. I then want to append these sums to the second row of the list of lists.
What I’ve tried:
for row in list_of_lists[1:]:
total_gbp = []
expense_gbp = float(row[3])
total_gbp.append(expense_gbp)
list_of_lists[1].append(sum(total_gbp))
total_eur = []
expense_eur = row[7]
total_eur.append(expense_eur)
list_of_lists[1].append(sum(total_eur))
If I print(list_of_lists), I get:
[['Date', 'Description', 'Income', 'Expense', 'Currency', 'Exchange Rate', 'Bank account', 'Converted amount', 'Converted currency', 'Total expenses £', 'Total expenses €'],
['2024-06-06', 'Gardener', '', '500', '£', '1.191', 'bank name', 595.5, '€', 500.0, 595.5, 16.0, 19.0432, 300.0, 357.06],
['2024-07-15', 'Keys cut', '', '16', '£', '1.1902', 'bank name', 19.0432, '€'],
['2024-07-15', 'Skip hire', '', '300', '£', '1.1902', 'bank name', 357.06, '€']]
So it’s appending the elements instead of the sum
Inserting print(total_gbp) in the for loop outputs:
[500]
[16]
[300]
And I don’t understand why it’s not a list as follows:
[500, 16, 300]
1
Is there any reason you would not consider using pandas instead of trying to reason about lists of lists and mixed types?
If you convert your spreadsheed to a pandas dataframe, you can then easily reason about columns and column types, sum them or any other manipulation. It will be easier and also much more efficient if you have big columns.
In the gspread docs, you can find example for how to convert you spreadsheet into a pandas dataframe:
import pandas as pd
dataframe = pd.DataFrame(worksheet.get_all_records())
Then you can do something like dataframe['Expense'].sum(), much easier than manipulating lists of lists.
0
Do you mean like this:
total_gbp = 0
total_eur = 0
for row in list_of_lists[1:]:
total_gbp += float(row[3])
total_eur += float(row[7])
list_of_lists[1].append(total_gbp)
list_of_lists[1].append(total_eur)
Output:
['Date', 'Description', 'Income', 'Expense', 'Currency', 'Exchange Rate', 'Bank account', 'Converted amount', 'Converted currency', 'Total expenses £', 'Total expenses €']
['2024-06-06', 'Gardener', '', '500', '£', '1.191', 'bank name', 595.5, '€', 816.0, 971.6032]
['2024-07-15', 'Keys cut', '', '16', '£', '1.1902', 'bank name', 19.0432, '€']
['2024-07-15', 'Skip hire', '', '300', '£', '1.1902', 'bank name', 357.06, '€']
The main issue with your original code is that you restart the total calculations for each row. This means you are only adding the values from one row at a time instead of adding up all the values from all rows.
you redeclare total_gbp and total_eur per loop. Instead, you should change the scope of total_gbp and total_eur.
total_gbp = []
total_eur = []
for row in list_of_lists[1:]:
expense_gbp = float(row[3])
total_gbp.append(expense_gbp)
expense_eur = row[7]
total_eur.append(expense_eur)
list_of_lists[1].append(sum(total_gbp))
list_of_lists[1].append(sum(total_eur))