转置数据和拆分列
转置数据和拆分列
提问于 2021-02-10 19:23:03
我有这个示例数据框，常规数据框有大约35个不同的列，还有更多的类别/子类别
id drink_bottle drink_can food_napkin food_wrapper location
1 5 5 2 6 mall
2 2 4 3 1 mall
3 7 2 0 1 mall
4 0 3 9 2 urban
5 3 1 4 4 urban
6 2 4 5 3 urban
7 2 1 4 3 urban
8 7 1 2 0 urban
9 0 2 3 9 urban
10 3 4 4 4 recreational
11 2 3 5 5 recreational
12 5 1 0 3 recreational我想把它变成这样。
id category subcategory amount location
1 drink bottle 5 mall
1 drink can 5 mall
1 food napkin 2 mall
1 food wrapper 6 mall
2 drink bottle 2 mall
2 drink can 4 mall
2 food napkin 3 mall
2 food wrapper 1 mall
3 drink bottle 3 mall
3 drink can 4 mall
3 food napkin 5 mall
3 food wrapper 7 mall
4 drink bottle 1 urban
4 drink can 4 urban
4 food napkin 2 urban
4 food wrapper 1 urban我尝试过不同的方法，比如转置函数和使用字符串的各个部分来添加值。但是我真的不知道，解决这样的问题最好的办法是什么。因为我不知道如何重新排列它，所以它也需要标量，以防添加新列。有没有人可以把我推向正确的方向？
d={'drink_bottle': {1: 5, 2: 2, 3: 7, 4: 0},
'drink_can': {1: 5, 2: 4, 3: 2, 4: 3},
'food_napkin': {1: 2, 2: 3, 3: 0, 4: 9},
'food_wrapper': {1: 6, 2: 1, 3: 1, 4: 2},
'location': {1: 'mall', 2: 'mall', 3: 'mall', 4: 'urban'},
'id': {1: 1, 2: 2, 3: 3, 4: 4}}
df = pd.DataFrame(data=d)回答 1
Stack Overflow用户
回答已采纳
发布于 2021-02-10 19:25:57
您可以将带有_的非列名转换为按DataFrame.set_index建立索引，然后按Series.str.split拆分列并按DataFrame.stack按两个级别进行整形：
df1 = df.set_index(['id','location'])
df1.columns = df1.columns.str.split('_', expand=True)
df1 = (df1.rename_axis(['category', 'subcategory'], axis=1)
.stack([0,1])
.reset_index(name='amount'))
print (df1)
id location category subcategory amount
0 1 mall drink bottle 5.0
1 1 mall drink can 5.0
2 1 mall food napkin 2.0
3 1 mall food wrapper 6.0
4 2 mall drink bottle 2.0
5 2 mall drink can 4.0
6 2 mall food napkin 3.0
7 2 mall food wrapper 1.0
8 3 mall drink bottle 7.0
9 3 mall drink can 2.0
10 3 mall food napkin 0.0
11 3 mall food wrapper 1.0
12 4 urban drink bottle 0.0
13 4 urban drink can 3.0
14 4 urban food napkin 9.0
15 4 urban food wrapper 2.0页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/66135902
复制相关文章
相似问题
腾讯云开发者
