给定以下数据框
data = [[1, 'Yes','A','No','Yes','No','No','No'],
[2, 'Yes','A','No','No','Yes','No','No'],
[3, 'Yes','B','No','No','Yes','No','No'],
[4, 'No','','','','','',''],
[5, 'No','','','','','',''],
[6, 'Yes','C','No','No','Yes','Yes','No'],
[7, 'Yes','A','No','Yes','No','No','No'],
[8, 'Yes','A','No','No','Yes','No','No'],
[9, 'No','','','','','',''],
[10, 'Yes','B','Yes','Yes','No','No','No']]
df = pd.DataFrame(data,columns=['Cust_ID','OrderMade','OrderType','OrderCategoryA','OrderCategoryB','OrderCategoryC','OrderCategoryD'])
+----+-----------+-------------+-------------+------------------+------------------+------------------+------------------+
| | Cust_ID | OrderMade | OrderType | OrderCategoryA | OrderCategoryB | OrderCategoryC | OrderCategoryD |
|----+-----------+-------------+-------------+------------------+------------------+------------------+------------------|
| 0 | 1 | Yes | A | No | Yes | No | No |
| 1 | 2 | Yes | A | No | No | Yes | No |
| 2 | 3 | Yes | B | No | No | Yes | No |
| 3 | 4 | No | | | | | |
| 4 | 5 | No | | | | | |
| 5 | 6 | Yes | C | No | No | Yes | Yes |
| 6 | 7 | Yes | A | No | Yes | No | No |
| 7 | 8 | Yes | A | No | No | Yes | No |
| 8 | 9 | No | | | | | |
| 9 | 10 | Yes | B | Yes | Yes | No | No |
+----+-----------+-------------+-------------+------------------+------------------+------------------+------------------+
我如何将其转换为基于 OrderCategory 的行？
+--------+-----------+----------+----------------+
|Cust_ID | OrderMade |OrderType | OrderCategory |
|--------+-----------+----------+----------------|
|1 | Yes | A | OrderCategoryB |
|2 | Yes | A | OrderCategoryC |
|3 | Yes | B | OrderCategoryC |
|4 | No | | |
|5 | No | | |
|6 | Yes | C | OrderCategoryC |
|6 | Yes | C | OrderCategoryD |
|7 | Yes | A | OrderCategoryB |
|8 | Yes | A | OrderCategoryC |
|9 | No | | |
|10 | Yes | B | OrderCategoryA |
|10 | Yes | B | OrderCategoryB |
+--------+-----------+----------+----------------+
我尝试使用 crosstab 从一个 OrderCategory 开始，并计划为每个类别复制，但这似乎效率低下，我不确定如何继续得到我想要的结果...
imgCROSS = pd.crosstab(df["Cust_ID"], df["OrderCategoryA"])
返回...
OrderCategoryA No Yes
Cust_ID
1 0 1 0
2 0 1 0
3 0 1 0
4 1 0 0
5 1 0 0
6 0 1 0
7 0 1 0
8 0 1 0
9 1 0 0
10 0 0 1
我还想我可以填充一个名为 Category 的新空列并遍历每一行，根据 Yes/No 值填充适当的类别，但这不会' 适用于具有多个类别的行。此外，此想法的以下实现返回了一个空列。
imgRaw["Category"] = ""
for index, row in df.iterrows():
catA = row["OrderCategoryA"]
catB = row["OrderCategoryB"]
catC = row["OrderCategoryC"]
catD = row["OrderCategoryD"]
if catA == "Yes":
row["Category"] = "OrderCategoryA"
elif catB == "Yes":
row["Category"] = "OrderCategoryB"
elif catC == "Yes":
row["Category"] = "OrderCategoryC"
elif catD == "Yes":
row["Category"] = "OrderCategoryD"
我知道我需要转换数据框，可能要转换多次才能得到我想要的结果。只是停留在如何继续。
请您参考如下方法:
让我们分四步使用 pandas:
df_1 = df.set_index(['Cust_ID', 'OrderMade', 'OrderType'])
df_2 = df_1.where((df_1 == "Yes") | (df_1 == "")).rename_axis('OrderCategory', axis=1).stack().reset_index()
df_2['OrderCategory'] = df_2['OrderCategory'].mask(df_2['OrderMade'] == 'No','')
df_2.drop_duplicates().drop(0, axis=1)
输出:
Cust_ID OrderMade OrderType OrderCategory
0 1 Yes A OrderCategoryB
1 2 Yes A OrderCategoryC
2 3 Yes B OrderCategoryC
3 4 No
8 5 No
13 6 Yes C OrderCategoryC
14 6 Yes C OrderCategoryD
15 7 Yes A OrderCategoryB
16 8 Yes A OrderCategoryC
17 9 No
22 10 Yes B OrderCategoryA
23 10 Yes B OrderCategoryB
