Finding correlation between two data sets in R

4
votes

UPDATED DATA SET 2 and 1 STRUCTURE: Sorry for this sudden update. I have two data sets. The structure of my first data set is (when using print(matr1) in R):

        month_year  income
 [1,]  "Jan 2000"  "30000"
 [2,]  "Feb 2000"  "12364"
 [3,]  "Mar 2000"  "37485"
 [4,]  "Apr 2000"  "2000"
 [5,]  "Jun 2000"  "7573"
          .     .      .
          .     .      .

Thus the first data set has one income value for each month of each year.

The structure of my second data set is (when using print(matr2) in R):

     month_year     value
 [1,] "Jan 2000" "84737476"
 [2,] "Jan 2000" "39450334"
 [3,] "Jan 2000" "48384943"
 [4,] "Feb 2000" "12345678"
 [5,] "Feb 2000" "49595340"

          .     .      .
          .     .      .

So in this second data set I have n(say 100 but not constant all the time) number of values for each month of each year.

Both the data sets have values month wise for many subsequent years(like for all the months for year 2000, 2001 and so on). Now I want to find the correlation between these two data sets but month-wise and not as whole. When I use the R command cor(as.numeric(matr1[,"income"]),as.numeric(matr2[,"value"])) then I get the overall correlation but I want the correlation per month rather than as a whole. I want the correlation something like this:

                  Jan | Feb | Mar | Apr | May | .....
Correlation        x  |  y  |  z  |  p  |  q  | .....

The issue that I have is:

  1. How do I get the correlation value per month instead of the overall correlation?

NOTE: I am not sure whether I should have posted this question here or on Cross Validated. I had posted a question for this data set only regarding an error in getting correlation and it was migrated from there to here. So please forgive if I am posting this on wrong place.

UPDATE1: After some suggestion I have modified this post to point in the correct dimension. First of all, the data sets as of now are in matrix format and hence the quotes. I can convert it to data.frame as suggested by some comments but right now I have been calculating the correlation by converting the columns using as.numeric.

