I have N numbers, call it 3 for now: A1, A2, A3. I'd like to generate the following dataframe in Pandas:
Category | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
---|---|---|---|---|---|---|---|
1 | A1 | A1+A2 | A1+A2+A3 | A2+A3 | A3 | 0 | 0 |
2 | 0 | A2 | A2+A3 | A2+A3+A1 | A3+A1 | A1 | 0 |
3 | 0 | 0 | A3 | A3+A1 | A3+A1+A2 | A1+A2 | A2 |
I am completely at a loss as to how to generate the logic for this.
My only thoughts are: take the 1st column, add A2 to all values, then add A3. Then add A1, although if it's already there then remove it. Seems thorny though.
I'd like to run this for general N.
For N = 4
Category | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
---|---|---|---|---|---|---|---|---|---|---|
1 | A1 | A1+A2 | A1+A2+A3 | A1+A2+A3+A4 | A2+A3+A4 | A3+A4 | A4 | 0 | 0 | 0 |
2 | 0 | A2 | A2+A3 | A2+A3+A4 | A2+A3+A4+A1 | A3+A4+A1 | A4+A1 | A1 | 0 | 0 |
3 | 0 | 0 | A3 | A3+A4 | A3+A4+A1 | A3+A4+A1+A2 | A4+A1+A2 | A1+A2 | A2 | 0 |
4 | 0 | 0 | 0 | A4 | A4+A1 | A4+A1+A2 | A4+A1+A2+A3 | A1+A2+A3 | A2+A3 | A3 |
The pattern I found in there is as follows:
import numpy as np
N = 3
for S in range(N): # S for "shift"
A = np.zeros((N, 3*N - 2))
M = N + S
M_ = 2*N + S
A[:S+1, S:M] = 1
A[S+1:, M:M_] = 1
so basically for A_n you start from column n, and fill n next rows. For the rest of the rows you fill next n columns. Here's how those matrices look for N = 3:
[[1. 1. 1. 0. 0. 0. 0.]
[0. 0. 0. 1. 1. 1. 0.]
[0. 0. 0. 1. 1. 1. 0.]]
[[0. 1. 1. 1. 0. 0. 0.]
[0. 1. 1. 1. 0. 0. 0.]
[0. 0. 0. 0. 1. 1. 1.]]
[[0. 0. 1. 1. 1. 0. 0.]
[0. 0. 1. 1. 1. 0. 0.]
[0. 0. 1. 1. 1. 0. 0.]]
Then, you can simply multiply those by the numbers you desire and sum them up:
import numpy as np
import pandas as pd
N = 3
As = [1, 2, 3]
result = np.zeros((N, 3*N - 2))
for S in range(N): # S for "shift"
A = np.zeros((N, 3*N - 2))
M = N + S
M_ = 2*N + S
A[:S+1, S:M] = 1
A[S+1:, M:M_] = 1
result += As[S] * A
pd.DataFrame(result)
0 1 2 3 4 5 6
0 1.0 3.0 6.0 5.0 3.0 0.0 0.0
1 0.0 2.0 5.0 6.0 4.0 1.0 0.0
2 0.0 0.0 3.0 4.0 6.0 3.0 2.0