0

I currently have a data file that has columns for each day over an 8-month period.

I am seeking of a way to create an interactive CARTO map that displays the change over time. I want to know if there is a way(or even possible) in Cartoframes or CARTO-VL that the animation over time can change by iterating through the date columns.

I know there is a way to display this change over time by making all date columns unique to each geography (polygon). The issue is I have nearly 850 geographies. Even limiting my dataset to 3 months, we are approaching 1 GB of data (due to the geometries of each field needing to be stored). The size won't generate a time series map on any library I've found or import to CARTO.

With the wide format (each date has its own field) the dataset is only 23MB for 8 months of data. I have not seen any examples iterating through columns but I feel there has to be a way (maybe I'm crazy).

Image of column sample:

I'm looking to iterate through each date column I have (1/1, 1/2, 1/3... Each of them associated to their respective polygon

2
  • Could you share a link to your dataset? If the dataset is private, could you share a dummy sample dataset with the right schema? Oct 9, 2020 at 14:18
  • shared it in the edited post. I want to iterate over 1/1, 1/2, 1/3.....to each day
    – sTonystork
    Oct 9, 2020 at 17:00

1 Answer 1

0

You would need to melt your dataset in order to transpose your columns into rows. In doing so, your dates will be stored as a new unique field and CARTO VL will be able to iterate throught them. There are two alternatives to achieve this.

a. Pandas' melt function:

df_long = pd.melt(df, id_vars=["id"], var_name="date", value_name="metric")

b. Postgresql pivot query. You can see a working example here.

Finally, I would suggest storing your unique geometries in a master table and join them when needed.

1
  • Ah yes I've done that already, the only thing is my data is over ~230 consecutive days, so the draw time and storage is quite difficult. I have since converted each polygon to centroids in order to have a visualization. I think the polygons are just too complex, and it seems there is no reproducible example to iterate through each column data field, only melt which just places each date into a record like you metioned
    – sTonystork
    Oct 15, 2020 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.