Streamline Your Bill Excel for R&D Processes Effortlessly

Bill excel for R&D

Excel files are messy but they are a sad fact of life so let's learn to clean up messy Excel files with R in this Excel files we can see a lot of orders from different segments these segments are labeled consumer corporate or home office and for each one of those segments there is a shipping methods labeled first class same days second class or standard class also for each segment there is a total column that we don't need alright that's our data let's clean this we're first going to set our directory so that we can always find our Excel files then we are going to call the read xlsx function and specify the file name that we want to load and what I like to do is to specify the sheet that we want to load from our Excel files otherwise if I don't specify it it will just load the first one calling this will give us this kind of table we can see here that the column names are basically the first row from that Excel file and what we really want is to use the second row as current names so let us skip the first row then we'll have these column names and these are very bad color names due to the fact that they use these little quotation marks that are just annoying to type so we're going to fix them by using the name repair argument and we're going to set it to the make clean names function from janitor now we can see that we have programming friendly column names also notice that the column names for the total columns became x x underscore 2 and X underscore 3. since we don't need them let's first remove them to do so we first load the Tidy verse which will also give us access to all the cool functions that we need for cleaning our data sets then we're just going to select the columns that start with x these are exactly the columns you want to get rid of so we negate our previous call to get rid of those columns then we take care of the first row of our data set all we need from this is the name order ID so let's manually set the column label to order ID and make the first row go away a slice now we need to make sure that the column names contain references to their segment to do so we create a vector that describes our segment names we then do the same for the shipping modes and then we combine these two vectors by repeating the segment names once for each shipping mode and we repeat the shipping modes as often as we have segments in this Vector we have most of the new column names now let us replace the rename call by rename with that uses a function defined by the tilde operator that always returns a column Vector with our desired column names all that is left to do is to rearrange the data from all what the First Column what we need for that is the regular expression that can detect the segment and shipping method from the column names once we have that regular expression we can use pivot longer to rearrange the data by targeting all but the First Column here we use our regular expression for the patterns of the current column names and map the matches from that regular expression to segment and shipping mode and of course we want to get rid of all the missing values that are currently in our data set perfect now we have clean data and if you want you could also sort this data set our second Excel file looks basically the same as the first one except for the fact that it doesn't have total columns also instead of order ID it uses order dates this means that we can clean that file with mostly the same code we just have to use the new file name get rid of the part where we remove the totals columns and instead of order ID we use order date cool that was easy what's left to do is to take care of those annoying Excel dates to do so we first convert them to numeric vectors instead of characters and then we use the Excel numeric to date function from janitor to convert these numbers to actual dates in our next example we have all of our data mixed into single rows if we look closely we can see that we can extract names addresses agent and genders from these rows we do this by loading the data as before and what we get is a table where all of the information is in one column so we use the separate function to Target that column and separate it into name address age and gender and the way to split that is to use a regular expression that always has a white space followed by one of the four categories and yet another white space the results aren't perfect because the First Column still contains the word name also notice the small quotation marks in the address column they are there because some of the street names still have trailing white space we fix all of this by first removing the word name followed by an empty space in the name column then we can get rid of the leading or trailing white space in the address column by using Str trim before we move on to the next example let me remind you to hit that like button if you find this stuff useful so with that said let's get back to the content in our fourth example people we take a look at an Excel file that uses a column which mixes a quantity with its unit once again we load the data and then we just use Str extract to well extract the words from the quantity column here I'm using a regular expression that is looking for small letters capital letters or a DOT the plus means that I'm not only looking for a single one but for one or more letters this will give us the unit and then we can extract the quantity by calling plus number which will just extract the first number it finds in each entry of the quantity column in our next Excel file we try to get rid of typos where numbers are by mistake spelled with for example O's instead of zeros you can find these cells easily as they are left aligned instead of right aligned like the regular numbers so let us load the Excel file and create a vector that contains all of the things that we want to replace we can use this replacement Vector in Str replace all by calling mutate and since we want to Target all of the columns here we will need to use the across function as well it helps us to apply a function to multiple columns in this case we apply a function that puts the column's content into Str replace all as first argument and our replacement Vector as a second argument in our next example we can see an Excel file that combines multiple things with these vertical lines in both the category and the amount column to untangle this we first load the data set as we need to Target multiple columns we use mutate and across again then we target all but the First Column and use the Str split function using this vertical line as a split indicator notice that this vertical line is a special character in regular Expressions so we need to escape it with two backslashes once we execute this we get a nested table with all of the split stuff to untangle this nested table we just use our Nest on all but the First Column and and in our final example we have a data set with empty rows and columns or columns that don't have any information at all also we again see date columns that do not have a proper date format we can fix all of that with help from more janitor functions so for the last time we load an Excel file and then we can remove empty columns and rows with remove empty then we can also remove constant columns which in this case will get rid of the column that contains only yes finally we can fix the dates once again using the Excel numeric to date function alright we have cleaned six Excel files so that's it for today thanks for watching and don't forget to give me a thumbs up if you like this video have a nice day and see you next time