[R] organizing data in a matrix avoiding loop

S Ellison S.Ellison at LGCGroup.com
Fri May 26 15:28:07 CEST 2017

> -----Original Message-----
> From: A M > Lavezzi
> I have data on bilateral trade flows among countries in the following form:
>       iso_o iso_d year FLOW
> 1   ABW   AFG 1985   NA
> 2   ABW   AFG 1986   NA
> 3   ABW   AFG 1987   NA
> 4   ABW   AFG 1988   NA
> 5   ABW   AFG 1989   NA
> 6   ABW   AFG 1990   NA
> I have 215 countries. I would like to create a 215x215 matrix , say M, in which
> element M(i,j) is the total trade between countries i and j between
> 1985 and 2015 (i.e. the sum of annual amounts of trade).
> After collecting the country codes in a variable named "my_iso", I can obtain
> M in a straightforward way using a loop 
> Is there a way to avoid these loops?

Using core R:
#Use aggregate() to aggregate across years:

dataTrade.ag <- aggregate (dataTrade[,'Flow',drop=FALSE], by=dataTrade[, c('iso_o', 'iso_d')], FUN=sum, na.rm=TRUE)

#where na.rm=TRUE (passed to sum()) essentially treats NAs as 0. If you really want NA leave it out or set it to FALSE
#This gives you one row per origin/destination pair that contains the total trade in Flow.
#If the years you want are a subset, subset the data frame first.

#Form an empty matrix with suitable dimnames:
N_iso <- length(my_iso)
dT.m <- matrix(rep(NA, N_iso*N_iso), ncol=N_iso, dimnames=list(my_iso, my_iso))

#Then use matrix indexing by name to populate your matrix with the available flow data
dT.m[as.matrix(dataTrade.ag[1:2]) ] <- dataTrade.ag$Flow	
	#This relies on a default conversion from data frame factors to a character matrix, together
	#with R's facility for matrix indexing by 2-column matrix

dataTrade.ag[1:10, 1:10]

#should have what you seem to want

S Ellison

This email and any attachments are confidential. Any use, copying or
disclosure other than by the intended recipient is unauthorised. If 
you have received this message in error, please notify the sender 
immediately via +44(0)20 8943 7000 or notify postmaster at lgcgroup.com 
and delete this message and any copies from your computer and network. 
LGC Limited. Registered in England 2991879. 
Registered office: Queens Road, Teddington, Middlesex, TW11 0LY, UK

More information about the R-help mailing list