2
rcorrelation
I see you're using a matrix. data.frame or ts object would be more appropriate for these sort of matters. – Roman Luštrik
Does each of your data sets have one income/value for each month/year? Then how do you expect to compute a correlation? Apart from that statistical query, your question needs a lot of other help, such as why is everything in quotes (A: you haven't read your data in properly) and how do I convert things to dates (A: see about a hundred other SO questions and/or install the "lubridate" package). – Spacedman
@Spacedman, everything in quotes because he has a matrix, and matrix can't have both character and numeric classes at the same time, so the coercion hierarchy was implemented here by default I guess. – David Arenburg
@Spacedman I have done some modification to above post. I understand that by having only one value of month in each data set we cannot get the correlation. I have values for month for each year in both data sets(for April in 2000 as well as 2001 and so on in each data set and for each month in that way) – Jason Donnald

2 Answers

2
votes

May be you can try:

dat1 <- structure(list(year = c(2000L, 2000L, 2000L, 2000L, 2000L, 2001L, 
2001L, 2001L, 2001L, 2001L), month = c(1L, 2L, 3L, 4L, 5L, 1L, 
2L, 3L, 4L, 5L), income = c(30000L, 12364L, 37485L, 2000L, 7573L, 
25000L, 14364L, 38485L, 4000L, 7873L)), .Names = c("year", "month", 
"income"), class = "data.frame", row.names = c(NA, -10L))

dat2 <- structure(list(month_year = c("Jan 2000", "Feb 2000", "Mar 2000", 
"Apr 2000", "May 2000", "Jan 2001", "Feb 2001", "Mar 2001", "Apr 2001", 
"May 2001"), value = c(84737476L, 39450334L, 48384943L, 12345678L, 
49595340L, 84337476L, 34450334L, 48984943L, 124545678L, 49525340L
)), .Names = c("month_year", "value"), class = "data.frame", row.names = c(NA, 
-10L))



 dat1$month_year <- paste(month.abb[dat1$month], dat1$year)
 dat1$month <- gsub(" \\d+","", dat1$month_year)
 dat2$month <- gsub(" \\d+","", dat2$month_year)
 dat1$indx <- with(dat1, ave(month, month, FUN=seq_along))
 dat2$indx <- with(dat2, ave(month, month, FUN=seq_along))
 dat1 <- dat1[,c(2,3,5)]
 dat2 <- dat2[,c(3,2,4)]
 colnames(dat2)[2] <- "income"

 library(reshape2)

 dat2C <- dcast(dat2, indx~month, value.var="income")
 dat1C <- dcast(dat1, indx~month, value.var="income")
 m1 <- as.matrix(dat1C[,-1])
 m2 <- as.matrix(dat2C[,-1])
 cor(m1,m2)
  diag(cor(m1,m2))
 # Apr Feb Jan Mar May 
  #1   -1   1   1  -1

Also, if you can merge the two datasets together, this could be done using data.table. Using the dput() data above

 library(data.table)
 dat1$month_year <- paste(month.abb[dat1$month], dat1$year)
 dat1 <- dat1[,c(4,3)]
 setDT(dat1)
 setDT(dat2)
 setkey(dat2, month_year)

 dat2[dat1, income := i.income]
 dat2[,month:= gsub(" \\d+", "", month_year)][,cor(value, income), by=month] 
 #    month V1
 #1:   Apr  1
 #2:   Feb -1
 #3:   Jan  1
 #4:   Mar  1
 #5:   May -1

Update

dat1 <- structure(list(month_year = structure(c(5L, 3L, 8L, 1L, 7L, 6L, 
4L, 9L, 2L), .Label = c("Apr 2000", "Apr 2001", "Feb 2000", "Feb 2001", 
"Jan 2000", "Jan 2001", "Jun 2000", "Mar 2000", "Mar 2001"), class = "factor"), 
income = c(30000, 12364, 37485, 2000, 7573, 42000, 15764, 
38465, 5000)), .Names = c("month_year", "income"), row.names = c(NA, 
-9L), class = "data.frame")


 dat2 <-  structure(list(month_year = structure(c(5L, 5L, 5L, 3L, 3L, 7L, 
 7L, 7L, 1L, 1L, 6L, 6L, 4L, 4L, 8L, 8L, 2L, 2L, 2L, 2L), .Label = c("Apr 2000", 
 "Apr 2001", "Feb 2000", "Feb 2001", "Jan 2000", "Jan 2001", "Mar 2000", 
 "Mar 2001"), class = "factor"), value = c(84737476, 39450334, 
 48384973, 12345678, 49595340, 4534353, 43353325, 84333535, 35343232, 
 4334353, 3434353, 5355322, 5223345, 4523535, 345353, 32235, 423553, 
 233553, 423535, 884455)), .Names = c("month_year", "value"), row.names = c(NA, 
 -20L), class = "data.frame")


 datN <- merge(dat1, dat2, all=T)
 library(data.table)
 DT <- data.table(datN)
 DT[, month:= gsub(" \\d+", "", month_year)][,cor(value, income),by=month]
 #   month         V1
 #1:   Apr -0.7136049
 #2:   Feb -0.7037676
 #3:   Jan -0.8637808
 #4:   Jun         NA
 #5:   Mar -0.6484684
0
votes

Get your data into a data frame with month, value, and income columns. EG:

d = data.frame(month=rep(1:12,5),value=runif(60,10000000,60000000), income=runif(60,5000,40000))

> head(d)
  month    value   income
1     1 58348424 34478.63
2     2 59512513 16179.46
3     3 21844994 20961.56
4     4 25843593 38502.16
5     5 24805863 12397.32
6     6 24200966 24110.27

Then its as simple as using dplyr to group by month and summarize:

> require(dplyr)
> d %.% group_by(month) %.% summarize(cor = cor(value, income))
Source: local data frame [12 x 2]

   month         cor
1      1  0.17774478
2      2 -0.61693145
3      3 -0.05692027
4      4 -0.44966542
5      5 -0.30049386
6      6  0.09447414
7      7  0.67567298
8      8  0.14363810
9      9 -0.71899361
10    10  0.20807679
11    11 -0.42560100
12    12  0.23584150

Getting the month number from a date string is covered in many other places... but here I'd use the lubridate package. For the month/year strings in your second data set, for example:

require(lubridate)
month(dmy(paste("01",dat2$month_year)))

returns the month number. Note the trick of sticking a "01" on the start to make it a valid date.