0

In my example I am working with two dataframes. One dataframe is empty with 12 columns numbered 1-12. The other has the data I wish to map, as well as columns that point to which column the mapped data should go to (images attached).

I need to use the matrix of positions to transpose the dataframe values into the correct columns of the resulting dataframe. Image below show what I'm explaining.

Dataframes:

image

Vitalizzare
  • 4,496
  • 7
  • 13
  • 32
Ryan Leonard
  • 1
  • 2
    Hi there, welcome to SO. To increase the chances to get an answer, please edit your question to include some actual test data, as text, not as image. The best practices to post pandas questions are outlined [here](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples). Please also include what you tried to solve the problem. – fsimonjetz Aug 26 '22 at 22:55
  • Please provide enough code so others can better understand or reproduce the problem. – Community Aug 26 '22 at 23:30

1 Answers1

0

From what I can see, you have not 2 but 3 tables: source (left half of df2), destination (df1) and mapping (right half of df2). Let's create them:

index=['ABC1','ABC2']
columns1 = range(1, 13)
columns2 = [f'Q{i}' for i in range(1,7)]

data = [
    [10000,5000,'cat','doc','fish',''],
    ['foo',25000,'bar',60000,'moo','']
]

mapping = [
    [8,9,10,11,12,pd.NA],
    [1,2,3,4,5,6]
]

destination = pd.DataFrame(pd.NA, index, columns1)
source = pd.DataFrame(data, index, columns2)
mapping = pd.DataFrame(mapping, index, columns2)

In general, iteration might be the answer:

for i, c in mapping.stack().index:
    if pd.notna(mapping.loc[i,c]):
        destination.loc[i, mapping.loc[i,c]] = source.loc[i, c]

In case if mapping has no nan values, we could use numpy.put_along_axis. But before that, destination indices stored in mapping should be transformed to numpy indices. In this case, the indices should only be subtracted by 1. Also, I replaced nan with 6 to move the space from the source upper-right corner to the 6th position in the destination table:

indices = mapping.fillna(6).to_numpy() - 1
np.put_along_axis(
    destination.to_numpy(copy=False), 
    indices, 
    source, 
    axis=1
)

Output of print(destination):

        1      2     3      4     5  6     7      8     9     10    11    12
ABC1  <NA>   <NA>  <NA>   <NA>  <NA>     <NA>  10000  5000   cat   doc  fish
ABC2   foo  25000   bar  60000   moo     <NA>   <NA>  <NA>  <NA>  <NA>  <NA>
Vitalizzare
  • 4,496
  • 7
  • 13
  • 32