Enhance your R&D process with our pipeline tracking spreadsheet

How to create outlook signature

Hi my name is felix and today  i will show you how to read   and write hundreds of excel files  using the r programming language   so first we will begin by creating a very simple  loop that writes a couple of excel files into a   folder and then from this folder we will read all  the excel files that are in it using another loop   and i will show you guys how easy it is to  also add some r code inside the loop to clean   and validate the data that is inside your excel  files at the end of this video i will show you   also how easy it is to match all the data from the  different excel files that you have into a single   consolidated data set if you want to follow along  with this tutorial you can join my newsletters on   felixanalytix.com where you can download all  the code that being said let's get started   to begin i just want to quickly show you how to  read and write a single unique excel file using   r so first we will the r packages readxl  on writexl as well as a tidyverse for general data   transformations and as an example data set we will  use a txhousing data available when you load the   tidyverse R package and this dataset contains  data about sales by city on date in texas us so   let's create a folder when we will save our excel  file and to save this data set that we have here   in r as an excel file in your folder you can just  run write_xlsx and to read it into r you can just   run read_excel with the path of your excel files  and if you want to delete our excel files which   we will do we can use the unlink function now we  will see how to write multiple excel files in r   so every time we have a repetitive task in r we  want to make a function so here i have created   a function that takes our data set so texas  housing filter it by a given city name and save   this filter that i set as an excel file so for  example if our city name is abilene if i pronounce   it correctly our data set will contain only  information about this specific city and only then   our function will save this full trick that i set  as an excel file with the city name as the excel   file name in our folder so let's test our function  on the first city of our dataset and it looks like   it's working now we want to iterate and to do so  we will use the map function from the r package   purrr and we will iterate this function on every  city that we have in our list city names object   that we created with the unique function and we  see the loop writing the excel files isn't that   beautiful anyway as you can see we have indeed  successfully written 46 excel files in our folder   let's see how to read and join multiple excel  files using r so first we will create a list   of all the excel files we want to read into r so  for that we will use the list.files function but   don't forget to add the full.names = true  function to get the full pass of the files   otherwise you will not it will not work you will  not know why so be sure to add this full.names   argument equal true as we want to iterate over  multiple files once again we want to create a   function on which we want to loop so inside  this function you can add different tests   as well as data cleaning but to make  things as simple and as minimal as possible   just for this tutorial i just added a simple test  to check if the file path isn't a missing value   then as you can see the function reads the excel  files into r using the read_excel function but in   most use cases you will have to write additional  data cleaning on additional data validation let's   test our function on our first excel files to see  if it works yes it works now again we can loop   on all our excel files using a map function from  the purrr R package so the first argument should   be the list of all your excel file paths and  the second argument is a name of the function   we just created so now if we run our function  of all the paths and save the data in a df_list   object and if you want to extract for example the  first data set from new list you can use this code   on don't forget the double brackets over here now  if the different data sets of your excel files   have the same data structure meaning the same  columns or variable names as well as the same   data types can very easily join them together into  a single consolidated data set using the map_dfr   function from the purrr R package the df stands  for data frame and r is joining by row so let's   try this code and as you can see we have back  our original data sets that contains eight   thousand six hundred and two rows with nine  variables and we want to clean and remove the   texas housing folder on all the excel files you  can just run a link function with recursive as   true so now our working directory is clean well  i hope you find this tutorial useful if you did   just give me a like or a comment it's always  very appreciated if you want to get the code   just join my newsletters on felixanalytix.com  and i see you in another video bye-bye