問題描述
我有一個這樣的數據框，我正在嘗試使用 Pandas 中的 Pivot 重塑我的數據框，這樣我可以保留原始行中的一些值，同時將重復的行變成列并重命名它們.有時我有 5 個重復的行
I have a data frame like this and I'm trying reshape my data frame using Pivot from Pandas in a way that I can keep some values from the original rows while making the duplicates row into columns and renaming them. Sometimes I have rows with 5 duplicates
我一直在嘗試，但我不明白.
I have been trying, but I don't get it.
import pandas as pd
df = pd.read_csv("C:dummy")
df = df.pivot(index=["ID"], columns=["Zone","PTC"], values=["Zone","PTC"])
# Rename columns and reset the index.
df.columns = [["PTC{}","Zone{}"],.format(c) for c in df.columns]
df.reset_index(inplace=True)
# Drop duplicates
df.drop(["PTC","Zone"], axis=1, inplace=True)
輸入
ID Agent OV Zone Value PTC
1 10 26 M1 10 100
2 26.5 8 M2 50 95
2 26.5 8 M1 6 5
3 4.5 6 M3 4 40
3 4.5 6 M4 6 60
4 1.2 0.8 M1 8 100
5 2 0.4 M1 6 10
5 2 0.4 M2 41 86
5 2 0.4 M4 2 4
輸出
ID Agent OV Zone1 Value1 PTC1 Zone2 Value2 PTC2 Zone3 Value3 PTC3
1 10 26 M_1 10 100 0 0 0 0 0 0
2 26.5 8 M_2 50 95 M_1 6 5 0 0 0
3 4.5 6 M_3 4 40 M_4 6 60 0 0 0
4 1.2 0.8 M_1 8 100 0 0 0 0 0 0
5 2 0.4 M_1 6 10 M_2 41 86 M_4 2 4
推薦答案
使用cumcount 用于計數組，由 MultiIndex/pandas-docs/stable/generated/pandas.DataFrame.set_index.html" rel="nofollow noreferrer">set_index 與 unstack 和列的最后展平值:
Use cumcount for count groups, create MultiIndex by set_index with unstack and last flatten values of columns:
g = df.groupby(["ID","Agent", "OV"]).cumcount().add(1)
df = df.set_index(["ID","Agent","OV", g]).unstack(fill_value=0).sort_index(axis=1, level=1)
df.columns = ["{}{}".format(a, b) for a, b in df.columns]
df = df.reset_index()
print (df)
ID Agent OV Zone1 Value1 PTC1 Zone2 Value2 PTC2 Zone3 Value3 PTC3
0 1 10.0 26.0 M1 10 100 0 0 0 0 0 0
1 2 26.5 8.0 M2 50 95 M1 6 5 0 0 0
2 3 4.5 6.0 M3 4 40 M4 6 60 0 0 0
3 4 1.2 0.8 M1 8 100 0 0 0 0 0 0
4 5 2.0 0.4 M1 6 10 M2 41 86 M4 2 4
如果只想替換為 0 數字列:
If want replace to 0 only numeric columns:
g = df.groupby(["ID","Agent"]).cumcount().add(1)
df = df.set_index(["ID","Agent","OV", g]).unstack().sort_index(axis=1, level=1)
idx = pd.IndexSlice
df.loc[:, idx[['Value','PTC']]] = df.loc[:, idx[['Value','PTC']]].fillna(0).astype(int)
df.columns = ["{}{}".format(a, b) for a, b in df.columns]
df = df.fillna('').reset_index()
print (df)
ID Agent OV Zone1 Value1 PTC1 Zone2 Value2 PTC2 Zone3 Value3 PTC3
0 1 10.0 26.0 M1 10 100 0 0 0 0
1 2 26.5 8.0 M2 50 95 M1 6 5 0 0
2 3 4.5 6.0 M3 4 40 M4 6 60 0 0
3 4 1.2 0.8 M1 8 100 0 0 0 0
4 5 2.0 0.4 M1 6 10 M2 41 86 M4 2 4
這篇關于Pivot 將行復制到新列 Pandas的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！