python - 根据具有值的列的数量转置 DataFrame

Question

我希望转置编号为 1-11 的列中的值，而不是 NaN。对于每列转置，我需要将“运算符(operator) id”和其余数据复制到每个新行。每个“运算符(operator) id”最多可以有 11 个条目或只有 1 个条目。

我尝试过 df.T 和 df.stack()。

df.head()
Out[7]: 
    Operator id  CountryID                  1                   2        3    4    5    6    7    8    9   10   11
0    john.smith      376.0     jake.sha.human                 NaN      NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN
1    john.wayne      826.0  sambaser@fake.com                 NaN      NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN
2    mike.evans      840.0    ardenn@fake.com  isabellac@fake.com  sam.cha  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN
3  sarah.wagner      356.0    nathan@fake.com                 NaN      NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN
4    two.chains      784.0        AR@fake.com         hr@fake.com      NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN

    Operator id  CountryID               TransCol
0    john.smith      376.0          jake.sha.human
1    john.wayne      826.0          sambaser@fake.com
2    mike.evans      840.0          ardenn@fake.com
3    mike.evans      840.0          isabellac@fake.com
4    mike.evans      840.0          sam.cha
5    sarah.wagner    356.0          nathan@fake.com
6    two.chains      784.0          AR@fake.com
7    two.chains      784.0          hr@fake.com

Answer 1

这是融化:

df.melt(id_vars=['Operator id', 'CountryID']).drop('variable', 1).dropna()

输出:

     Operator id  CountryID               value
0     john.smith      376.0      jake.sha.human
1     john.wayne      826.0   sambaser@fake.com
2     mike.evans      840.0     ardenn@fake.com
3   sarah.wagner      356.0     nathan@fake.com
4     two.chains      784.0         AR@fake.com
7     mike.evans      840.0  isabellac@fake.com
9     two.chains      784.0         hr@fake.com
12    mike.evans      840.0             sam.cha

或堆栈:

(df.set_index(['Operator id', 'CountryID'])
   .stack()
   .reset_index(level=-1, drop=True)
   .reset_index(name='TransCol')
)

输出:

    Operator id  CountryID            TransCol
0    john.smith      376.0      jake.sha.human
1    john.wayne      826.0   sambaser@fake.com
2    mike.evans      840.0     ardenn@fake.com
3    mike.evans      840.0  isabellac@fake.com
4    mike.evans      840.0             sam.cha
5  sarah.wagner      356.0     nathan@fake.com
6    two.chains      784.0         AR@fake.com
7    two.chains      784.0         hr@fake.com