我正在尝试将 pandas 数据框转换为新格式。

我合并了两个数据框。新的数据框包含重复的条目，我想对其进行转换，以便这些重复的条目中的每一个都在一行中，其中包含包含此重复数据的额外列。我尝试使用 .pivot、.pivot_table、.melt、.unstack，但没有成功实现我想要的。

假设我有数据帧 d1:

d1 = pd.DataFrame({"Wall ID": ["E12", "E12", "E12", "C35", "C35", "C35"], 
                  "Length": [15, 15, 15, 21, 21, 21], 
                  "Station": ["A1", "A2", "A3", "A1", "A2", "A3"], 
                  "Time": [20, 14, 26, 29, 14, 12]}) 
d1 

我想把它改成dataframe d2:

d2 = pd.DataFrame({"Wall ID": ["E12", "C35"], 
                  "Length": [15, 21], 
                  "A1": [20, 29], 
                  "A2": [14, 14], 
                  "A3": [26, 12]}) 
d2 

请注意，对于相同的“Wall ID”，“Length”始终相同。

请您参考如下方法:

如果你想有性能你可以使用groupby:

Groupby 比数据透视表快一倍。

设置

import pandas as pd 
 
df = pd.DataFrame({"Wall ID": ["E12", "E12", "E12", "C35", "C35", "C35"], 
                  "Length": [15, 15, 15, 21, 21, 21], 
                  "Station": ["A1", "A2", "A3", "A1", "A2", "A3"], 
                  "Time": [20, 14, 26, 29, 14, 12]}) 
 
df 
  Wall ID  Length Station  Time 
0     E12      15      A1    20 
1     E12      15      A2    14 
2     E12      15      A3    26 
3     C35      21      A1    29 
4     C35      21      A2    14 
5     C35      21      A3    12 

使用数据透视表

%%timeit 
df.pivot_table("Time", ["Wall ID","Length"], "Station").reset_index() 
7.83 ms ± 503 µs per loop (mean ± std. dev. of 7 runs, 100 loops each) 
 

使用分组

%%timeit 
 
(df.groupby(["Wall ID", "Station","Length"]) # These are the indices we want 
.agg({"Time": 'first'}) # We want to aggregatge Time, e.g. 'mean' 
.unstack(level="Station") # Move Station to column section 
.droplevel(0,axis=1) # Remove outermost multi-index of agg col 'Time' 
.reset_index() # completely remove multi-index and make simple table 
) 
3.84 ms ± 300 µs per loop (mean ± std. dev. of 7 runs, 100 loops each) 

输出

Station Wall ID  Length  A1  A2  A3 
0           C35      21  29  14  12 
1           E12      15  20  14  26