My data frame consists of individuals and the city they live at a point in time. I would like to generate one origin-destination matrix for each year, which records the number of moves from one city to another. I would like to know:

1. How can I generate the origin-destination tables for each year in my dataset automatically?
2. How can I generate all tables in the same 5x5 format, 5 being the number of cities in my example?
3. Is there a more efficient code than what I propose below? I intend to run it on a very large dataset.

Consider the following example:

```
#An example dataframe
id=sample(1:5,50,T)
year=sample(2005:2010,50,T)
city=sample(paste(rep("City",5),1:5,sep=""),50,T)
df=as.data.frame(cbind(id,year,city),stringsAsFactors=F)
df$year=as.numeric(df$year)
df=df[order(df$id,df$year),]
rm(id,year,city)

```

My best try

```
#Creating variables
for(i in 1:length(df$id)){
  df$origin[i]=df$city[i]
  df$destination[i]=df$city[i+1]
  df$move[i]=ifelse(df$orig[i]!=df$dest[i] & df$id[i]==df$id[i+1],1,0) #Checking whether a move has taken place and whether its the same person
  df$year_move[i]=ceiling((df$year[i]+df$year[i+1])/2) #I consider that the person has moved exactly between the two dates at which its location was recorded
}
df=df[df$move!=0,c("origin","destination","year_move")]    

```

Creating an origin-destination table for 2007

```
yr07=df[df$year_move==2007,]
table(yr07$origin,yr07$destination)

```

Result

```
        City1 City2 City3 City5
  City1     0     0     1     2
  City2     2     0     0     0
  City5     1     1     0     0

```