How to Make Pie Charts from Spreadsheets in After Effects

all right we should be good hey guys today we're making pie charts that are automatically generated from csv spreadsheet files in after effects and i should warn you that this is a little bit of a bulky tutorial but i'm going to walk you through every single step so the original concept for this project actually came from instagram from the account of tony agliada follow me on instagram uh if you don't follow tony then i highly recommend you going and doing so recently he's been challenging himself to create 99 different style frames and when i saw this specific post of a stylized pie chart i really wanted to see it put in motion so i messaged tony and he was gracious enough to let me try to put this in motion and as i was doing so i realized that it would be helpful to go through the process of creating pie charts from csvs so before we jump into the tutorial here's the final animation i came up with although i still don't think it quite does the original style frame justice and also a huge congratulations to tony on the completion of his 99 style frames project all right so here we are in after effects and let me just go ahead and show you what we're going to make and the project file for this will be available for download in the description of this video alright so it is a pie chart that animates on and it is drawing data directly from a csv spreadsheet file which is right here so if we were to change one of the values so from 144 to 300 and go back to after effects the chart automatically updates for accuracy let's go ahead and change that back and similarly if we replace this spreadsheet file which is located here at the bottom of the layer stack with a different csv by holding alt clicking and dragging then dropping after effects will warn us that our undo stack will be purged that's fine with me now everything has updated the labels the title the chart you will notice that that there's some data missing and that is quickly solved by simply duplicating a few layers and there we go all right so let's go ahead and build this from scratch so first and foremost we want to make sure that we have our spreadsheet set up correctly and here is that spreadsheet in the first column at the very top we want the title of the chart and underneath it the labels for all the data that we want to display and then in the second column at the top we're going to have the units and then the actual data underneath that's very important so back in after effects let's go ahead and look at what this csv spreadsheet file looks like on our timeline we have a data folder and inside is a property called number of rows which in this case is five because we have one two three four five data points or five rows of data then we have two more folders the top one contains all of our labels and the bottom one total min contains the actual data values that we'll be pulling to populate the spreadsheet and if we look one layer further we can see how after effects is getting this information it is looking in the file netflix csv and in array counting it is looking at cell one comma zero so looking back at the spreadsheet we can see that it is interpreting row b as column one so zero one and then it is looking at row two it completely skips the first row row two is the first row that it's reading so row two is zero row three will be one and so in array counting the first data point will be column one row zero and that is how it's finding this value of ten all right so with that out of the way let's go ahead and start making the pi chart i'm gonna make a circle and rename that layer that shape layer to pie chart hyphen 1 and then i'm going to make a blank shape layer and call it control make it yellow so i know it's a control layer and make it a guide layer just to be safe and now there is a little bit of preliminary setup involved here so just bear with me i'm going to go ahead and add a slider and duplicate that we want four sliders the first one we'll call size next one we'll call stroke width and then progress and then my nickname in college total value and then i'm going to go ahead and also add some color controls which i'm going to rename these to color hyphen one and then duplicate so we have five oops i think actually i put a space in there so we want to get rid of that okay and then each of these are going to be a different color so i'm going to go ahead and lock this effect controls panel and under our shape layer pull up the size property of our path and link that to the size slider oops and so it disappears because there's zero set for the value but if we put 500 now we can see it and then pull up the width of our stroke and link that and again we need to add a value so now under this the control layer these sliders will control the appearance of the slices of our pie chart and then lastly i'm going to go ahead and link the color of this pie chart slice to one of these color controls based on the layer name now you could go into the stroke color property but i find it more efficient to just add a fill effect so under that fill effect on our shape layer i'm going to add an expression i'm declaring a variable in this case i call it id and we type this layer dot name dot split we want to split the layer name at the hyphen and then we want the second part of the layer name split at the hyphen so in array counting that is number one and then we're going to go ahead and pick whip to our first color control effect on the control layer and in this expression where we reference the effect name we're going to delete the number and we're going to add the id variable that we just defined which is the number one so now when i hit enter and i just for the sake of demonstration if i duplicate this as the number automatically changes on this layer name then the expression on the fill effect of these new layers will automatically reference the different color control effects on the control layer okay so i'm going to delete these because we're not quite ready for that yet so now that we have our setup out of the way we're going to add a trim paths to this shape layer and we're going to link the end of this trim paths to our first value so in this case total minutes zero you can see what's happened is that now this absolute value of 10 has been translated into a percentage which is not quite what we want but it is a good start so if we look at what's happening here we're referencing the layer called netflix csv under the data folder and we're pulling this value but what we actually want to do is copy the expression that's directly pulling its value from the spreadsheet and paste it into the end property of our trim paths for flexibility instead of referencing this file name directly what we want to do is reference the file with the name of the layer at the bottom of our layer stack and we do that like this declare a new variable in this case i'm calling it csv name say this comp dot layer and what layer do we want well we want the one at the bottom so we'll say this comp dot num layers which will give us the total number of layers in the layer stack dot name and then we're going to change out this file name reference with this variable and whenever we duplicate this layer we don't want to have to manually input which cell of the spreadsheet to pull data from we want after effects to do that automatically and we can do that by declaring a variable in this case id we'll say this layer dot name dot split at the hyphen we want to take the second part of the layer name and then we'll put this variable as the second data point in this array and we can see immediately that the data has changed and this is because we're starting our layer counting at one but after effects is counting cells starting at zero so we need to subtract one from the id to start at zero so if i go ahead and duplicate this we can see that after effects is in fact populating each new slice of our pie chart with data from a different cell in our spreadsheet but the problem is that these data points that we're populating the end property of our trim paths with are not percentages they're just absolute values found in a spreadsheet what we need to do is calculate what percentage of the total of all values each value is and i'll show you how we're going to do that and this is why we added this slider called total value so here we're going to add an expression and we're going to declare the same variable to find our csv file saying this comp dot layer we want this comp dot num layers oops dot name and then we need to find how many data points there are that we're going to add together i'm going to do this by declaring a variable that i've named cell count then i'm going to go ahead and pick whip all the way down to the number of rows property in our csv so this will tell us how many data points we're going to be adding together and i'm going to go ahead and replace this file name with csv name that way if we replace that csv layer with a different spreadsheet file then nothing will break and then we're going to add one more variable called val for value we're going to set that equal to 0 and now we're all ready to add a loop which will add all the values together in our spreadsheet so we're going to say 4 parentheses i equals zero that is the starting value of our loop semicolon then if i is less than our cell count variable semicolon then i plus plus which will rerun the loop we'll add a brackets and we'll say val plus equals footage csv name data value parentheses 1 comma i semicolon and what this does is basically adds the data value of each cell to the variable val until there are no more data points to add giving us the total for all the data points in that column so if i click away we see that the slider value is now 196 and if we return to our spreadsheet and put in some parentheses highlight those cells we'll see that the sum of all the data values is in fact 196. perfect so now that we have a slider that has the value of all of our data cells we can go back into the end property of our trim paths on these shape layers which are the slices of our pie chart and modify our expression so that the data point we're pulling is actually a percentage of the total of all data points we'll do that like this we're going to make a new variable call it total val say equals and then we're going to pick whip to that total value slider we just calculated and then on the last line for simplicity we'll call this line data point then we just need to say data point divided by total val times 100. now let's go ahead and delete those other shape layers that don't have this expression applied and just duplicate this layer and we can see that progress is being made except that we need each of these slices to start where the previous one ends so we can do that pretty simply using the offset property of the trim paths like this let's go ahead into the second slice trim paths in the offset we're going to add an expression and we're going to go ahead and say id equals this layer dot name dot split split it at the hyphen and we want the second part and then we're going to subtract one and now we're going to create a variable that pulls the end value and converts it to an angle because the offset property is an angle not a percentage so we do that like this prev val is the name of our variable i'm going to go ahead and just pick whip to the previous slice end value and then where we have the number in the layer name i'm going to just add our id then i'm going to divide the whole thing by a hundred and multiply by 360. and then lastly i'm going to declare one more variable which i'll call o for offset and pick whip to the offset of our previous layer and in the same way delete the number from the layer name and add the id semicolon at the end and then add it all together with preval plus o perfect so what we're seeing here if i turn off these other layers is that the second slice of our pie chart is starting after the first slice ends so i'm going to delete the pie chart slice layers that don't have this expression applied and then duplicate this layer a couple times to take their place and now the question is how do we animate this and that's the reason that we have this progress slider i'm going to add a keyframe set to zero then another keyframe one second ahead and set that to one and then i'll go ahead and turn off all the slices except for the first one and in the end property of our trim paths i'm going to declare one more variable called p for progress and pick whip to that progress slider semicolon at the end and then in the final output i'll say p times final output and you can see that as the progress slider on the control layer of our composition goes from zero to one the the slice of our pie chart animates okay so now i just need to right click say copy expression only and then paste that on the end property of the rest of our trim paths and so now if i make all of these visible see that that is animating and we'll just add some ease here perfect okay so that is the basics of the pie chart but what about the graph name and the labels here and this counter in the middle well if we look at the source text of one of these labels you can see what's happened it's the same thing we've done before we've used the split expression to separate the last number of the layer name and then we've input that to pull the data from the cell of the spreadsheet that has the name of this label and with the title we had to do something a little bit different the expression looks at a data point in the spreadsheet and then it takes the name of its property group the last element we haven't covered is the counter and the way this was created here i'll just hide this for a second so i created a new text layer and then using the euchremedia counter hit play i keyframed this counter set it to round to the nearest integer and then under the counter property of the smart counter effect that was added i created a variable called total val and then pick whipped and we'll get an expression error for now pick whipped to the total val slider on the control layer then created a variable that links to the progress slider then used a linear expression so that as the progress slider goes from 0 to 1 the value of this property will go from 0 to the total value so putting it all together based on this beautiful style frame by tony ugliata i was able to create this animation so that's the whole tutorial uh if you found it helpful be sure to click the like button so youtube knows to promote it to other people and also subscribe to this youtube channel and if you have questions feel free to post them in the comments alright have a good day [Music] you

Channel: ukramedia

Views: 7,641

Keywords: How to Make Pie Charts from Spreadsheets in After Effects, How to Make Pie Charts from CSV Files in After Effects, Make Pie Charts from live Spreadsheets in After Effects, Make Pie Charts from live CSV Spreadsheets in After Effects, Adobe After Effects, How to, Video Tutorial, Video Lesson, Expressions, After Effects Expressions, Adobe After Effects Expressions, Andrew Marston, Motion Graphics, Motion Design, Pie Chart, Spreadsheet, Pie Charts in Adobe After Effects, split, split()

Id: VXzbjVSC1Ps

Length: 16min 46sec (1006 seconds)

Published: Thu Apr 29 2021