Блог

у меня есть следующий набор данных, который я извлек из фрейма данных pandas

 {'Batch': {0: 'Nos705', 1: 'Nos706', 2: 'Nos707', 3: 'Nos708', 4: 'Nos709', 5: 'Nos710', 6: 'Nos711', 7: 'Nos713', 8: 'Nos714', 9: 'Nos715'}, 
'Message': {0: 'ACBB', 1: 'ACBL', 2: 'ACBL', 3: 'ACBC', 4: 'ACBC', 5: 'ACBC', 6: 'ACBL', 7: 'ACBL', 8: 'ACBL', 9: 'ACBL'}, 
'DCC': {0: 284, 1: 21, 2: 43, 3: 19, 4: 0, 5: 0, 6: 19, 7: 27, 8: 27, 9: 19}, 
'DCB': {0: 299, 1: 22, 2: 24, 3: 28, 4: 167, 5: 167, 6: 20, 7: 27, 8: 27, 9: 28}, 
'ISC': {0: 'Car010030', 1: 'Car010054', 2: 'Car010047', 3: 'Car010182', 4: 'Car010004', 5: 'Car010004', 6: 'Car010182', 7: 'Car010182', 8: 'Car010182', 9: 'Car010182'},
'ISB': {0: 'Car010010', 1: None, 2: None, 3: None, 4: None, 5: None, 6: None, 7: None, 8: None, 9: None}, 
'VSC': {0: 25, 1: 25, 2: 25, 3: 25, 4: 25, 5: 25, 6: 25, 7: 25, 8: 25, 9: 25},
'VSB': {0: 27.0, 1: nan, 2: nan, 3: nan, 4: nan, 5: nan, 6: nan, 7: nan, 8: nan, 9: nan},
'PGC': {0: 2.78, 1: 2.79, 2: 2.08, 3: 2.08, 4: 2.08, 5: 2.08, 6: 2.71, 7: 1.73, 8: 1.73, 9: 1.73}, 
'PGB': {0: 2.95, 1: nan, 2: nan, 3: nan, 4: nan, 5: nan, 6: nan, 7: nan, 8: nan, 9: nan}, 
'PHB': {0: 2.96, 1: nan, 2: nan, 3: nan, 4: nan, 5: nan, 6: nan, 7: nan, 8: nan, 9: nan}, 
'PHC': {0: 2.94, 1: 2.94, 2: 1.63, 3: 1.63, 4: 1.63, 5: 1.63, 6: 2.06, 7: 1.75, 8: 1.75, 9: 1.75}, 
'BPC': {0: 3.17, 1: 3.17, 2: 3.17, 3: 3.17, 4: 3.17, 5: 3.17, 6: 3.17, 7: 3.17, 8: 3.17, 9: 3.17}, 
'BPB': {0: None, 1: None, 2: None, 3: None, 4: None, 5: None, 6: None, 7: None, 8: None, 9: None}}
 

Я хочу создать фрейм данных, который складывается для связанных столбцов
, например: все значения DCC и DCB должны отображаться в одном столбце, один под другим. Аналогично для ISC и ISB, VSC и VSB, PGC и PCB, PHC и PHB, BPC и BPB

Пакет остается здесь первичным ключом. Как это сделать, в Python

Сначала преобразуйте столбцы для повторения в MultiIndex :

 df1 = df.set_index(['Batch','Message'])
 

Затем создайте MultiIndex in columns по всем значениям без последних и последних значений имен столбцов в MultiIndex.from_arrays и измените по DataFrame.stack , для правильного добавления порядка DataFrame.sort_values :

 df1.columns = pd.MultiIndex.from_arrays([df1.columns.str[:-1], df1.columns.str[-1]],
                                        names=[None, 'types'])
df1 = (df1.stack(dropna=False)
          .reset_index()
          .sort_values(['Batch','Message','types'], 
                       ascending=[True, True, False], 
                       ignore_index=True))
print (df1)
     Batch Message types    BP   DC         IS    PG    PH    VS
0   Nos705    ACBB     C  3.17  284  Car010030  2.78  2.94  25.0
1   Nos705    ACBB     B  None  299  Car010010  2.95  2.96  27.0
2   Nos706    ACBL     C  3.17   21  Car010054  2.79  2.94  25.0
3   Nos706    ACBL     B  None   22       None   NaN   NaN   NaN
4   Nos707    ACBL     C  3.17   43  Car010047  2.08  1.63  25.0
5   Nos707    ACBL     B  None   24       None   NaN   NaN   NaN
6   Nos708    ACBC     C  3.17   19  Car010182  2.08  1.63  25.0
7   Nos708    ACBC     B  None   28       None   NaN   NaN   NaN
8   Nos709    ACBC     C  3.17    0  Car010004  2.08  1.63  25.0
9   Nos709    ACBC     B  None  167       None   NaN   NaN   NaN
10  Nos710    ACBC     C  3.17    0  Car010004  2.08  1.63  25.0
11  Nos710    ACBC     B  None  167       None   NaN   NaN   NaN
12  Nos711    ACBL     C  3.17   19  Car010182  2.71  2.06  25.0
13  Nos711    ACBL     B  None   20       None   NaN   NaN   NaN
14  Nos713    ACBL     C  3.17   27  Car010182  1.73  1.75  25.0
15  Nos713    ACBL     B  None   27       None   NaN   NaN   NaN
16  Nos714    ACBL     C  3.17   27  Car010182  1.73  1.75  25.0
17  Nos714    ACBL     B  None   27       None   NaN   NaN   NaN
18  Nos715    ACBL     C  3.17   19  Car010182  1.73  1.75  25.0
19  Nos715    ACBL     B  None   28       None   NaN   NaN   NaN
 

Последний, если необходимо, удаляет types столбец:

 df1 = df1.drop('types', axis=1)