python - Pandas :将具有相同键的行分组在一行中

Question

在 pandas 中，我试图弄清楚如何使用相同的键对行进行分组，在一行中有一组包含组中键(按 id 分组)的常见特征，一组不常见的特征。

有点像

  id  C1  C2  Uk  U10  x   1   2   3   41  y   5   6   7   82  x   1   2   9  103  y   5   6   3  11

Here the set of common features is C1 and C2, the set of uncommon features is Uk and U1 with the key for the groups being Uk.

In this example, the desired result is:

  id  C1  C2  Uk3_U1  Uk7_U1  Uk9_U10  x   1   2       4     NaN    10.01  y   5   6      11     8.0     NaN

Of course, their could have been a U2 column (hi Bono!), but that makes the example harder to write because of the number of columns in the result would be bigger.

The code for generating this dataset is :

pd.DataFrame({'id': ['x', 'y', 'x', 'y'],
              'C1': [1, 5, 1, 5], 'C2': [2, 6, 2, 6],
              'Uk': [3, 7, 9, 3], 'U1': [4, 8, 10, 11]})

谢谢。

Answer 1

pd.pivot_table

您可以使用 pd.pivot_table 指定 index 和 columns:

# add string prefix to Uk series
df['Uk'] = 'Uk' + df['Uk'].astype(str)

# pivot data and add suffix to columns
res = pd.pivot_table(df, index=['id', 'C1', 'C2'], columns='Uk')\
        .add_suffix('_U1').reset_index()

# flatten MultiIndex columns
res.columns = [j or i for i, j in res.columns.values]

print(res)

  id  C1  C2  Uk3_U1  Uk7_U1  Uk9_U1
0  x   1   2     4.0     NaN    10.0
1  y   5   6    11.0     8.0     NaN