I have a static structure (dummy table):
| iso | pop | gdp | priority01 | priority02 | priority03 | priority04 |
|---|---|---|---|---|---|---|
which should be populated with the data from 2 different tables.
pop & GDP:
| iso | pop | gdp |
|---|---|---|
| AFG | 8000000 | 20000 |
| BGD | 7000000 | 30000 |
| ZAF | 6000000 | 40000 |
Priorities:
| iso | priority |
|---|---|
| AFG | pr_abc |
| AFG | pr_cba |
| AFG | pr_acb |
| AFG | pf_bca |
| BGD | pr_xyz |
| BGD | pr_zyx |
The final table should be like that:
| iso | pop | gdp | priority01 | priority02 | priority03 | priority04 |
|---|---|---|---|---|---|---|
| AFG | 8000000 | 20000 | pr_abc | pr_cba | pr_acb | pf_bca |
| BGD | 7000000 | 30000 | pr_xyz | pr_zyx | NULL | NULL |
| ZAF | 6000000 | 40000 | NULL | NULL | NULL | NULL |
There are no issues with first table to join. But I don't' know how to deal with second table. I've tried to transpose it but I have no solution for missing values (like BGD and ZAF):
table_2 = table_2.reset_index().groupby(['iso', 'priority'])['priority'].aggregate('first').unstack().reset_index(drop=False)
table_2 = table_2.set_axis(['iso', 'priority01', 'priority02', 'priority03',
'priority04'], axis=1, inplace=False)
Above-mentioned solution can work with consistent data where each ISO has 4 priorities. But again. how to deal with missing values...
Maybe there is another way to solve this problem...Thanks
