Excel to R - Pivot + Bar Chart in Excel & R using tidyverse [Live Coding]

hey friends welcome to own little coder this is a new series that I'm hoping to start where I'm going to show something that you can do with Excel Microsoft Excel which is still one of the most popular data analytics tool on the planet and I'm going to do the exact same thing on our or specifically our studio so this is to help people who use Excel but to advance them to are are to evolve them into our world so this is my attempt I don't know how it is going to go so for this particular video we are going to use one of the tidy Tuesday data set that is food consumption and co2 emission data set this data sheet has the full columns country food category consumption co2 emission and this data set was contributed by a kasi Akuma so let us go ahead and start looking at it so I've got this data set my excel sheet and so the first thing that whenever we get a data in excel the first thing that we try to do is we try to you know let's assume in this case that I want to do a pivot and then try to see for a given country what is my total co2 emission so I'm going to select everything I'm going to say P word which is usually okay I have to increase my excel size because I'm forgetting my excel so I'm going to go to insert pivot table and then select everything and then see okay I've got this thing I'm going to select my country in my arrows and then I'm going to select my co2 emission which is sum okay that is good so at this point we have got row labels as the country and then we have got co2 emission so let's do one thing which is to sort this you vote emission okay sort is not available for us in this given case that is fine so ideally what we want to do is we want to see the total co2 emission for every content this is a weighted in Excel so do a pivot and then get the country in the rows and values in the value values in the values and the co2 emission and then we have done so let's go ahead and then see how to do the same thing in are so before that what we can do is I want to just copy paste this to different sheet and right click paste as values and then I want to say okay I want to sorted sorted by see what emission largest to smallest okay so I've got Argentine at the top and I've got Australia so I'm going to highlight the top 20 countries I'm going to put border than that they will smell it I'm going to say let's say conditional formatting I will just put a border okay so this is our first task okay so what I'm going to do is I'm going to first call the library tidy verse which is what we use for lot of data analysis so in this case I'm hoping that you would start the tidy verse which is slightly easier and I have this file in my downloads so I have to first read the file and so these are few things that a beginner might find bit you know a small overhead to start with but again if you want files you can directly go here and then you can extract read it but I'm going to show it programmatically so it is easier for you I'm going to call it zero to read underscore CSV I see users downloads and the file name is four underscore consumption dot CSV okay so it's a Windows problem after use this slash otherwise I have to use two slashes okay it is not available see user so I have forgotten my username okay the file has been successfully read you can see the file here and you can see four columns are there country food category consumption co2 emission and what we are going to do now is the same thing that we did here and this is co2 pipe so this is something called pipe where the output of the left-hand side is given as the input to the first argument of the function that you are going to use so we will keep it at this level to keep it in simple English it's just like a connector pipe so it takes something and then it gives back so pipe and you can see what we want to do is we first want to group by which is our label which is in this case country and then we have to summarize it and we after summarize it sing some of co2 is equal to let's take the column name column name is co2 emission sum of co2 emission so at this point you can see that you have got the columns or let me do you so that we can actually view it so view is a function for you to see something as a separate data frame so at this point you have got country one line one row and then you have got the total co2 but it is still not sorted so let's go ahead and then say arrange we want it in descending order and the column name for which we want arrange this sum of co2 no and you run it let's do you it's urgent you know Australia so you get the same output you can see the same output and you can in fact see that it's it's all same format at all these things so what we wanted to do is we want to extract top 20 so now you can simply say head of 20 after you sorted it now you're going to have 20 rows so now it's a next step let's remove all these things from this we don't need okay I definitely have to improve my Excel skills notice sure okay so for this 20 we will call it country okay now let's say we want to make a plot let's see what plot Microsoft District into us it says clustered bar mop they're not getting them back okay so let's just make this normal bad lot so we have um sorry we have co2 emission and we'll call it total co2 emission by country so let's make this plot and let's see how it looks so once we have this then we can say okay at this point I want to say ggplot which is what we are going to use to make lot GM call we want to make a column plot and a yes which is our aesthetics our x-axis is country and our y-axis is sum of co2 okay let's make a plot and see how it looks okay [Music] the plot looks like this not one of the greatest plot that you would see it's still the biggest problem is that it is not sorted so to sort that plot there are multiple ways the simplest way we would do is we would first sort the factor so country is equal to FC TD order of country by some of co2 okay I will just reduce the size sum of co2 and then we have this plot it's in descending order will make ascending order sorry be it was in ascending order and it will me descending order so now at this point you have these columns now you can see that these names are overlapped which you do not want and you want to make a bit of change in the theme and you want to give a title and the color to be blue so let's make a very similar to that or to say what we want to do is we want to fill the color with blue okay and then we want to say there is a package GGC which we recently saw we want to say easy rotate X labels that is done and then let us say theme minimal okay so what's the problem the problem is I have used pipe instead of plus inside ggplot you have to use plus outside GZ but you have to use pipe so let's zoom it I don't see what is owed the same let's run it once more and while getting any plot okay the problem is this pipe operator again you need to use plus so at this point you have got nice blue oh this rotating thing didn't work probably because we have all this extra levels so you have got a nice oh you've got a nice blue plot with x-axis rotated so maybe you can now let's say I want to add title labs of title is equal to total co2 emission by country and you want to say the datasource offer caption is equal to source tidy Tuesday if you want to call out the owner name so you can call out the owner name it so at this point you have a plot like this have a plot like this so now where if you actually see R is really really helpful for you than using Python sorry then using Excel Python is also equally important and using Excel is there let's say now someone tells you that instead of co2 emission at the start of the raw data you saw you have got a co2 emission and consumption right so let's say now someone says you want a consumption by country then you want co2 emission or again another plot so now what you have to do is you have to repeat the entire process you have to go make a pivot select the top five and you know you have to just repeat this entire process altogether so I think that is where having a programming language is quite handy because what you have to do is in that case in if it is an Excel so you have to make appeared first you have to sort it you have to extract it and then you have to repeat the entire process so it's like like five minutes of what we don't know probably like it would take few minutes for you but what we can do in this case is because we have got a programming language and simply copy and paste it and vari of you see what emission you can simply say consumption and some of consumption just because name is more representative and you can use some of consumption everywhere I can just you know find and replace some of consumption total consumption by country and if I just just few copy paste or I can simply use find and paste and then you can see at this point that we have got the solution already in hand so this is the biggest hydrant age of using a programming language than a gy like excel because you can just replicate it there the the power of reproducibility and scale is you could have seen that it would have taken multiple clicks multiple steps for us to do something that we repeated excel for a different column but in our it is just one line again you can do almost everything that you can do it with excel but I think for this initial video of this series I don't have a name but simply what you can do it in Excel you can easily do it in R or Python in this case I'm demonstrating it with R which is very very easy for anyone who does not understand any programming language so the idea is if you wanted to make pivot with one categorical variable and continuous variable and we wanted to sort it and then we wanted to make it a plot so we have done it with let's say like five six lines of our code which is plain English do you group it by which is like a pivot and then you summarize it use this raising values um and then you are arranging it in descending order then you are taking the head of top-20 and probably this is something that you need to understand to order the values and then you are making a plot column plot on your x-axis your y axis you have rotated the axis and you have title and everything so on you got a nice little plot like this with the source the title and x axis and y axis all these details you want to make a change you can make a change you can do anything that you want with this plot so I think that is where a programming language is really handy I hope this video is quite helpful for you too if you are an Excel user to get down started with all I hope I'll make more videos like this um take care see you bye bye