How can I get a heatmap of "average" correlation by group?

[John Williamss]

Suppose we have a data in the long form where each observation is the crime rate in a city in a year. What I want is a heatmap where each cell is the average correlation of crimes of cities in the two states. For example, if Texas has 4 cities and California 5 then it will average of 20 numbers.
I know I can achieve the heatmap of the average crime rates of different states this way:

collapse (mean) crime , by(year state)
reshape wide crime , i(y) j(region) string
pwcorr crimeAL-crimeWY
return list
matrix corrmatrix = r(C)
heatplot corrmatrix , lower nodiag title("Correlation Matrix Of Average Crime Rates")  color(hcl diverging, intensity(.7))

But what I want is different. I want to average over all pairwise city-wise correlations grouped by states.

[Nick Cox]

Let's back up here. Take 4 cities in CA and 5 in TX as postulated. How do you measure correlation in that case?

[John Williamss]

Let's back up here. Take 4 cities in CA and 5 in TX as postulated. How do you measure correlation in that case?

For each city in CA and each city in TX, I measure the simple correlation (These are yearly series). Hence I have a number. I do this for all combinations of TX, CA cities which are 4*5=20. I then average over these 20 numbers and that's my desired number.

[Nick Cox]

Thanks for the detail. I think I see.

Correlations are not quantities I feel happy about averaging. Whenever this is discussed, not often in my reading, people seem to lean towards averaging on the atanh scale and then converting back.

There are so many ways that an overall correlation could be defined, meaning that the prospect is clouded.