python - 根据 Pandas 中的条件创建更多行

Question

我有如下数据框:

id, index, val_1, val_2
1, 1, 0.2, 0
1, 2, 0.4, 0.2
2,2, 0.1, 0.5
2,4, 0.7, 0.0
....

等等

现在，每个 id 允许的索引值的完整范围是

[1,2,3,4]

因此，如果每个 id 缺少此索引中的任何一个，我想添加这些行。所以对于上面的例子，期望的输出是

id, index, val_1, val_2
1, 1, 0.2, 0
1, 2, 0.4, 0.2
1, 3, 0, 0 # added because index 3 was missing for id 1
1, 4, 0, 0  # added because index 4 was missing for id 1
2, 1,0,0  # added because index 1 was missing for id 2
2,2, 0.1, 0.5
2, 3, 0, 0
2,4, 0.7, 0.0
....

我如何在 pandas 中执行此操作？

Answer 1

试试这个:

In [210]: from itertools import product

In [211]: x = pd.DataFrame(list(product(df.id.unique(), [1,2,3,4])), columns=['id','index']).assign(val_1=0, val_2=0).set_index(['id','index'])

In [212]: x.update(df.set_index(['id','index']))

In [213]: x
Out[213]:
          val_1  val_2
id index
1  1        0.2    0.0
   2        0.4    0.2
   3        0.0    0.0
   4        0.0    0.0
2  1        0.0    0.0
   2        0.1    0.5
   3        0.0    0.0
   4        0.7    0.0

In [214]: x.reset_index()
Out[214]:
   id  index  val_1  val_2
0   1      1    0.2    0.0
1   1      2    0.4    0.2
2   1      3    0.0    0.0
3   1      4    0.0    0.0
4   2      1    0.0    0.0
5   2      2    0.1    0.5
6   2      3    0.0    0.0
7   2      4    0.7    0.0

解释:

In [225]: x = (pd.DataFrame(list(product(df.id.unique(), [1,2,3,4])), columns=['id','index'])
   .....:        .assign(val_1=0, val_2=0)
   .....:        .set_index(['id','index']))

In [226]: x
Out[226]:
          val_1  val_2
id index
1  1          0      0
   2          0      0
   3          0      0
   4          0      0
2  1          0      0
   2          0      0
   3          0      0
   4          0      0

In [227]: x.update(df.set_index(['id','index']))

In [228]: x
Out[228]:
          val_1  val_2
id index
1  1        0.2    0.0
   2        0.4    0.2
   3        0.0    0.0
   4        0.0    0.0
2  1        0.0    0.0
   2        0.1    0.5
   3        0.0    0.0
   4        0.7    0.0

In [229]: x.reset_index()
Out[229]:
   id  index  val_1  val_2
0   1      1    0.2    0.0
1   1      2    0.4    0.2
2   1      3    0.0    0.0
3   1      4    0.0    0.0
4   2      1    0.0    0.0
5   2      2    0.1    0.5
6   2      3    0.0    0.0
7   2      4    0.7    0.0