 Welcome. Today, we're going to be looking at generating, well, we've already generated some fake information that now we're going to use to calculate some pensionable salaries. So people have asked me for real-world scenarios. So I'm going to make this tutorial as real as I can with some very basic information. So far, what we've done, we have this project I have up on GitLab. Check out the links in the description of the video for it. Once you've cloned this repository, there's a folder called Yearly Salaries. And in there, there are some scripts, which we'll quickly review. So let me go ahead and just clear this out. First of all, there is a script in here called clean, which is going to remove all the files that we've generated with the scripts. If a file hasn't been generated yet, because we haven't gotten to it, you'll get. And there are nothing to worry about. But let's go ahead and just review what we've done so far. I have a get people script that downloads a file to CSV file, which is like a spreadsheet. And if I kept that out, you'll can see that it basically has some pretend people information, names, addresses, birthdays, phone numbers, that sort of thing. And we're going to use that to create another spreadsheet by randomly grabbing some of those names and generating some salaries. So now we're going to say create CSV, it's going to take a second again, it's going to go through that file, grab 100 random names and generate 25 years worth of made up salaries for them. Put it into a salaries.csv and there it is. Now in the last video, we used the usable.csv script, which basically replaces the comma as the delimiter with a pipe symbol. So now I can cut out that salaries.csv and you can see that we have these pipe symbols. It just makes it easier for us with the shell script to cut out these fields because we don't get kind of confusing with these commas in here. So now what do we want to do? What is the semi real world scenario of this? And I'm going to base this off of how things are where I work. So where I work, we have a pension and you vest six years and we have a 3% multiplier and you can start collecting when you're 55 or 25 years old or you've completed 25 years of service. So what does that mean? Was it 3% multiplier in the 25 years? There's a lot to it. The basic concept is you work at least 25 years theoretically to collect a pension and then you have a 3% multiplier. So you can work more than 25 years, but however many years you work, you multiply that by 3 and that gives you the percentage of what you'll get and it's based on your top five years, which is important because some places will do your last five years, which may not be your highest paid years. So basically what we need to do, let me just open up our salaries.csv in Libre office, is we want to go through each person's name. Look at all their salaries for the last 25 years and this is not really a truly realistic spreadsheet because here everyone's worked exactly 25 years where in the real world most people haven't gotten to 25 years yet and also some people might work more than 25 years and some people might work less, but they're older so they have a lower percentage, but anyway what we want to do is find the top five years for each person based on their salary, get the average of that, so add them all together, divide it by five to give us the average, then we want to multiply that by 0.75 to give us the 75% of those top five years and that is what that person will theoretically get as their pension every year and then we'll divide that by 12 to calculate their monthly payments and I actually get a sheet like this with everybody that's in the pension plan every year and what they've contributed each year and that's how it's calculated and we actually get a sheet that tells you what you're supposed to be getting, but the sheet they give us is never right because it's based on pensions that don't apply to ours so the numbers are off and so I have to do this each year and so you might ask why not just do the math in the spreadsheet because you could do it, but I get a spreadsheet each year so I'd rather write a script and when I get that spreadsheet at your point at and I'll get everyone's calculations for them. So let's go ahead and start working on that. I have some scripts here, let me list them out. Today we're going to be looking at the average, get average and get monthly. So I'm going to cat out the get average.sh. This is the script. Let me go ahead and run that, get average and I'm going to print out each person's name and this is what they will get each year. So let's go ahead and look at how that script works. So I'll cat it out again and I'll walk you through it step by step doing one line at a time. So let me open up another shell here and again let me go ahead and just grab one line so I'm going to say head-n1 so it's a little different than the code which we're looking through. I'm just going to show you based on one person's salary. So there is the first person's salary ignoring the line where I just received some mail. So there we go, we have that. So again we want to take each of those numbers, find the five highest, get the average of that and then 75% of that. So how are we going to do that? Well the way I went about doing it is as you can see here, this is where our code is starting, I'm going to use the TR command although you could use said and I'm going to replace all our delimiters which are the pipe character with a new line which this is why in the last tutorial we changed it from commas to pipe symbols otherwise we would be putting new line characters in the middle of numbers. So let's go ahead and do that. I'll cat out that or head out that first line there and I'll say TR, give it a pipe character and new line. So there we have new line but if I scroll up not only do we have the numbers for that person but we also have their name at the top. We don't want their name so the next part of this is I'm going to grep in brackets say 0-9 and that's going to give us only lines that have numbers so that will remove the line with the name in it. Next we want to do is we want to remove the dollar sign and comma from all these numbers and that's what this said command does. We went over this in the previous video, we'll go ahead and pipe into that and we'll say set and we're saying replace dollar sign with nothing replace comma with nothing. So now we do that and we get the list of numbers without the commas and dollar signs. Now that we have that we can use sort but we have to remember to use the dash G option because we're working with numbers. So here we sort so now our top number should be at the 5C. If we didn't use that dash G you can see the top numbers are not at the bottom. They're actually at the top because it starts with the one so we got to make sure that we put that dash G. Now that we have that, well we can use the tail command tail dash N5 and that will give us the last five numbers. So now we have the five highest years. Now we want to do math on that and there's different ways to do math in bash. I'm going to use BC which is a bash calculator. I'm pretty sure that's what it stands for. So I'm going to pipe this back in TR and I'm going to replace all the new line characters with a plus symbol. Boom! Now we have all our numbers here but we also have an added plus sign at the end. Different ways we can get rid of that. So what I'm going to do is I'm just going to add a zero to the end of that. So we're adding zeros at the end. So this may not be the cleanest way to do it but what I'm going to do is I'm going to put this, I'm going to say echo and put this inside parentheses with a dollar sign or with the quotations around it and I'm going to say plus zero and if I did that right, oh not plus zero but just zero. So now we have that same math with the zero and we can now pipe that into BC and that gives us all those numbers added together. Next thing we need to do is divide it by five to make sure that we get the right number. We need to put this like this so that we use the order of operations. So parentheses first so that we add all that together. I believe I put my parentheses in the right spot and I should be able to say divide by five and there we go. So that is the average of the top five years but we don't want the average top five years. We want 75% of that. If you work 25 years again times three is 75. If you work 30 years it would be more, it would be 90%. So we're going to say times 0.75 and there is the yearly average for that person. Then what we can do up in the script I use said but just for simplicity I'm going to use num format dash dash g and there we go and I can add, it's a little weird to do it this way. I'm going to say echo backslash dollar sign dollar sign parentheses. This is probably getting kind of confusing for people who are not very fluent in bash. What did I do here? I think I got it there. No, what did I do? It's getting confusing for me as well. So let's just go ahead and move this. Let me go back up a few commands. Okay, there's that. I add in echo quotation dollar sign and again should be backslash dollar sign dollar sign that and then then here is where I want this I think. No, I'm doing something wrong there. Again, writing this all as a one liner kind of confuses the mind a little bit. So let's go ahead. It's actually, maybe it goes here. Let's do pipe this invalid number. Anyway, trying to do as a one liner gets confusing which is why I put it in a script for you. So let's go ahead and look up at this script here and again. So instead of reading one line I'm going to cap the entire file. I'm going to loop through it so while and so each line is now called line in a variable. We're gonna say echo that line and cut the first column based on the limiters and it says field one. So that will print out the name. So that will print out the name and then we're going to do the same thing that we just did in the script adding that dollar signs at the beginning and using said to add the commas and when we do that we can now say get average and there we go. It lists out each person's name and their five year high five highest years at 75% underneath their name. Now we also want to know the monthly thing. So I have a script called get monthly and it's the same exact thing except for I'm throwing that monthly or that yearly salary into a file called average which is really the 75% of the average. So now I can cut that out. Here I add in this is where I add the dollar sign here and add in the commas. It's a little bit easier to read that way and then here I'm doing the same thing but we're adding the divide by 12. So now if I say get monthly I also add a divider line between each name. So you can see each person's name, their yearly retirement from their pension and their month retirement. These are some pretty high numbers but again I put an average from 30,000 to 120,000 and the main reason I did that was just so that we can have some larger numbers when it comes to sorting I could show you that once you get to 100,000 you have to make sure you sort it the right way. So that is that and that is all that we're going to do in this tutorial. Next time we're going to show how to generate other types of output for this. So I do thank you for watching. I hope that you are enjoying this. I know it's a bit much but if you've watched previous tutorials everything we're going through is just review and again scripting this out allows you to well script it out rather than having a spreadsheet that each person has a different spreadsheet. Here you can throw this script on a server and have it output the information. So thank you for watching. Visit filmsbychrist.com. That's Chris the K. There's a link in the description. As always I hope that you have a great day.