Combine Multiple Excel Sheets into a Single Table using R Tidyverse

[@clerisduchrist1253]

I really appreciate this video ! The tidyverse is a really powerful tool

[@JonathanNg]

Thanks for your comment. Tidyverse is awesome.

[@ikhlashusein2712]

Hey Jonathan. thank you for your valuable information. Just a quick question; how can i view the new file that combined those datasheets?

[@abderrahmankilali8096]

Thank you Jonathan this is really helpful

[@leesummers7461]

Thank you so much for making this video. Extremely helpful.

[@valensrwema]

Hi Jonathan, Thank you for the amazing work, I really appreciate it, Quick question: How to skip a sheet while combining other sheets together as one data frame? Thank you

[@lorenasalcesdourado2651]

Thank you Jonathan, very helpful!! How can I adapt this if one of the Excel Sheets does not have column names?

[@JonathanNg]

library(readxl) dataset

[@lorenasalcesdourado2651]

@@JonathanNg in this particular xlsx, sheet 1 has column names but sheet 2 doesnt. not sure if this suggestion would work

[@JonathanNg]

@@lorenasalcesdourado2651 Just use the names() function to take the column names from sheet1 and then apply them to sheet2 sheet1

[@ThePlum77]

Hello Jonathan, thank you for the video and keeping it simple. How to do the same exercise with CSV file having data in multiple sheets?

[@nurseryrhymes8804]

Thank you for this video. Is there any online link available on how to use data in the dt objects, for example, taking one column from one sheet and adding to another column in another sheet etc etc. Thank you.

[@kvafsu225]

Very informative and useful.

[@DaudSerkonos]

Perfection! Thank you so much !

[@parishahid]

Nicely explained the complex thing. Let me know how to export these merged excel file as new excel file.

[@NhuNguyen-mr5mm]

Thank you for the clip Jonathan. I wish to ask if there is a neat way to combine multiple sheets of an Excel file with sheets of another Excel file? Basically each Excel file has 10 sheets. The sheets within each Excel file has different format, but the 10 sheets of Excel file 1 have the same names and format as the associated 10 sheets of Excel file 2. Essentially each Excel file holds the different types of information of a different country, but the types of information collected are the same for each country. And I have many countries so I'm thinking of using a loop.

[@JonathanNg]

Yep you can do that. Map and loops achieve the same result of iteration but map tends to have a number of benefits. 1. It's more concise 2. It doesn't need to run in a sequence which means it can split the job into parallel to run faster. map will return all the sheets to a 10 item list which you can then bind/union together with your other list/s of 10 items.

[@NhuNguyen-mr5mm]

@@JonathanNg Thank you very much I'll try it out.

[@gabrielmesac]

Amazing man! keep shining

[@janardhanvenkatarama6134]

Hi Jonathan, Here you combine all sheets within a xlsx workbook. Can we do this also with xlsx files which are in a folder but with specifying a certain sheet within these xlsx files (e.g. from all xlsx files in a folder combining sheet number 3)?

[@JonathanNg]

Sure thing. Check out this video here. Just place 3 in the sheet parameter when writting the code. kzbin.info/www/bejne/apnKg2CkeqycY7s

[@janardhanvenkatarama6134]

@@JonathanNg Hi Jonathan, I checked that video but those files are either csv or txt files with delimiters, not common Excelfiles (.xlsx). I tried that out with xlsx files but unfortunately it didnt work. As you described in this video above within ONE excel file it is possible but with excel files in a folder and combining those all into a tibble by specifying e.g. always the 3rd sheet is not possible it seems. I really searched for it so many days now in the web...no chance to find a solution for this. The only way was to go for a loop which makes it a little more complex due to more code. Do you maybe know some good internet sources for this kind of issue? Sorry for chasing you but if I find a solution that would easy my work a lot by far. :-)

[@JonathanNg]

@@janardhanvenkatarama6134 It's hard for me to make a video about every specific situation, however you should be able to quite easily combine the information from the two videos with a little training and practice. Using the map function from one with the read function for whatever type of file you want to work with. If you'd like a little more assistance perhaps you might want to consider signing up to one of my courses where I can give you a bit more specific help. I can provide you with the exact code and help you debug any issues. Here's a discount code to the course here. datastrategypro.com/p/shiny/?coupon_code=KZbin2

