python - 如何折叠/旋转多个 Pandas 列

Question

在下面的数据集中，

# DataFrame using arrays.
import pandas as pd
import numpy as np

 
# create dataset
data = {'Gender':['287F', '287F', '287F', '287F','287F', '287F', '189M', '189M','189M', '189M', 
                  '189M', '189F','287M', '189F', '287M', '287M','287M','189F', '189F', '287M'],
        'code_num':[1001,1001,1002,1002,1003,1003,1004,1004,1005,1005,
                    1006,1006,1007,1007,1008,1008,1009,1009,1010,1010],
        'Date':['10-22-1923','10-22-1923','10-22-1923','10-22-1923','10-22-1923','10-22-1923','10-22-1923','10-22-1923','10-22-1923','10-22-1923','10-22-1923','10-22-1923','10-22-1923','10-22-1923','10-22-1923','10-22-1923','10-22-1923','10-22-1923','10-22-1923','10-22-1923'],
        'Location':['PHX','PHX','PHX','PHX','PHX','PHX','PHX','PHX','PHX','PHX',
                    'MIA','MIA','MIA','MIA','MIA','MIA','MIA','MIA','MIA','MIA'],
        'Age':['18yr','18yr','18yr','18yr','18yr','18yr','18yr','18yr','18yr','18yr','18yr','18yr','18yr','18yr','18yr','18yr','18yr','18yr','18yr','18yr'],
        'Group':['F1', 'D1', 'F2', 'D2','F1', 'D1', 'F2', 'D2','F1', 'D1', 'F3', 'D3','F2', 'D2', 'F4', 'D4','F3','D3', 'F4', 'D4'],
        'Dog_10_UID': ['T-X', 'T-X', 'G-A', 'G-A','T-X', 'T-X', 'G-A', 'G-A','T-X', 'T-X', 'C-A', 'C-A','G-A', 'G-A', 'F-L', 'F-L','C-A','C-A', 'F-L', 'F-L'],
        'Dog_10_name': ['Tex', 'Tex', 'Gina', 'Gina','Tex', 'Tex', 'Gina', 'Gina','Tex', 'Tex', 'Carla', 'Carla','Gina', 'Gina', 'Flora', 'Flora','Carla','Carla', 'Flora', 'Flora'],
        'Dog_10_txt':['>11','51','61','>11','>91','61','51','>11','>91','>11','61','>11','>71','51','>11','61','>11','>71','>91','51'],
         'Dog_10_index':[11,51,61,11,91,61,51,11,91,11,61,11,71,51,11,61,11,71,91,51],
         'Dog_20_UID': ['T-X', 'T-X', 'G-A', 'G-A','T-X', 'T-X', 'G-A', 'G-A','T-X', 'T-X', 'C-A', 'C-A','G-A', 'G-A', 'F-L', 'F-L','C-A','C-A', 'F-L', 'F-L'],
        'Dog_20_name': ['Tex', 'Tex', 'Gina', 'Gina','Tex', 'Tex', 'Gina', 'Gina','Tex', 'Tex', 'Carla', 'Carla','Gina', 'Gina', 'Flora', 'Flora','Carla','Carla', 'Flora', 'Flora'],
        'Dog_20_txt':['>12','52','62','>12','>92','62','52','12','>92','>12','62','>12','>72','52','>12','62','>12','>72','>92','52'],
         'Dog_20_index':[12,52,62,12,92,62,52,12,92,12,62,12,72,52,12,62,12,72,92,52]
       }

data = pd.DataFrame(data)
data

我想折叠(或旋转)以下相应的列

Dog_10_UID 和 Dog_20_UID 产生单列 Dog_UID

Dog_10_name & Dog_20_name 产生单列 Dog_name

Dog_10_txt 和 Dog_20_txt 产生单列 Dog_txt

Dog_10_index 和 Dog_20_index 产生单列 Dog_index

折叠/透视后，最终数据框应具有以下列名称

性别, code_num, 日期, 地点, 年龄, 组, Dog_UID,Dog_name,Dog_txt, Dog_index

我的尝试

# 'Gender','code_num', 'Date', 'Location', 'Age', 'Group' should remain constant while collapsing/pivoting Columns starting with 'Dog_'

keys = [x for x in data if x.startswith('Dog_')]

df  = data.melt(id_vars=['Gender','code_num', 'Date', 'Location', 'Age', 'Group'], var_name=['Dog_UID','Dog_name', 'Dog_txt', 'Dog_index'], 
          value_name='keys')

我对其他方法持开放态度，请分享您的完整代码。谢谢

Answer 1

第一步是 DataFrame.set_index , 通过所有未被 split 处理的列创建 MultiIndex 并通过 DataFrame.stack reshape

df = data.set_index(['Gender','code_num', 'Date', 'Location', 'Age', 'Group'])
df.columns = df.columns.str.split('_', expand=True)
df = df.stack(1)
df.columns = df.columns.map(lambda x: f'{x[0]}_{x[1]}')
cols = ['Dog_UID', 'Dog_name', 'Dog_txt', 'Dog_index']
df = df.reset_index(level=-1, drop=True)[cols].reset_index()
print (df.head())
  Gender  code_num        Date Location   Age Group Dog_UID Dog_name Dog_txt  \
0   287F      1001  10-22-1923      PHX  18yr    F1     T-X      Tex     >11   
1   287F      1001  10-22-1923      PHX  18yr    F1     T-X      Tex     >12   
2   287F      1001  10-22-1923      PHX  18yr    D1     T-X      Tex      51   
3   287F      1001  10-22-1923      PHX  18yr    D1     T-X      Tex      52   
4   287F      1002  10-22-1923      PHX  18yr    F2     G-A     Gina      61   

   Dog_index  
0         11  
1         12  
2         51  
3         52  
4         61