重塑pandas数据帧(按列转置行)

Question

我刚开始学习Python，并希望重塑它在R上的实现方式。

我现在有d1格式的数据帧，我想把它转换成d2格式：

d1:列有: Country、Movie_type (取值为动作、浪漫或戏剧)、1-20、2-20和3-20。

d2:列有:国家、月份-年(值为1-20、2-20或3-20)、动作、戏剧、浪漫..

(请注意，戏剧和浪漫的栏目不会显示来自d1的实际计数，只有动作会)。

d1 = pd.DataFrame({

    "Country" : {0: "Australia",1: "Australia", 2:"Australia", 3:"Canada", 4:"Canada", 5:"Israel", 6:"India",7: "India", 8:"Poland", 9: "Zambia"},
    "Movie_type" : {0: "Action",1:"Drama",2: "Romance",3: "Romance",4: "Action",5: "Action",6: "Action",7: "Drama",8: "Drama",9:"Action"},
    "Jan-20" :{0: 0,1: 1,2: 2,3: 3,4: 0,5: 3,6: 2,7: 0,8: 2,9: 5},
    "Feb-20" :{0: 2,1: 3,2: 2,3: 1,4: 0,5: 0,6: 2,7: 4,8: 2,9: 7},
    "Mar-20" :{0: 1,1: 5,2: 2,3: 7,4: 4,5: 8,6: 2,7: 4,8: 5,9: 9}
}
)



d2 = pd.DataFrame({

    "Country" : {0: "Australia",1: "Australia", 2:"Australia", 3:"Canada", 4:"Canada", 5:"Canada", 6:"Israel",7: "Israel", 8:"Israel", 9: "India", 10: "India", 11: "India", 12: "Poland", 13: "Poland", 14: "Poland", 15: "Zambia", 16: "Zambia", 17: "Zambia"},
    "Month_year" : {0: "Jan-20",1: "Feb-20", 2:"Mar-20", 3: "Jan-20",4: "Feb-20", 5:"Mar-20",6: "Jan-20",7: "Feb-20", 8:"Mar-20",9: "Jan-20",10: "Feb-20", 11:"Mar-20",12: "Jan-20",13: "Feb-20", 14:"Mar-20",15: "Jan-20",16: "Feb-20", 17:"Mar-20"},
    "Action" :{0: 0,1: 0,2: 0,3: 0,4: 0,5: 0,6: 3,7: 0,8: 0,9: 2,10:0,11:0,12:0,13:0,14:0,15:5,16:0,17:0},
    "Drama" :{0: 0,1: 0,2: 0,3: 0,4: 0,5: 0,6: 0,7: 0,8: 0,9: 0,10:0,11:0,12:0,13:0,14:0,15:0,16:0,17:0},
    "Romance" :{0: 0,1: 0,2: 0,3: 0,4: 0,5: 0,6: 0,7: 0,8: 0,9: 0,10:0,11:0,12:0,13:0,14:0,15:0,16:0,17:0}
}
)

如何将Movie_types转换为列名，并将MMM-YY (当前在d1中作为列名称)转换为行的DIFF值？如果这是一个堆栈/取消堆栈/旋转或重塑问题，我会很乐意接受教育。

Answer 1

在DataFrame.pivot中使用DataFrame.melt，因为正确的排序是将值转换为datetimes：

df = (d1.melt(['Country','Movie_type'], 
             var_name='Month_year')
        .assign(Month_year = lambda x: pd.to_datetime(x['Month_year'], format='%b-%y'))
        .pivot(['Country','Month_year'], 'Movie_type','value')
        .fillna(0)
        .astype(int)
        .rename(index = lambda x: x.strftime('%b-%y'), level=1)
        .reset_index()
        )
print (df)
Movie_type    Country Month_year  Action  Drama  Romance
0           Australia     Jan-20       0      1        2
1           Australia     Feb-20       2      3        2
2           Australia     Mar-20       1      5        2
3              Canada     Jan-20       0      0        3
4              Canada     Feb-20       0      0        1
5              Canada     Mar-20       4      0        7
6               India     Jan-20       2      0        0
7               India     Feb-20       2      4        0
8               India     Mar-20       2      4        0
9              Israel     Jan-20       3      0        0
10             Israel     Feb-20       0      0        0
11             Israel     Mar-20       8      0        0
12             Poland     Jan-20       0      2        0
13             Poland     Feb-20       0      2        0
14             Poland     Mar-20       0      5        0
15             Zambia     Jan-20       5      0        0
16             Zambia     Feb-20       7      0        0
17             Zambia     Mar-20       9      0        0