CODEWITHSUNDEEP
rindexingdataframe
Mike
Mike

Reputation: 25

How do I use a column from a dataframe in R to index another dataframe?

I have dataframe StateList with 2 columns STATE and Month. I have another data frame StateTemp with the average temp of each state for each month in the US. I am trying to create third column StateList$Temp which will get the temperature from StateTemp based upon the values of StateList$State and StateList$Month indexed into StateTemp. Please see below for reference. Any help is greatly appreciated.

 head(StateList)
   STATE Month
1     FL   Jan
3     MD   Jan
4     MD   Jan
5     WI   Jan
6     UT   Jan
12    NY   Jan

Second object:

head(StateTemp)
     Jan   Feb   Mar   Apr   May   Jun   Jul   Aug   Sep   Oct   Nov   Dec
AL 44.29 48.04 55.47 61.99 69.89 76.75 79.87 79.04 73.88 63.08 54.10 46.85
AZ 42.27 46.24 51.03 57.63 66.01 75.51 80.19 78.50 72.52 61.61 49.64 42.51
AR 38.48 43.76 51.96 60.36 68.62 76.40 80.57 79.26 72.26 61.47 50.32 41.59
CA 45.14 48.51 51.76 56.50 63.11 70.18 75.32 74.62 69.97 61.56 51.17 44.98
CO 23.71 28.34 35.57 43.06 52.50 62.15 67.60 65.75 57.72 46.64 33.51 25.20
CT 25.96 28.43 36.94 47.07 57.77 66.29 71.52 69.77 61.68 50.60 41.43 31.13

Upvotes: 1

Views: 869

Answers (4)

Mike
Mike

Reputation: 25

Thank you everyone for your responses. BondedDust that was awesome. Vaetchen your solution is great too. After I posted I managed to get some code with a for loop working as below. BondedDust's solution is much more elegant than mine. I need to get better with the [ function. hrbrmstr I shuld have expressed it more clearly I was not reshaping StateTemp but adding a third column to a two column dataframe StateList with 150K rows. The StateTemp is basically a lookup table to populate it. As usual there seems to be a over hundred ways to skin a cat in R.

`for (i in 1:nrow(StateList)) { StateList$Temp[i] <-StateTemp[StateList$STATE[i],StateList$Month[i]] }'

Upvotes: 0

vaettchen
vaettchen

Reputation: 7659

If you like a more "traditional" approach:

# state list that fits to the temparature data
StateList <- data.frame( STATE = c( "AL", "CT", "CA", "AZ", "CO", "AR" ),
                   Month = c( "Jan", "Feb", "Mar", "Jan", "Jan", "Feb" ), 
                   stringsAsFactors = FALSE )

# create column for temperature values
StateList$Temp <- 0

# fill it row by row
for( i in 1 : length( StateList$STATE ) )
{
  s <- StateList[ i, 1 ]     # get state name
  m <- StateList[ i, 2 ]     # get month name
  # find in matrix:
  StateList$Temp[ i ] <- StateTemp[ rownames( StateTemp ) == s, 
                                    colnames( StateTemp ) == m ]
}

# I guess this is what you want to see:
StateList
  STATE Month  Temp
1    AL   Jan 44.29
2    CT   Feb 28.43
3    CA   Mar 51.76
4    AZ   Jan 42.27
5    CO   Jan 23.71
6    AR   Feb 43.76

Upvotes: 0

IRTFM
IRTFM

Reputation: 263481

Try this. It uses the ability of a column oriented matrix to index a dimensione object using such an object as a single argument to the "[" function:

> StateList$Temp <- StateTemp[ with( StateList, cbind( STATE, Month) ) ]
> StateList
   STATE Month  Temp
1     FL   Jan 44.29
3     MD   Jan 42.27
4     MD   Jan 42.27
5     WI   Jan 23.71
6     UT   Jan 45.14
12    NY   Jan 38.48

Upvotes: 4

hrbrmstr
hrbrmstr

Reputation: 78832

You can just reshape your StateTemp to get what you want (in this example using dplyr & tidyr):

StateTemp <- read.table(text="     Jan   Feb   Mar   Apr   May   Jun   Jul   Aug   Sep   Oct   Nov   Dec
AL 44.29 48.04 55.47 61.99 69.89 76.75 79.87 79.04 73.88 63.08 54.10 46.85
AZ 42.27 46.24 51.03 57.63 66.01 75.51 80.19 78.50 72.52 61.61 49.64 42.51
AR 38.48 43.76 51.96 60.36 68.62 76.40 80.57 79.26 72.26 61.47 50.32 41.59
CA 45.14 48.51 51.76 56.50 63.11 70.18 75.32 74.62 69.97 61.56 51.17 44.98
CO 23.71 28.34 35.57 43.06 52.50 62.15 67.60 65.75 57.72 46.64 33.51 25.20
CT 25.96 28.43 36.94 47.07 57.77 66.29 71.52 69.77 61.68 50.60 41.43 31.13", header=TRUE)

library(tidyr)
library(dplyr)

StateTemp %>%
  add_rownames(var="State") %>%
  gather(Month, Temp, -State)

## Source: local data frame [72 x 3]
## 
##    State Month  Temp
## 1     AL   Jan 44.29
## 2     AZ   Jan 42.27
## 3     AR   Jan 38.48
## 4     CA   Jan 45.14
## 5     CO   Jan 23.71
## 6     CT   Jan 25.96
## 7     AL   Feb 48.04
## 8     AZ   Feb 46.24
## 9     AR   Feb 43.76
## 10    CA   Feb 48.51
## ..   ...   ...   ...

Upvotes: 1

Related Questions