[@janardhanvenkatarama6134]

@@JonathanNg Noprob, Jonathan. For Sure you cannot cover all specific issue here. Nevertheless, appreciate your support a lot. Your videos are really stunning and already helped me a lot!

[@ba-en1io]

hey, how can I manipulate the tibbles in the list to change their data types

[@JonathanNg]

Great question. To extract a single item to work on you'd use list[[index number]]. To iteratively work on every tibble in the list you'd use the map function. lists %>% map(function to perform). I'll need to go over this in more detail in a video some time. Thanks for asking.

[@alexcloake1314]

When I do this, it gives me ‘Error in app$vspace(new_style$’margin-top’ %|| 0): attempt to apply non function’ what does this mean? I have updated all packages Many thanks

[@JonathanNg]

sounds like you made a typo. The error attempt to apply non function means you probably sent the data to something which is not a function and therefore cannot process it.

[@darozo3]

Bro thanks,!! very simple explanation and high save of time!

[@kehindeodubela1917]

Thanks for the video! Is there a way to include a column in the combined table that shows the sheet where the data came from, or any other identifier? Thanks.

[@JonathanNg]

Yes absolutely. The gist of it is in lines 9-11 of the code in this video here kzbin.info/www/bejne/apnKg2CkeqycY7s . After you do this you can put the dataframe through an unnest() function to convert it back into a regular looking table.

[@DudeGuyWho]

Awesome content! Can you help me understand how to download a multi-sheet xlsx workbook from URL into R? It's only two tabs and I do know how to merge the tabs into a single dataframe once downloaded.

[@stevemack9221]

Side question. I've used the XLConnect package because it was the first one I came across and it worked. Is the readxl package objectively superior for some reason? Thanks...

[@giuliko]

It is way faster then any other xl package available out there. Specially when you are working with big workbooks containing several sheets each one, you want to use readxl. Also it's a better option to import excel files into shiny apps.

[@JonathanNg]

Thanks Steve for your question and@@giulikothanks for your input. Just to add to these points readxl is part of the tidyverse, loads data as a Tibble by default and has no external dependencies so it works across different operating systems.

[@stevemack9221]

Thanks much to Giuliko and Jonathan. I'm already wired into the Tidyverse. readxl is a very nice addition. BTW, Hadley Wickham is a hyper-prolific genius.

[@taranaferdous2858]

What if some of these sheets have different variable?

[@JonathanNg]

Generally it just combines them.

[@anamitralayek4140]

Hi Jonathan thanks for this video. I have two excel sheets and i just want some of the columns from the two sheets , the columns are same in both sheets . How can i make a consolidated excel file in R ?

[@rrrprogram8667]

Good one john

[@hamzarafique3857]

How could we split a data frame into multiple excel files based on specific criteria from any column? In this case, split this data into multiple excel files based on the year column with each year having its own separate file

[@JonathanNg]

Great question. I have a couple of videos that sort of cover this In this example I show how to partition a table based on a column to generate multiple reports kzbin.info/www/bejne/emeUknWNobuFoJI Here is another example showing how to batch create files kzbin.info/www/bejne/apnKg2CkeqycY7s There is also a split function that works well with these techniques. I'll look at creating a more specific video about this.

[@JonathanNg]

Join 15,000 to Learn Advanced Data Analytics Skills. Through step by step lectures, complete with downloadable code examples and student support. R Tidyverse Reporting and Analytics for Excel Users Level up your advanced data analytic skills. Learn one of the fastest easiest ways to work with large datasets. www.udemy.com/course/r-tidyverse-reporting-and-analytics-for-excel-users/?referralCode=E6333F435381F1A83CCD R Shiny Flex Dashboards and Interactive Data Visualizations The fastest, easiest way to get up to speed with R Shiny to transform your analysis into data driven applications. www.udemy.com/course/r-shiny-flexdashboards/?referralCode=15405DCD94D9E80B5440 Easy Excel Dashboards, Models, Visualizations with Power Query Better than VBA. A no code way to easily transform data and build automated reports with Microsoft Excel www.udemy.com/course/easy-excel-dashboards-models-visualizations-power-query/?referralCode=331E9DFAE5A544402066 What do you want to know about advanced analytics and data science? Leave a comment below.

