Animate Line Graph using Large Excel Data Sets

[timh32091073]

Hello everyone,

I'm trying to quickly animate a line graph to layover a video. I have a large amount of data in excel that can sometimes exceed 50K rows so inputting these values manually would be time consuming. Is there a way that I plot a line graph using my excel data? If I have collected data at 30fps, I would ideally like the line graph to plot each new point of data for each new frame (assuming video settings are 30fps). For consistency, I tried this on a random excel file that has x values (time) starting at 0 and increasing 1/30 each row. The y value is a function y=sin(x).

[Rick Gerard · Accepted solution]

The easiest way to do that would be to use the Graph functions in Illustrator, expand the appearance, then distribute the different lines to layers and import as a composition in AE. If you are clever you could use gradient fills in copies of the line files as gradient wipe sources and eliminate all but 2 keyframes and a little easing.

Here's a good HOWTO from Lynda.com on how to get started. It's pretty easy and copy and paste directly from Excel works very well.

The other option would be to break down the data into X Y components in Excell, convert that to a CSV file, and then copy the keyframe data of a couple position keyframes on a null layer, paste that keyframe data into an excel spreadsheet, fix the rows and columns, then replace the two existing x,y value rows with your x,y values from Excel. You'll get a 1 to 1 ratio that can be adjusted by using expressions, then the position data can be used to drive any number of things that will draw lines.  With 50K rows and that is going to be 50K frames so some serious work is going to have to be done by a master of the spreadsheet to average out the data. This is extremely cumbersome with lots of data so it's not really an option. There are scripts and 3rd Party plugins that make it a little easier.

If you are running CC 2018 you can use JSON files and AE to build graphs but it's a lot more work. Here's how to get started with that.

The problem with the last two options is that they require a lot more experience with AE. The JSON solution is elegant but requires skill sets that many AE artists do not have and can take a bit of work to master.

I think the Illustrator approach is going to be the most efficient workflow. If you turn the data into simple line graphs you can also extract position data from the paths, and that is going to be a lot easier to handle than trying to translate that much data directly into After Effects. I hope this helps.

Another off the wall approach would be to use an audio app to turn changing x and y values into different audio files. You could then convert the audio files to keyframes and use that data to drive animations. I'm not going to bother with showing you some examples of that technique, but I have used it. It can get pretty wild.

BTW, both of these trainers are vetted and they know what they are talking about.