CODEWITHSUNDEEP
rexcel
Alex
Alex

Reputation: 11

R import excel matrix with more than one row name

I'm completely new in R programming. I have to improt excel matrix into R and transform it into plain table. I have a problem just in the beginning:/ There is really lot of possibilities of excel/csv matrix import but i have no idea how to make it read more row names.

Here is the example:

          |  Year       a1  a2  a3
Colname1  |  ColName2   b1  b2  b3   
   c1          d1        x   x   x
   c2          d2        x   x   x
   c3          d3        x   x   x

a1,a2,a3 is a list of years b1,b2,b3 is a list of cities - this row has no name but it should

What i need at the end is to make a simple table

Colname1, Colname1, Year, City, Data

Upvotes: 1

Views: 926

Answers (2)

jb123
jb123

Reputation: 197

From my experience in general the easiest way is to prepare the dataset well in excel before importing it to R, especially when you are just starting to work with R. So depending on the size of your matrix (how many columns?), i would consider to adjust the column names in excel, that is using only one row for column names, e.g.

Colname1  |  ColName2  | b1, a1 | b2, a2 | b3, a3   
   c1          d1           x        x        x
   c2          d2           x        x        x
   c3          d3           x        x        x

A great tutorial on data import to R can be found here: https://www.datacamp.com/community/tutorials/r-tutorial-read-excel-into-r.

Upvotes: 1

Konrad
Konrad

Reputation: 18657

Assuming that you are working with the data resembling the Excel table below:

Table

You could import your file in the following manner:

require(readxl)
xlImp <- read_excel(path = "file.xlsx", sheet = 1,
                    col_names = FALSE, na = " ")

Preview

The obtained object will be easy to manipulate:

> head(xlImp)
        X0          X1 X2 X3 X4
1     <NA> Year        a1 a2 a3
2 Colname1    Colname2 b1 b2 b3
3       c1          d1  1  4  7
4       c2          d2  2  5  8
5       c3          d3  3  6  9

Cleaning

You could then use the imported data to undertake some basic operations, like setting up column names:

# Simple cleaning
Vectorize(require)(package = c("tidyr", "dplyr", "magrittr"),
                               char = TRUE)

# Set column names
xlImp %<>% 
  setNames(gsub(".", "", make.names(paste(.[1,], .[2,])), fixed = TRUE)) %>%
  ## Clean row redundant rows
  filter(row_number() %in% 3:dim(xlImp)[2])

Results

Those simple transformations would generate a (more or less) neat data frame that you should be able to easily reshape and manipulate as you please:

> head(xlImp)
Source: local data frame [3 x 5]

  NAColname1 YearColname2  a1b1  a2b2  a3b3
       (chr)        (chr) (chr) (chr) (chr)
1         c1           d1     1     4     7
2         c2           d2     2     5     8
3         c3           d3     3     6     9

Upvotes: 2

Related Questions