Data manipulation for importing file from excel to R

Question

I have an excel file as shown below.

         A    B    C     D   E   
2010    25   74   85    88  89
2011    27   86   97    99  
2012    37   115  131   
2013    47   146            
2014    56  

But When I am loading in R it is giving following output R Output:

    X__1    A    B    C     D   E   
1   2010    25   74   85    88  89
2   2011    27   86   97    99  
3   2012    37   115  131   
4   2013    47   146            
5   2014    56  

But my required output in R should be in the format shown below for my calculations :

         A    B    C     D   E   
2010    25   74   85    88  89
2011    27   86   97    99  
2012    37   115  131   
2013    47   146            
2014    56  

Can anyone please help me solve this issue

Answer 1

We can use the following code to set the row name

# Set the row name using X__1
rownames(dt) <- dt$X__1
# Remove X__1
dt$X__1 <- NULL

Or we can use tidyverse package to do this.

library(tidyverse)
dt <- dt %>% 
  # Remove row name
  remove_rownames() %>%
  # Set column as row name
  column_to_rownames("X__1")

Data

dt <- read.table(text = "    X__1    A    B    C     D   E   
1   2010    25   74   85    88  89
                 2   2011    27   86   97    99  
                 3   2012    37   115  131   
                 4   2013    47   146            
                 5   2014    56  ",
                 header = TRUE, fill = TRUE)