Obligatory "I am not very experienced using Pandas, and I could use some help to overcome a hurdle on my project"-statement. A large matrix (LM) will need to be aggregated to smaller matrices to be used in different models, which in the past has been done using outdated software. Luckily we have Python's Pandas to the rescue. Any input is received with great thanks!
For each model I want to build multiplication matrices (MM) (consisting of zeros and ones) to reduce the size of the LM.
Currently I have access to this Dataframe, where column 0-5 tells us which LM-variables are to be aggregated into which model-variable (LHS).
Modelvar 0 1 2 3 4 5
0097 None None None None None None
004 004 006 0014 0099 None None
... ... ... ... ... ... ...
874 8714 8894 None None None None
904 904 None None None None None
I also have access to a dataframe, consisting of a single vector, showing the order of LMs variables (along one of its axis'):
0 004
1 005
2 006
3 008
.. ...
640 8895
I should then in theory be able to create a MM looking like this:
004 005 006 008 0099 ... 904 ... 8894 8895
0097 0 0 0 0 0 ... 0 ... 0 0
004 1 0 1 0 1 ... 0 ... 0 0
... ... ... ... ... ... ... ... ... ... ...
874 0 0 0 0 0 ... 0 ... 1 0
904 0 0 0 0 0 ... 1 ... 0 0
Finding a solution has been troublesome, as a concise wording of the problem seem to elude me. I believe merge(), concat() or join() contain what I am looking for, but I seem to never get it just right. I therefore hope that someone that dabble with Pandas on the regular can point me in the right direction.
A brute force attempt has been to first create a zero-matrix with the correct labels, but then I find it overly difficult to "match" the content of the first Dataframe-table with the LM-variable-labels in order to insert the 1s. This also seem inefficient, and I would much rather create the MM in one go.