{"id":3503,"date":"2022-06-23T22:46:33","date_gmt":"2022-06-23T17:16:33","guid":{"rendered":"https:\/\/sheetstips.com\/?p=3503"},"modified":"2022-06-23T22:46:33","modified_gmt":"2022-06-23T17:16:33","slug":"how-to-convert-excel-content-into-dataframe-in-r","status":"publish","type":"post","link":"https:\/\/sheetstips.com\/how-to-convert-excel-content-into-dataframe-in-r\/","title":{"rendered":"How to Convert Excel Content into DataFrame in R ?"},"content":{"rendered":"<p>The R Programming Language allows us to read and write data into a variety of files such as CSV, Excel, and XML.<\/p>\n<p>In this tutorial, we will look at how to convert Excel data into a DataFrame in R. <span style=\"font-size: inherit;\">To read an excel file, use the read. xlsx() function of the xlsx package.<\/span><\/p>\n<p><strong>xlsx package:<\/strong><\/p>\n<p>The excel package is a robust Java-based language for reading, writing, and updating data from and to excel files.<\/p>\n<p><strong>Installation of xlsx package<\/strong><\/p>\n<p>Before we work with this package we must first install it. Type the below command in the R console:<\/p>\n<pre>install.packages(\"xlsx\")<\/pre>\n<p><strong>Output:<\/strong><\/p>\n<pre>Installing package into \u2018C:\/Users\/vicky\/AppData\/Local\/R\/win-library\/4.2\u2019\r\n(as \u2018lib\u2019 is unspecified)\r\nalso installing the dependencies \u2018rJava\u2019, \u2018xlsxjars\u2019\r\n\r\ntrying URL 'https:\/\/cloud.r-project.org\/bin\/windows\/contrib\/4.2\/rJava_1.0-6.zip'\r\nContent type 'application\/zip' length 1245703 bytes (1.2 MB)\r\ndownloaded 1.2 MB\r\n\r\ntrying URL 'https:\/\/cloud.r-project.org\/bin\/windows\/contrib\/4.2\/xlsxjars_0.6.1.zip'\r\nContent type 'application\/zip' length 9485708 bytes (9.0 MB)\r\ndownloaded 9.0 MB\r\n\r\ntrying URL 'https:\/\/cloud.r-project.org\/bin\/windows\/contrib\/4.2\/xlsx_0.6.5.zip'\r\nContent type 'application\/zip' length 375143 bytes (366 KB)\r\ndownloaded 366 KB\r\n\r\npackage \u2018rJava\u2019 successfully unpacked and MD5 sums checked\r\npackage \u2018xlsxjars\u2019 successfully unpacked and MD5 sums checked\r\npackage \u2018xlsx\u2019 successfully unpacked and MD5 sums checked\r\n\r\nThe downloaded binary packages are in\r\nC:\\Users\\vicky\\AppData\\Local\\Temp\\Rtmp84RZij\\downloaded_packages<\/pre>\n<p><strong>read.xlsx() Function:<\/strong><\/p>\n<p>The read.xlsx() function is imported from the R language\u2019s xlsx package and used to read\/import an excel file.<\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<pre>read.xlsx(\"excelFilePath, sheetName = \"sheet name\", \u2026)<\/pre>\n<p><strong>Parameters<\/strong><\/p>\n<p><strong>excelFilePath:<\/strong> This is reIt is the excel file path on which we work.<\/p>\n<p><strong>sheetName:<\/strong> It is the name of the excel sheet to be used.<\/p>\n<p>This function also has many other parameters like colNames, <code>rowNames<\/code>, <code>skipEmptyRows<\/code>, <code>skipEmptyCols<\/code>, <code>rows<\/code>, <code>cols<\/code>, etc., and so on to perform more modifications. These are all optional parameters.<\/p>\n<p><strong>colIndex:<\/strong> It is used to get only the columns that we wanted.<\/p>\n<p>In this case, we set the colIndex argument to 1 to get the first column of the Excel file.<\/p>\n<p>Let us use the sampleExcelFile.xlsx as an example here which is shown below:<\/p>\n<p><strong> sampleExcelFile.xlsx:<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3610\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/06\/sample-excel-file-sheet1-image.jpg\" alt=\"sample-excel-file-sheet1-image\" width=\"1391\" height=\"777\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/06\/sample-excel-file-sheet1-image.jpg 1391w, https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/06\/sample-excel-file-sheet1-image-300x168.jpg 300w, https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/06\/sample-excel-file-sheet1-image-1024x572.jpg 1024w, https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/06\/sample-excel-file-sheet1-image-768x429.jpg 768w\" sizes=\"(max-width: 1391px) 100vw, 1391px\" \/><\/p>\n<h2>Converting Excel Content into DataFrame in R<\/h2>\n<p><strong>Approach:<\/strong><\/p>\n<ul>\n<li>Importing xlsx package using the library() function.<\/li>\n<li>Give the excel file path and store it in a variable.<\/li>\n<li>Pass the excel File , sheetName , Index of the column and store that column in a variable.<\/li>\n<li>Create a dataframe of the above 9 columns using the data.frame() function and store it in another variable.<\/li>\n<li>Print the DataFrame using the print() function.<\/li>\n<li>The Exit of the Program.<\/li>\n<\/ul>\n<p><strong>Below is the implementation:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"r\"># Importing xlsx package using the library() function\r\nlibrary(xlsx)\r\n# Give the excel file path and store it in a variable\r\nexcelfilepath &lt;- \"C:\/Users\/cirus\/Downloads\/excelFile.xlsx\"\r\n\r\n# Pass the excel File , sheetName , Index of the column  and store that column in a variable\r\nWebsiteName &lt;- read.xlsx(excelfilepath, sheetName = \"Sheet1\", colIndex = 1)\r\nName &lt;- read.xlsx(excelfilepath, sheetName = \"Sheet1\", colIndex = 1)\r\nAge &lt;- read.xlsx(excelfilepath, sheetName = \"Sheet1\", colIndex = 2)\r\nCity &lt;- read.xlsx(excelfilepath, sheetName = \"Sheet1\", colIndex = 3)\r\nState &lt;- read.xlsx(excelfilepath, sheetName = \"Sheet1\", colIndex = 4)\r\nStudent &lt;- read.xlsx(excelfilepath, sheetName = \"Sheet1\", colIndex = 5)\r\nEmployed &lt;- read.xlsx(excelfilepath, sheetName = \"Sheet1\", colIndex = 6)\r\nMarried &lt;- read.xlsx(excelfilepath, sheetName = \"Sheet1\", colIndex = 7)\r\nScholorship &lt;- read.xlsx(excelfilepath, sheetName = \"Sheet1\", colIndex = 8)\r\nId &lt;- read.xlsx(excelfilepath, sheetName = \"Sheet1\", colIndex = 9)\r\n\r\n\r\n# Create a dataframe of the above 9 columns using the data.frame() function \r\n# and store it in another variable\r\nDataFrame &lt;- data.frame(WebsiteName, Name, Age,City,State,Student,Employed,Married,Scholorship,Id)\r\n# Print the DataFrame\r\nprint(DataFrame)<\/pre>\n<p><strong>Output:<\/strong><\/p>\n<p><strong>Dataframe<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">     Website.name. Website.name..1      Name Age        City          State Student Employed Married    Scholorship\r\n1      Sheets Tips     Sheets Tips    Vikram  21   Hyderabad      Telangana     Yes       No      No Not Applicable\r\n2  Python-Programs Python-Programs  Ashritha  22        Pune         Mumbai     Yes       No      No  Already Taken\r\n3       BTechGeeks      BTechGeeks    Manish  19      Indore Madhya Pradesh     Yes       No      No  Already Taken\r\n4      PythonArray     PythonArray     Anish  20      Rajkot        Gujarat     Yes       No      No  Already Taken\r\n5      Sheets Tips     Sheets Tips    Vishal  18      Ranchi      Jharkhand     Yes       No      No  Already Taken\r\n6  Python-Programs Python-Programs     Pavan  23     Lucknow   Uttarpradesh      No      Yes      No Not Applicable\r\n7      Sheets Tips     Sheets Tips      John  32 Los Angeles        America      No      Yes     Yes Not Applicable\r\n8  Python-Programs Python-Programs     Virat  34        Agra          Delhi      No      Yes      No Not Applicable\r\n9       BTechGeeks      BTechGeeks      Cary  36       Tokyo          Japan      No      Yes     Yes  Already Taken\r\n10     PythonArray     PythonArray      Tara  39    Helsinki        Finland      No      Yes     Yes Not Applicable\r\n11     Sheets Tips     Sheets Tips     Smith  42     Nairobi          Kenya      No      Yes     Yes Not Applicable\r\n12 Python-Programs Python-Programs Elizabeth  66       Paris          Italy      No      Yes     Yes Not Applicable\r\n<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The R Programming Language allows us to read and write data into a variety of files such as CSV, Excel, and XML. In this tutorial, we will look at how to convert Excel data into a DataFrame in R. To read an excel file, use the read. xlsx() function of the xlsx package. xlsx package: &#8230; <a title=\"How to Convert Excel Content into DataFrame in R ?\" class=\"read-more\" href=\"https:\/\/sheetstips.com\/how-to-convert-excel-content-into-dataframe-in-r\/\" aria-label=\"More on How to Convert Excel Content into DataFrame in R ?\">Read more<\/a><\/p>\n","protected":false},"author":3,"featured_media":3591,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[182],"tags":[],"jetpack_featured_media_url":"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/06\/How-to-Convert-Excel-Content-into-DataFrame-in-R.jpg","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/3503"}],"collection":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/comments?post=3503"}],"version-history":[{"count":7,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/3503\/revisions"}],"predecessor-version":[{"id":3619,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/3503\/revisions\/3619"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/media\/3591"}],"wp:attachment":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/media?parent=3503"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/categories?post=3503"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/tags?post=3503"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}