Calculations on Data in Tables

Anvil Q&A
1

Hi

I am struggeling with calulations of data from the table and passing them to another table, should be an easy task.

Code Sample:
https://anvil.works/build#clone:O2GZYJL73WT6IZHN=F3Q5WEMZ7BGPVHWEPXWXSGF5

What I’m trying to do:

Calculation (e.g. sum of column a + b) of data and putting them into a new row, as well generating a copy o a column to a new table column (row by row)

Calulation: Starts at Time 1 and stops at time 10 in a step of 1. So, row by row calulation.

Or I am getting a dict an integer or Object is Not Iterable error…

It would be helpfull to get more ANVIL Examples of Tables with Math and dynamic calulations.

I appreciate you help.
Thanks
Gerhard

2

Your clone link won’t run (gets a TIME not defined error in Form1). Be sure it’s in a state to demonstrate the issue you’re trying to get help with, otherwise we’re flying blind.

In general, just looking at the code, it looks like your call to add_row:

sum = app_tables.data.add_row(TIME=TIME, DBT=DBT, total= TIME + DBT)

tries to set a column named total. But that table doesn’t have a column named total.

Then your row_copy function doesn’t make much sense at all. The first line:

get_time = [r['TIME'] for r in app_tables.data.search()][0:10]

is going to give you a list of numbers. But in the next line you use **get_time which is something you do with a dict, not a list. It isn’t clear at all what you’re trying to accomplish with this function.

3

Hi Jay,

sorry for the column name and not making it clear.
The goal is:

Function 1:
I would like to sum 2 column (TIME + DBT) in the table_data and write the sum row by row to the column (sum) in the same table_data.

TIME DBT = sum
1 1.7 2.7
2 0.2 2.2

Function 2:
I would like to copy row by row of the column TIME of the table_data to the table_copy in the column TIME.

Thank you,
Gerhard

4

Which of these do you mean?

  1. Add a new row to the table with an automatically calculated sum column

  2. Calculate and store the sum column for every row in the table

Are you trying to create a backup of all the rows in the data table?

5

No, the app will get a dynamic calulation from 1 to 8760 rows (I have to select the start and end row)
Over different drop-down menu I have to select the appropriate Look-Up-Tables (Anvil Table).
Differnt Tables have to be combined as well completed thermodynamic calulations on the server side; all the output and input variables or results have to be written row by row in a Final Parametric Table as a Data Base.

Having this, the results from the Parametric Table are going on the GUI e.g. Plots, I will calculate AVG, MIN, MAX, SUM values and so on.

6

All of that can be done (assuming I’ve understood correctly), but none of it is one or two lines of Anvil code. Nothing of what you have in your current server functions is going to do any of that.

Have you been through the various Anvil tutorials? They’ll cover fetching a set of rows from a data table and processing them one by one. That’s the sort of thing you’ll need.

As you’re processing the rows one by one, you can add all the calculated columns you want, in a similar way to what you’re trying to do with your app_tables.data.add_row(TIME=TIME, DBT=DBT, sum= TIME + DBT) line.

7

Hi Jay,

thank you for the tutorial hint, would be nice if there would be an easy example.
I reduced the app now to one exmaple.
The sum of the table row is workingnow, however adding the sum to add_row not.
I do not get any error and the SUM Column empty?
What I am doing wrong, LOOP needed?

@anvil.server.callable
def get_product_data():
startrow = 0
stoprow = 5
for row in app_tables.data.search()[startrow:stoprow]:
sumrows = (row[‘TIME’] + row[‘DBT’])
print(sumrows)
app_tables.data.add_row(SUM = sumrows)

https://anvil.works/build#clone:ETFUJPTFIKBB6C6Z=57RM5OBB5YINBTGWLUBYLQOH

8

When you post code, put it between triple backticks ``` to maintain the indentation, that makes it easier to visually parse.

@anvil.server.callable
def get_product_data():
    startrow = 0
    stoprow = 5
    for row in app_tables.data.search()[startrow:stoprow]:
      sumrows = (row['TIME'] + row['DBT'])
      print(sumrows)
      app_tables.data.add_row(SUM = sumrows)

In this for loop you’re processing every row in the data table, then you add a new row with the sum. You can see those new rows in your data table, so you know the code is working.

Based on your comment, I think adding a new row isn’t what you want. You want to modify the current row. If you’re not sure how to do that, I suggest the News Aggregator tutorial for a good foundation in working with data tables: Anvil | Build Database-Backed Apps

9

Hi Jay,

thank you, I will remenber the bracket.
I did not refreshed the table so the values not visible.
Yes, I like to add a new Column with rows in this case SUM.
The SUM should start by row 1 with DBT and TIME = 1 and not after row 10.

The Columns DBT and TIME are fix as values.

Gerhard

10

If the tutorial isn’t giving you what you need, the docs on data tables (specifically on updating data tables) should: Anvil Docs | Using Data Tables from Python

11

Hi Jay,

appreciate your effort.
So, task solved.

By the way: I did not click the refresh data button on the table form :frowning: and lost much time to work on solved solutions. If you download the table as CSV, the data are correct even not refreshed on the table form. This should go automatically at any data change.

Cheers,
Gerhard

1 Like