Convert combinations of row+column as column headers, with a fixed number of columns

Question

Assume a dataframe like this:

Machine    Time      Part    PowerA    PowerB
1         20:30       1        0.1      0.4
1         20:30       2        0.9      0.7
1         20:31       1        0.3      0.1
1         20:31       2        0.2      0.3
2         20:30       1        0.2      0.5
2         20:31       1        0.8      0.4

Every machine can have up to 8 parts. The objective is to do something like asked in this question, but always force the creation of 8 column parts for each of the power columns, even if the dataframe only contains machines with less than 8 parts. I am currently using one of the solutions that I was provided, but it doesn't meet this new requirement.

s = df.pivot_table(index= ['Machine','Time'], 
                   columns = df.Part.astype(str).radd('Part'),
                   values=['PowerA','PowerB'],
                   fill_value=-1).swaplevel(1,0, axis=1).sort_index(level=0, axis=1)

s.columns = s.columns.map('_'.join)
s.reset_index(inplace=True)
s
Out[751]: 
   Machine   Time  Part1_PowerA  Part1_PowerB  Part2_PowerA  Part2_PowerB
0        1  20:30           0.1           0.4           0.9           0.7
1        1  20:31           0.3           0.1           0.2           0.3
2        2  20:30           0.2           0.5          -1.0          -1.0
3        2  20:31           0.8           0.4          -1.0          -1.0

The output I am striving for now would be:

   Machine   Time  Part1_PowerA  Part1_PowerB  Part2_PowerA  Part2_PowerB  Part3_PowerA ->
0        1  20:30           0.1           0.4           0.9           0.7      -1.0
1        1  20:31           0.3           0.1           0.2           0.3      -1.0
2        2  20:30           0.2           0.5          -1.0          -1.0      -1.0
3        2  20:31           0.8           0.4          -1.0          -1.0      -1.0

->       Part3_PowerB      ...     Part8_PowerA   Part8_PowerB
0           -1.0          -1.0         -1.0          -1.0
1           -1.0          -1.0         -1.0          -1.0
2           -1.0          -1.0         -1.0          -1.0
3           -1.0          -1.0         -1.0          -1.0

What I did, and I believe is an awful solution, was to append a 'dummy' rows to the initial data frame containing all the parts up to 8, so it would then always results in a data frame with 16 columns, as desired. Then I would remove those. Is there a better way?

score 0 · Accepted Answer · answered Jun 11 '22 at 17:29

After your code, you could add this line:

s[
    [f"Part{i}_Power{j}" for i in range(int(s.shape[1] / 2) + 1, 9) for j in ("A", "B")]
] = -1

print(s)
# Output
   Part1_PowerA  Part1_PowerB  Part2_PowerA  Part2_PowerB  Part3_PowerA  \
0           0.1           0.4           0.9           0.7            -1   
1           0.3           0.1           0.2           0.3            -1   
2           0.2           0.5          -1.0          -1.0            -1   
3           0.8           0.4          -1.0          -1.0            -1   

   Part3_PowerB  Part4_PowerA  Part4_PowerB  Part5_PowerA  Part5_PowerB  \
0            -1            -1            -1            -1            -1   
1            -1            -1            -1            -1            -1   
2            -1            -1            -1            -1            -1   
3            -1            -1            -1            -1            -1   

   Part6_PowerA  Part6_PowerB  Part7_PowerA  Part7_PowerB  Part8_PowerA  \
0            -1            -1            -1            -1            -1   
1            -1            -1            -1            -1            -1   
2            -1            -1            -1            -1            -1   
3            -1            -1            -1            -1            -1   

   Part8_PowerB  
0            -1  
1            -1  
2            -1  
3            -1

Convert combinations of row+column as column headers, with a fixed number of columns

1 Answers1