Using pandas with Anvil

Anvil Q&A
1

Hello there.
I am trying to make some data analysis.
Is there any way to make a Data Table into a Data Frame from the pandas Library ?
I tried first convert the Data Table into a CSV file and then build the Data Frame with that, but I couldn’t make it. I got an exception because there is info associated with other tables. For example: I have this table called Cases, where in each case I have a user. Instead of having the info of a user, the csv contains something like this #ROW[14424,1379343] wich I belive is the ID.
Is there any way to merge the tables so I can Get the correct csv ?
Can you Think of another way to make this Data Frame ?
Hope you can help me.

1 Like
2

So depending on what you are trying to do you might want to link the two tables together maybe into 1 table and pull from there. You are right that the 14424,1379343 is the row ID for that row in the table.

I haven’t tried this myself with Pandas but I have a lot of table links I use since I have users and those users have snakes they own in my system so I link the snake table to that particular user’s table/row.

Here is an example with mine where I have a snake table and link the snake to the owner’s table/row.

image
image.png1279×146 18.9 KB

1 Like
3

Hi @ldelfin,

If you want to construct a Pandas data frame out of data held in your Anvil data tables, I’d suggest you construct a list of dicts with the information you want, and then call DataFrame.from_dict() on it.

Eg, if I have a table called “people”, which has name and age columns, plus group which is a link to a row from the “groups” table, and the “groups” table has a column called name, I could do:

from anvil.tables import app_tables
import pandas

# Get an iterable object with all the rows in my_table
all_records = app_tables.people.search()
# For each row, pull out only the data we want to put into pandas
dicts = [{'name': r['name'], 'age': r['age'], 'group_name': r['group']['name']}
         for r in all_records]

df = pandas.DataFrame.from_dict(dicts)

# ...now I have my data as a Pandas data frame!

Does this make sense?

Depending on the amount of data you’re processing, it might take some time to load it all from the data tables, so you might want to run this in an Uplink script to avoid Anvil’s server timeouts.

9 Likes
4

Thank you very much meredydd, that was very helpfull !!!

1 Like
5

I have just started using SQL to work with large data sets, and it solved all my performance problems. I keep using the data tables when I deal with one form, small data sets or simple queries. But when things get too slow I fall back to the good old SQL.

The code is longer and less readable, but it runs much faster.

I like the fact that the same tables are accessible by both the user friendly app_tables and the fast psycopg2.

3 Likes
6

Hi there. I am new to Anvil and I tried to mimic your example using a python script on my machine, which was uplinked to my anvil app and I got it to work! I am sure I will have some additional questions moving forward but I am glad that it worked!

2 Likes