Odtnhj

I have an unbalanced panel data set that gives me information on how much banks lend in different areas. Geography id and bank id are numeric variables that were created using a Stata command like egen id=group(var).

The geography id goes from 1 to n and the bank id goes from 1 to k. To give you a more concrete idea of how my data look:

Geography ID (gid) | Bank ID (bid) | lending
-----------------------------------------------
1 | 1 | 25
1 | 2 | 32
1 | 4 | 83
----------------------------------------------
2 | 1 | 76
2 | 3 | 22
---------------------------------------------
3 | 2 | 42
3 | 3 | 12
3 | 5 | 22
--------------------------------------------

My final goal is to create a dataframe that has all the pairwise combinations of the geographical areas such that:

 1 2 3 ......... n
-------------------------------
1|(1,1) (1,2) (1,3)......(1,n)
2|(2,1) (2,2) (2,3)......(2,n)
.| . . .
n|(n,1) . ......(n,n)

Such that entry (i,j) gives me:

(i,j)=(Lending from Banks Operating in Area i and j)/(Total Lending in Area i and j)

So for instance given the above data

(1,1)=1 (1,2)=(25+76)/(25+32+83+76+22) (1,3)=(32+42)/(25+32+83+42+12+22)

I have a feeling that as a first step I should use levelsof and bysort in a loop but I am unsure on how exactly to tackle the problem.

Even if you can't provide an exact solution I would be extremely grateful receiving any help or suggestion. Although I prefer Stata I also have some knowledge of Matlab/R, so if you think it would be more suitable for that problem I am open to suggestions.

Here's an R method:

x <- data.frame(
 geoid = c(1,1,1, 2,2, 3,3,3),
 bankid = c(1,2,4, 1,3, 2,3,5),
 lending = c(25,32,83, 76,22, 42,12,22)
)

myfunc <- function(x, i, j) 
 geos <- x$geoid %in% c(i, j)
 banks <- with(x, intersect(bankid[geoid == i], bankid[geoid == j]))
 with(x, sum(lending[geos & bankid %in% banks]) / sum(lending[geos]))


outer(unique(x$geoid), unique(x$geoid),
 function(i,j) mapply(myfunc, list(x), i, j))
# [,1] [,2] [,3]
# [1,] 1.0000000 0.4243697 0.3425926
# [2,] 0.4243697 1.0000000 0.1954023
# [3,] 0.3425926 0.1954023 1.0000000

It's not the most efficient, but it's a start. It's difficult (I think) to do this truly vectorized, since each subset requires intersections, though I'm sure this could be optimized to not require re-calculating intersect(bankid...) twice for each equivalent pair (if that's a performance factor).

Edit: slightly more efficient process that does not re-calculate equivalent pairs of geoid:

Split the data by geo:

geox <- split(x, x$geoid)

myfunc <- function(i, j) 
 if (i >= j) return(NA)
 banks <- intersect(geox[[i]]$bankid, geox[[j]]$bankid)
 sum(with(geox[[i]], lending[ bankid %in% banks ]),
 with(geox[[j]], lending[ bankid %in% banks ])) /
 sum(geox[[i]]$lending, geox[[j]]$lending)


o <- outer(seq_along(geox), seq_along(geox),
 function(i,j) mapply(myfunc, i, j))
o
# [,1] [,2] [,3]
# [1,] NA 0.4243697 0.3425926
# [2,] NA NA 0.1954023
# [3,] NA NA NA

(Just to prove we only calculated the minimum set.) Now, flip the upper triangle's data to lower triangle:

o[which(lower.tri(o),TRUE)] <- o[which(upper.tri(o),TRUE)]
o
# [,1] [,2] [,3]
# [1,] NA 0.4243697 0.3425926
# [2,] 0.4243697 NA 0.1954023
# [3,] 0.3425926 0.1954023 NA

And assign the known-value of 1 to the diagonal:

diag(o) <- 1