The labels on rows 1 and 2, columns 1 and 2 was deleted and turned into numbers 1, 2 and so on.

This is the database – https://kidb.adb.org/mrio#:~:text=ADB%20MRIO%20at%20Constant%202010%20Prices%20(2021)

The file has multiple headers: along rows 1 are countries, and columns 1 countries; along rows 2 are sectors and columns 2 sectors.

I was hoping to aggregate the values on these:

Aggregate the countries along with their sectors (both rows and columns) as: BRA, CAN, MEX, and ROW as “ROW”; AUS, TAP, KAZ, SRI, PAK, FIJ, BRU, BHU, KGZ, MLD as “ROA”; then, retained these countries as is, PRC, JPN, KOR, BAN, CAM, HKG, INO, IND, LAO, MAL, MON, NEP, PHI, SIN, THA, VIE, US, GER; and then the rest of the countries as “EU”.

Then aggregate all the sectors (both rows and columns) as “OTH”, except for C4, C12, C13, C14 and C15 which are to be retained.

Then there are also F1, F2, F3. F4. F5 for each countries under rows 2. I want to aggregate the countries (both rows and columns) as BRA, CAN, MEX, and ROW as “ROW”; AUS, TAP, KAZ, SRI, PAK, FIJ, BRU, BHU, KGZ, MLD as “ROA”; then, retained these countries as is, PRC, JPN, KOR, BAN, CAM, HKG, INO, IND, LAO, MAL, MON, NEP, PHI, SIN, THA, VIE, US, GER; and then the rest of the countries as “EU”. Keep the tagging of F1, F2, F3, F4 and F5.

I tried these codes:

import numpy as np
import scipy as sp
import scipy.linalg as linalg
import pandas as pd

MRIO_org = pd.read_excel( 'ADB MRIO 2019.xlsx' )
print( MRIO_org )

MRIO_org1 = pd.read_excel('ADB MRIO 2019.xlsx', header=6,
usecols=range(3,2525))
MRIO_org2 = MRIO_org1.set_index('Unnamed: 3')
print(MRIO_org2)

Z_org = MRIO_org2.iloc[0 : 2205, 0 : 2205]
V_org = MRIO_org2.iloc[2205 : 2212, 0 : 2205]
x_org = MRIO_org2.iloc[2212 , 0 : 2205]
F_org = MRIO_org2.iloc[0 : 2205, 2205 : 2520]

col=    [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63]

row=[17,21,21,21,22,22,21,1,21,21,20,21,21,21,21,21,21,21,21,21,7,8,21,21,2,26,21,21,21,22,21,21,21,21,21,21,21,21,21,21,21,17,18,4,10,13,15,16,17,11,17,17,7,9,17,17,17,5,17,12,14,6,22]

h=35
m=len(row) # number of old counties
n=int(max(col)) # number of new countries
agg=np.zeros((m*h , n*h))
unit=np.identity(h)
for i in range(m):
agg[h*(col[i]-1) : h * col[i], h*(row[i]-1) : h*row[i]] = unit

g=5
agg2=np.zeros((m*g, n*g))
unit2=np.identity(g)
for i in range(m):
agg2[g*(col[i]-1) : g * col[i], g*(row[i]-1) : g*row[i]] = unit2

Z_new =agg @ Z_org @ agg.T
F_new =agg @ F_org @ agg2.T
x_new =x_org @ agg.T
V_new =V_org @ agg.T

MRIO_new=np.zeros((2213,2521))
MRIO_new[0 : 2205, 0 : 2205] = Z_new
MRIO_new[2205 : 2212, 0 : 2205] = V_new
MRIO_new[2212 , 0 : 2205] = x_new.T
MRIO_new[0 : 2205, 2205 : 2520] = F_new
MRIO_new[0 : 2205, 2520] = x_new

df=pd.DataFrame(MRIO_new)

MRIO_new=df.to_excel('MRIO2019_new.xlsx')

`