如何将多列转换为行

Question

我在excel中有一个如下的数据集

   A              B       C           D         E           F        G
1                SKU                 Units                Sales
2               11/5/18  12/5/18    11/5/18   12/5/18    11/5/18   12/5/18
3 Description                       total=5   total=9    total=33  total =60
4 Nail Varnish   2345     2345          2       4          $15      $30
5 Eyeliner       2346     2345          3       5          $18      $30

我使用熊猫转换成另一个excel与下面的结构

    Description     date          sku           Units          Sales             
0   Nail Varnish    11/5/18       2345            2            $15 
1    Eye liner      11/5/18       2346            3            $18
2   Nail Varnish    12/5/18       2345            4            $30 
3    Eye liner      12/5/18       2346            5            $30

我以前在pandas中使用过melt函数将一列转换为行（例如sales或Units），我不知道它是否适用于三个多索引列。你知道吗

df = pd.read_excel(filepath_name,index_col=False,usecols="A:G")
df2=pd.melt(df,id_vars=["SKU ","Units","Sales"], var_name ="Date", value_name='?)

但我如何能转换我的所有这些id变量（SKU，单位和销售）在一次去。你知道吗

Answer 1

Idea是用第一列创建索引，用前两行创建多索引，因此可以使用^{}：

df = pd.read_excel(filepath_name,index_col=[0],usecols="A:G", header=[0,1])

print (df.columns)
MultiIndex(levels=[['SKU', 'Sales', 'Units'], ['11/5/18', '12/5/18']],
           codes=[[0, 0, 2, 2, 1, 1], [0, 1, 0, 1, 0, 1]])


df = df.stack().rename_axis(('Description','date')).reset_index()
print (df)
    Description     date   SKU Sales  Units
0  Nail Varnish  11/5/18  2345   $15      2
1  Nail Varnish  12/5/18  2345   $30      4
2      Eyeliner  11/5/18  2346   $18      3
3      Eyeliner  12/5/18  2345   $30      5

如果excel文件中缺少第二个值，则需要进行预处理：

print (df)
                 SKU Unnamed: 1_level_0   Units Unnamed: 3_level_0   Sales  \
             11/5/18            12/5/18 11/5/18            12/5/18 11/5/18   
Nail Varnish    2345               2345       2                  4     $15   
Eyeliner        2346               2345       3                  5     $18   

             Unnamed: 5_level_0  
                        12/5/18  
Nail Varnish                $30  
Eyeliner                    $30 

a = df.columns.get_level_values(0)
b = df.columns.get_level_values(1)
a = a.where(~a.str.startswith('Unnamed')).to_series().ffill()
df.columns = [a, b]

print (df)
                 SKU           Units           Sales        
             11/5/18 12/5/18 11/5/18 12/5/18 11/5/18 12/5/18
Nail Varnish    2345    2345       2       4     $15     $30
Eyeliner        2346    2345       3       5     $18     $30

编辑1：

如果只需要筛选某些列以进行重塑：

cols = ['SKU','Units','Sales']
df = df[cols].stack().rename_axis(('Description','date')).reset_index()
print (df)
    Description     date   SKU Sales  Units
0  Nail Varnish  11/5/18  2345   $15      2
1  Nail Varnish  12/5/18  2345   $30      4
2      Eyeliner  11/5/18  2346   $18      3
3      Eyeliner  12/5/18  2345   $30      5

编辑1：

mux = pd.MultiIndex(levels=[['SKU ', 'Units', 'Unnamed: 0_level_0', 'Sales'], 
                            ['11/5/18', '12/5/18', 'Unnamed: 0_level_1'], 
                            ['total=5', 'total=9', 'total=33', 'total=60', 'Description', 'Unnamed: 1_level_2', 'Unnamed: 2_level_2']], codes=[[2, 0, 0, 1, 1, 3, 3], [2, 0, 1, 0, 1, 0, 1], [4, 5, 6, 1, 0, 2, 3]])

df = pd.DataFrame([range(7),range(7)], columns=mux)
print (df)
  Unnamed: 0_level_0               SKU                       Units          \
  Unnamed: 0_level_1            11/5/18            12/5/18 11/5/18 12/5/18   
         Description Unnamed: 1_level_2 Unnamed: 2_level_2 total=9 total=5   
0                  0                  1                  2       3       4   
1                  0                  1                  2       3       4   

     Sales           
   11/5/18  12/5/18  
  total=33 total=60  
0        5        6  
1        5        6  

---

a = df.columns.get_level_values(0)
b = df.columns.get_level_values(1)
c = df.columns.get_level_values(2)
#forward fliing missing values
a = a.where(~a.str.startswith('Unnamed')).to_series().ffill()
b = b.where(~b.str.startswith('Unnamed')).to_series().ffill()
#repalce missing values by empty string
c = c.where(~c.str.startswith('Unnamed'), '')

df.columns = [a, b, c]

#convert first column to index
df = df.set_index(df.columns[0])
df.index.name='Desc'
print (df)
        SKU            Units            Sales         
     11/5/18 12/5/18 11/5/18 12/5/18  11/5/18  12/5/18
                     total=9 total=5 total=33 total=60
Desc                                                  
0          1       2       3       4        5        6
0          1       2       3       4        5        6

---

#reshape and rename columns names
d = {'level_0':'a','level_1':'dates','level_2':'b'}
df = df.unstack().reset_index(name='vals').rename(columns=d)
print (df)
        a    dates         b  Desc  vals
0    SKU   11/5/18               0     1
1    SKU   11/5/18               0     1
2    SKU   12/5/18               0     2
3    SKU   12/5/18               0     2
4   Units  11/5/18   total=9     0     3
5   Units  11/5/18   total=9     0     3
6   Units  12/5/18   total=5     0     4
7   Units  12/5/18   total=5     0     4
8   Sales  11/5/18  total=33     0     5
9   Sales  11/5/18  total=33     0     5
10  Sales  12/5/18  total=60     0     6
11  Sales  12/5/18  total=60     0     6

Answer 2

^{}与^{}相反。你知道吗

and i dont know if it works for three or multi index columns.

是的，它适用于多索引列。如果列是多索引，则使用col_level参数来融化。 第一个链接中提供了示例。你知道吗