What if I tell, that you can export data frames to excel in R within a couple of minutes? This is possible with the help of functions like writexl and openxlsx. Seems interesting? In this tutorial, we are going to see how we can export data frames to excel in R.
Before we dive into the tutorials, let’s understand what is writexl and openxlsx functions.
Start with installing required packages
writexl: writexl is a library in R, which is capable of writing R data frames into excel files. To install this package, run the below code.
|
1 |
install.packages('writexl') |
Openxlsx: The R library which is very helpful in manipulating the data and export to Excel. To install this package, run the below code.
|
1 |
install.packages('openxlsx') |
Create a data frame and Export to Excel
Well, I hope you have successfully installed those packages. Now let’s create a data frame in R and export it to Excel using ‘writexl’ function.
|
1 2 3 4 |
#creates a dataframe in R mydataframe<- data.frame(name=c('John','Angelina','Lisa','Joseph','Antonio'), age=c(32,30,26,23,28), gender=c('male','female','female','male','male')) |
After creating this data frame, let’s export it to Excel using the writexl function.
|
1 2 3 4 5 6 7 8 |
#import the required library library(writexl) #exports the data to excel and dumps it to the working directory/folder #to see your working directory run 'getwd()' in R studio mydataframe<- data.frame(name=c('John','Angelina','Lisa','Joseph','Antonio'), age=c(32,30,26,23,28), gender=c('male','female','female','male','male')) write_xlsx(mydataframe, 'export_a_dataframe_to_excel.xlsx') |
The output excel file is shown below.
Import a dataset in R and export it to Excel
You have exported a simple data frame in R to excel in the above section. Here we are going to import a data set or a CSV file and export it to Excel file.
|
1 2 3 4 5 |
#imports the iris data set x<-datasets::iris View(x) #exports the data frames to excel write_xlsx(x, 'Exporitng_a_csv_file_to_excel.xlsx') |
Export a data frame to Excel by manipulating data using openxlsx
Till now, we have exported the data frames to Excel without any data manipulations. But what if you need to export the data with some meaningful changes? There comes the use of functions ‘dplyr’ and ‘openxlsx’.
Dplyr library will assist you in data manipulation i.e. you can make changes to the dataset by adding a column, a row, or a percentage and more.
Openxlsx function allows you to make all possible changes such as adding a style, font, color and you can also create a worksheet using this function.
The below image shows the basic data set available in R.
Let’s make some changes in the header of the data frame using dplyr. Here we are changing the color of headlines and alignment to center. We have also changed the values in the Illiteracy column as percentages.
Execute the below code to create excel file with new header style.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
#imports the required libraries library(dplyr) library(rio) library(openxlsx) #creates a data frame with percentage and manipulated it using dplyr mydata<- as.data.frame(state.x77)%>% mutate(State=row.names(state.x77),Illiteracy=Illiteracy/100)%>% select(State, Population:Area) #creates a new headline style my_headline<-createStyle(halign = "center",textDecoration = "Bold",fontSize = 14,fontColour = "blue") #changes the headline style write.xlsx(mydata, "new_header_style.xlsx", headerStyle=my_headline) #chnages the values in illeteracy column as percentages class(mydata$Illiteracy)<-"percentage" #exports the excel file with new header style and percentage of values write.xlsx(mydata, 'with_percentage_stye.xlsx', headerStyle=my_headline) |
The data frame with a new headline style and alignment style is shown below.
Create a new table style in R and export to Excel
You can also change the table theme or style by adding this piece of code to the above code.
|
1 2 |
#creates a new table style or theme write.xlsx(mydata, "new_table_style.xlsx", asTable = T, tableStyle="TableStyleLight1") |
Alright, we have created data frames, imported data frames, and exported to excel using the writexl and openxlsx functions. We did data manipulation and exported the data frame with a new look to excel.
But, there are some other possibilities that you can do in R. Let’s look into that.
Can we create a workbook in R and Export to Excel?
The answer will be a big ‘YES’. You can create an entire workbook and add worksheets into that with some data along with some changes which will be meaningful and make sense to readers.
Let’s create!!!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
#creates a new workbook with new worksheet myworkbook<-createWorkbook() addWorksheet(myworkbook,"MySheet") writeData(myworkbook,"MySheet",mydata) #adds the different nemurical styles and the number formates to data frame my_dollar_style<-createStyle(numFmt = "$0,0") my_percentage_style<-createStyle(numFmt = "0.0%") my_comma_style<-createStyle(numFmt = "0,0") #incorporates the styles to data addStyle(myworkbook, "MySheet", style = my_dollar_style, rows = 2:nrow(mydata),cols = 3) addStyle(myworkbook,"MySheet",style = my_percentage_style,rows = 2:nrow(mydata),cols = 4) addStyle(myworkbook,"MySheet",style = my_comma_style,rows = 2:nrow(mydata), cols = 9) #save the workbook with new number formats as well saveWorkbook(myworkbook,"with_numerical_styling.xlsx", overwrite = T) |
In the below workbook, you can see the income column has been added with a dollar sign and, values are separated by commas. These numerical format changes will make sense and convey the information in a more detailed way.
Conclusion
R is a perfect language for data analysis. You can do wonders using data in R. In this tutorial we have gone through the various ways of exporting a data frame to excel. You got to know how you can manipulate the data and bring some life into that before exporting to excel.
That’s all for now, stay connected for more R tutorials and I am sure that we can continue playing with some more data the next time. Happy exporting!!!