[@JidduVillarin]

Hey Jonathan, Thanks for the information presented. It's an approach I haven't come across yet and it seems like a nice addition to the toolbox. Is there a variant that pulls all the sheets from multiple files?

[@JonathanNg]

Yes you could do. I would take the code from this video. Wrap it in a function and then call that function passing in each of the excel files similar to this video kzbin.info/www/bejne/apnKg2CkeqycY7s I'll see if I can come up with an example.

[@lindseymaite964]

You saved my life

[@JonathanNg]

I'm really glad you found it helpful. Thanks for letting me know.

[@EverythingSpecial1]

When I tried to merge the excel sheets, each sheet containing 800 columns, I got an error message saying the column could not be converted from character to numeric. But when I tried to run same data with just few columns, it works. What should I do for the original data to merge.

[@JonathanNg]

This is a very common data issue when the expected type is numeric but a subsequent file returns character. The easiest way to deal with this is to for the column to be character in the first place so it captures everything. After that you can clean up the cell by doing things like removing non-numeric chracters and then doing a type conversion.

[@oluwaseunojo8958]

Got error message "could not find function "map_df""

[@JonathanNg]

You probably don't have the library(tidyverse) installed or loaded.

[@TheExceptionalState]

Super clear and very helpful. Thanks!

[@bartomiejcal4073]

Is it possible to refer to those sheets by names ?

[@JonathanNg]

Yep

[@bartomiejcal4073]

@@JonathanNg In your exampe dt[2] works but dt['chevrolet'] does not. Is it possible to refer to it by name somehow?

[@JonathanNg]

@@bartomiejcal4073 in this example each table is stored in an un-named list. So the first step is to apply names to each element of the list via this line of code names(dt)

[@GorArkson]

I already added this to my "codes to remember" list, but the tilde and dot still keeps bothering my curiosity. Can you further explain why I needed the tilde and dot, and what does an anonymous function mean? btw, I tried experimenting it by removing it and adding it back to find some clues on what it actually returns. But I still did not understand it. Thanks again for the great upload!

[@JonathanNg]

Thanks that's a great question. Probably best for me to create another video on this to do this question justice. ~ is a short hand used to create an un-named (anonymous function). This makes your code shorter and easier to manage when you do need to write your own functions that only work within a limited context. Pipe %>% enables concise code by making assumptions about where data is passed into the function. ~ and . allow you to override this default behaviour to get more control. In short these are great shortcuts for rapidly building flexible, concise code. To fully understand this you need to have a bit of background in Tidyverse and writting your own functions.

[@rexevan6714]

map, map_df is function from purrr package.. If you want to know more about purrr, check out this video kzbin.info/www/bejne/bYbPiH2Nm7-Cb68 The tilde ~ is also explained there.

[@Sumner18]

I asked a similar question on another video. But I'm trying to combine 76 .xls files, each with 36 sheets, and each sheet containing 30 columns and about 1300 rows. Is there not a way that I could pipe the .xls file names into this code and append all sheets from all .xls files into one large dataframe? A for loop just seems way too slow.

[@shailendrakumaryadav2936]

I have multiple excel files and every excel file the same name of sheets with the same name of columns. i want to import and cbind to all sheets of different excel files.

[@danadaurenkyzy]

Thanks! But I have a small problem, the numbers get rounded in R, how can I get the same numbers with 4 decimals? P.S. I have . as decimal separator in excel file.

[@shubhamgaikwad3240]

Its giving me error can't be converted from character to POSIXct/ POSIXt

[@JonathanNg]

POSIXct/ POSIXt are date types. Dates are always tricky because they come formatted so many different ways. If you specify the format of your dates it can reduce a lot of errors.

[@zoso103]

Thanks so much!

[@ANKIg2023]

Thank you :)