python - 尝试使用多个空列 (NaN) 取消堆叠数据帧

Question

我目前有一个代码可以变成这样:

       A     B    C  D    E  F    G  H    I  J
0  1.1.1  amba   50  1  131  4   40  3  150  5
1  2.2.2  erto   50  7   40  8  150  8  131  2
2  3.3.3  gema  131  2  150  5   40  1   50  3

进入这个:

      ID  User  40  50  131  150
0  1.1.1  amba   3   1    4    5
1  2.2.2  erto   8   7    2    8
2  3.3.3  gema   1   3    2    5

在这里您可以检查代码:

import pandas as pd
import io

df1 = pd.read_csv(io.StringIO("""  A       B       C      D      E      F      G      H      I       J     
1.1.1   amba     50      1     131     4      40       3       150       5   
2.2.2   erto     50      7     40      8      150      8       131       2
3.3.3   gema     131     2    150      5      40       1        50       3"""), sep="\s+")



print(df1)


df2 = (pd.concat([df1.drop(columns=["C","D","E","F","G","H"]).rename(columns={"I":"key","J":"val"}),
           df1.drop(columns=["C","D","E","F","I","J"]).rename(columns={"G":"key","H":"val"}),
           df1.drop(columns=["C","D","G","H","I","J"]).rename(columns={"E":"key","F":"val"}),
           df1.drop(columns=["E","F","G","H","I","J"]).rename(columns={"C":"key","D":"val"}),
          ])      
 .rename(columns={"A":"ID","B":"User"})
 .set_index(["ID","User","key"])
 .unstack(2)
 .reset_index()
)

# flatten the columns..
df2.columns = [c[1] if c[0]=="val" else c[0] for c in df2.columns.to_flat_index()]
df2

如果 Key 列具有唯一值，则程序可以正常工作，但如果存在重复值，则程序会失败。我遇到的问题是我的实际数据框有 30 个 clumns 的行，其他有 60 个，其他有 63 个等。所以程序将空值检测为重复，程序失败。
请检查这个例子:

       A     B    C  D    E  F      G    H      I    J
0  1.1.1  amba   50  1  131  4    NaN  NaN    NaN  NaN
1  2.2.2  erto   50  7   40  8  150.0  8.0  131.0  2.0
2  3.3.3  gema  131  2  150  5   40.0  1.0   50.0  3.0

我想得到这样的东西:

      ID  User  40  50  131  150
0  1.1.1  amba       1    4    
1  2.2.2  erto   8   7    2    8
2  3.3.3  gema   1   3    2    5

如果我尝试取消堆叠，我会收到错误“索引包含重复条目，无法 reshape ”。我一直在阅读这方面的内容，df.drop_duplicates、pivot_tables、tc 可以在这种情况下提供帮助，但我不能用我当前的代码来做任何事情。关于如何解决这个问题的任何想法？谢谢。

Answer 1

想法是将前 2 列转换为 MultiIndex ，然后使用 concat 按 DataFrame.iloc 选择的对和取消配对列, 由 DataFrame.stack reshape 并删除了 MultiIndex 的第三个不必要的级别由 DataFrame.reset_index :

df = df.set_index(['A','B'])
df = pd.concat([df.iloc[:, ::2].stack().reset_index(level=2, drop=True),
               df.iloc[:, 1::2].stack().reset_index(level=2, drop=True)], 
               axis=1, keys=('key','val'))

最后添加 key列至 MultiIndex由 DataFrame.set_index 并通过 Series.unstack reshape , 转换 MultiIndex到 reset_index 的列, 重命名列名并最后删除列级别名称 DataFrame.rename_axis :

df = (df.set_index('key', append=True)['val']
        .unstack()
        .reset_index()
        .rename(columns={"A":"ID","B":"User"})
        .rename_axis(None, axis=1))
print (df)
      ID  User  40  50  131  150
0  1.1.1  amba   3   1    4    5
1  2.2.2  erto   8   7    2    8
2  3.3.3  gema   1   3    2    5

它也适用于第二个示例，因为 stack 删除了丢失的行，还添加了 rename用于将列名转换为 int如果可能的话:

df = df.set_index(['A','B'])
df = pd.concat([df.iloc[:, ::2].stack().reset_index(level=2, drop=True),
               df.iloc[:, 1::2].stack().reset_index(level=2, drop=True)], 
               axis=1, keys=('key','val'))


print (df)
              key  val
A     B               
1.1.1 amba   50.0  1.0
      amba  131.0  4.0
2.2.2 erto   50.0  7.0
      erto   40.0  8.0
      erto  150.0  8.0
      erto  131.0  2.0
3.3.3 gema  131.0  2.0
      gema  150.0  5.0
      gema   40.0  1.0
      gema   50.0  3.0


df = (df.set_index('key', append=True)['val']
        .unstack()
        .rename(columns=int)
        .reset_index()
        .rename(columns={"A":"ID","B":"User"})
        .rename_axis(None, axis=1))
print (df)
      ID  User   40   50  131  150
0  1.1.1  amba  NaN  1.0  4.0  NaN
1  2.2.2  erto  8.0  7.0  2.0  8.0
2  3.3.3  gema  1.0  3.0  2.0  5.0

EDIT1 添加了带有计数器的辅助列以避免重复:

print (df)
       A     B    C  D    E  F    G  H    I  J
0  1.1.1  amba   50  1   50  4   40  3  150  5 <- E=50
1  2.2.2  erto   50  7   40  8  150  8  131  2
2  3.3.3  gema  131  2  150  5   40  1   50  3

df = df.set_index(['A','B'])
df = pd.concat([df.iloc[:, ::2].stack().reset_index(level=2, drop=True),
               df.iloc[:, 1::2].stack().reset_index(level=2, drop=True)], 
               axis=1, keys=('key','val'))

df['g'] = df.groupby(['A','B','key']).cumcount()
print (df)
            key  val  g
A     B                
1.1.1 amba   50    1  0
      amba   50    4  1
      amba   40    3  0
      amba  150    5  0
2.2.2 erto   50    7  0
      erto   40    8  0
      erto  150    8  0
      erto  131    2  0
3.3.3 gema  131    2  0
      gema  150    5  0
      gema   40    1  0
      gema   50    3  0

df = (df.set_index(['g','key'], append=True)['val']
        .unstack()
        .reset_index()
        .rename(columns={"A":"ID","B":"User"})
        .rename_axis(None, axis=1))
print (df)
      ID  User  g   40   50  131  150
0  1.1.1  amba  0  3.0  1.0  NaN  5.0
1  1.1.1  amba  1  NaN  4.0  NaN  NaN
2  2.2.2  erto  0  8.0  7.0  2.0  8.0
3  3.3.3  gema  0  1.0  3.0  2.0  5.0