Combine some columns of two matrices but with common information transposed

0
votes

I have the following two matrices:

matrix1 (first 10 rows and only some relevant columns):

   Prod_Y2010 Prod_Y2011 Prod_Y2012 Prod_Y2013 Prod_Y2014 Place
1        6101       5733       5655       5803       5155     3
2        4614       4513       4322       5211       4397     1
3        5370       5295       4951       5145       4491     3
4        5689       5855       5600       5787       4848     1
5        3598       3491       3462       3765       3094     2
6        6367       6244       5838       6404       5466     7
7        2720       2635       2465       2917       2623     2
8        5077       5113       4456       5503       4749     8
9        5260       5055       4512       5691       4876     2
10       4771       4583       4202       5266       4422     2

where each column is grassland productivity from years 2010 to 2014, and the last column is the place where productivity was measured.

and matrix2:

   Year Rain_Place1 Rain_Place2 Rain_Place3 Rain_Place7 Rain_Place8
11 2010       123.0       361.0        60.5       469.7       492.3
12 2011        45.5       404.4       224.8       395.4       417.3
13 2012       318.7       369.4       115.7       322.6       385.8
14 2013        93.2       378.4       155.5       398.2       413.1
15 2014       216.8       330.0        31.0       344.0       387.5

where for each of the same 5 years of matrix1 (which are the rows in matrix 2) I have data on the rainfall for each place.

I do not see how to proceed in R to join the information of the two matrices in such a way that my matrix1 has a series of additional columns intercalated (or interspersed) with the corresponding rain values matching the corresponding years and places. That is, what I need is a new matrix1 such as:

  Prod_Y2010 Rain_Y2010 Prod_Y2011 Rain_Y2011   Prod_Y2012  Rain_Y2012 ... Place
1        6101       60.5    5733        224.8       5655          115.7         3
2        4614       123.0   4513        45.5        4322          318.7         1
3        5370       60.5    5295        224.8       4951          115.7         3
4        5689       123.0   5855        45.5        5600          318.7         1
5        3598       361.0   3491        404.4       3462          369.4         2
...     ...         ...     ...         ...         ...             ...      ...

Of course the order is not important to me: if all the Rainfall columns are added as new columns at the right end of matrix1, that would be fine anyway.

Needless to say, my real matrices are several thousands rows long, and the number of years is 15.

1
rmatrix
Do you need to keep the expected output in the wide format? – jazzurro

1 Answers

0
votes

I would second @jazzurro's comment- reformatting your data to long format would likely make it easier to work with for analysis etc. However, if you want to keep it using the wide format here is a way that might work- it uses the reshape2 and plyr libraries.

Given these data frames (dput() output of your data frames above, only included for reproducibility):

m1<-structure(list(Prod_Y2010 = c(6101L, 4614L, 5370L, 5689L, 3598L, 
6367L, 2720L, 5077L, 5260L, 4771L), Prod_Y2011 = c(5733L, 4513L, 
5295L, 5855L, 3491L, 6244L, 2635L, 5113L, 5055L, 4583L), Prod_Y2012 = c(5655L, 
4322L, 4951L, 5600L, 3462L, 5838L, 2465L, 4456L, 4512L, 4202L
), Prod_Y2013 = c(5803L, 5211L, 5145L, 5787L, 3765L, 6404L, 2917L, 
5503L, 5691L, 5266L), Prod_Y2014 = c(5155L, 4397L, 4491L, 4848L, 
3094L, 5466L, 2623L, 4749L, 4876L, 4422L), Place = c(3L, 1L, 
3L, 1L, 2L, 7L, 2L, 8L, 2L, 2L)), .Names = c("Prod_Y2010", "Prod_Y2011", 
"Prod_Y2012", "Prod_Y2013", "Prod_Y2014", "Place"), class = "data.frame", row.names = c(NA, 
-10L))

    m2<-structure(list(Year = 2010:2014, Rain_Place1 = c(123, 45.5, 318.7, 
93.2, 216.8), Rain_Place2 = c(361, 404.4, 369.4, 378.4, 330), 
    Rain_Place3 = c(60.5, 224.8, 115.7, 155.5, 31), Rain_Place7 = c(469.7, 
    395.4, 322.6, 398.2, 344), Rain_Place8 = c(492.3, 417.3, 
    385.8, 413.1, 387.5)), .Names = c("Year", "Rain_Place1", 
"Rain_Place2", "Rain_Place3", "Rain_Place7", "Rain_Place8"), class = "data.frame", row.names = c("11", 
"12", "13", "14", "15"))

