Transpose rows using gropby with multiple values

userGFQq 0
Viewed 52

I have this below DataFrame:

df = pd.DataFrame({'DC':['Alexandre', 'Alexandre', 'Afonso de Sousa', 'Afonso de Sousa','Afonso de Sousa'],
          'PN':['Farmacia Ekolelo', 'Farmacia Havemos De Voltar', 'Farmacia Gloria', 'Farmacia Mambofar','Farmacia Metamorfose'],
          'PC':['C-HO-002815', 'C-HO-005192', 'C-HO-002719', 'C-HO-003030','C-SCC-012430'],
          'KCP':['NA', 'DOMINGAS PAULO', 'HELDER', 'Mambueno','RITA'],
          'MBN':['NA', 'NA', 29295486345, 9.40407E+11,2.92955E+11]})

enter image description here

Trying to convert data into below format.

enter image description here

By grouping DC column needs to transpose other columns as per above format.

python-3.x python
1 Answers

You can group by DC then aggregate with list. From there you can concat the dataframes created from the aggregated lists:

import pandas as pd

df = pd.DataFrame({'DC':['Alexandre', 'Alexandre', 'Afonso de Sousa', 'Afonso de Sousa','Afonso de Sousa'],
          'PN':['Farmacia Ekolelo', 'Farmacia Havemos De Voltar', 'Farmacia Gloria', 'Farmacia Mambofar','Farmacia Metamorfose'],
          'PC':['C-HO-002815', 'C-HO-005192', 'C-HO-002719', 'C-HO-003030','C-SCC-012430'],
          'KCP':['NA', 'DOMINGAS PAULO', 'HELDER', 'Mambueno','RITA'],
          'MBN':['NA', 'NA', 29295486345, 9.40407E+11,2.92955E+11]})

df = df.groupby('DC', as_index=False).agg(list)
#print(df)
df_out = pd.concat([df[['DC']]] + 
                    [
                        pd.DataFrame(l := df[col].to_list(),
                            columns=[f'{col}_{i}' for i in range(1, max(len(s) for s in l) + 1)]
                        ) for col in df.columns[1:]
                    ],
                    axis=1)

Note: the assignment in the comprehension l := df[col].to_list() only works for Python versions >= 3.8.
This will give you:

                DC              PN_1                        PN_2                  PN_3  ... KCP_3        MBN_1           MBN_2         MBN_3
0  Afonso de Sousa   Farmacia Gloria           Farmacia Mambofar  Farmacia Metamorfose  ...  RITA  29295486345  940407000000.0  2.929550e+11  
1        Alexandre  Farmacia Ekolelo  Farmacia Havemos De Voltar                  None  ...  None           NA              NA           NaN

You can then sort the columns with your own function:

def sort_columns(col_lbl):
    col, ind = col_lbl.split('_')
    return (int(ind), df.columns.to_list().index(col))

df_out.columns = ['DC'] + sorted(df_out.columns[1:].to_list(), key=sort_columns)

Output:

                DC              PN_1                        PC_1                 KCP_1  ...  PN_3         PC_3           KCP_3         MBN_3  
0  Afonso de Sousa   Farmacia Gloria           Farmacia Mambofar  Farmacia Metamorfose  ...  RITA  29295486345  940407000000.0  2.929550e+11  
1        Alexandre  Farmacia Ekolelo  Farmacia Havemos De Voltar                  None  ...  None           NA              NA           NaN
Tranbi0
Related