To get the place number from the column names in your rain data frame to use in a later join:

rename <- function(x) {
  y <- substr(x, nchar(x), nchar(x))
  return(y)
}

Edit: Here is a better rename function, that should work with more than 9 places (modified from an answer here):

rename <- function(x) {
  y <- unlist(regmatches(x, gregexpr('\\(?[0-9,.]+', x)))
  return(y)
}

sapply(names(m2[2:ncol(m2)]), FUN = rename)

names(m2) <- c(names(m2)[1], sapply(names(m2[2:ncol(m2)]), FUN = rename))

> m2
  Year     1     2     3     7     8
1 2010 123.0 361.0  60.5 469.7 492.3
2 2011  45.5 404.4 224.8 395.4 417.3
3 2012 318.7 369.4 115.7 322.6 385.8
4 2013  93.2 378.4 155.5 398.2 413.1
5 2014 216.8 330.0  31.0 344.0 387.5

Melt the rain data frame:

m3<-melt(m2, id.vars = "Year", variable.name = "Place", value.name = "Rain")

> head(m3)
  Year Place  Rain
1 2010     1 123.0
2 2011     1  45.5
3 2012     1 318.7
4 2013     1  93.2
5 2014     1 216.8
6 2010     2 361.0

Reshape the melted data frame to allow for a join by "Place", and treat "Place" as a character rather than a factor:

m4<-reshape(m3, idvar = "Place", timevar = "Year", direction = "wide")
m4$Place <- as.character(m4$Place)
> m4
   Place Rain.2010 Rain.2011 Rain.2012 Rain.2013 Rain.2014
1      1     123.0      45.5     318.7      93.2     216.8
6      2     361.0     404.4     369.4     378.4     330.0
11     3      60.5     224.8     115.7     155.5      31.0
16     7     469.7     395.4     322.6     398.2     344.0
21     8     492.3     417.3     385.8     413.1     387.5

Finally, join this melted/reshaped data frame to your "Prod" data frame.

m5<-join(m1, m4, by = "Place")

> m5
   Prod_Y2010 Prod_Y2011 Prod_Y2012 Prod_Y2013 Prod_Y2014 Place Rain.2010 Rain.2011 Rain.2012 Rain.2013 Rain.2014
1        6101       5733       5655       5803       5155     3      60.5     224.8     115.7     155.5      31.0
2        4614       4513       4322       5211       4397     1     123.0      45.5     318.7      93.2     216.8
3        5370       5295       4951       5145       4491     3      60.5     224.8     115.7     155.5      31.0
4        5689       5855       5600       5787       4848     1     123.0      45.5     318.7      93.2     216.8
5        3598       3491       3462       3765       3094     2     361.0     404.4     369.4     378.4     330.0
6        6367       6244       5838       6404       5466     7     469.7     395.4     322.6     398.2     344.0
7        2720       2635       2465       2917       2623     2     361.0     404.4     369.4     378.4     330.0
8        5077       5113       4456       5503       4749     8     492.3     417.3     385.8     413.1     387.5
9        5260       5055       4512       5691       4876     2     361.0     404.4     369.4     378.4     330.0
10       4771       4583       4202       5266       4422     2     361.0     404.4     369.4     378.4     330.0