python - 根据缺失条件在 Pandas 中创建代理行

Question

给定如下所示的 df，并假设 lapse 列下的值是唯一的，范围为 0 到 18。但是，某些值在此范围内不可用。对于此示例，缺少值 0、16 和 18。

   lapse    (a, i)    (a, j)    (b, k)         c
0    2.0  0.423655  0.645894  0.437587  0.891773
1    4.0  0.963663  0.383442  0.791725  0.528895
2    6.0  0.568045  0.925597  0.071036  0.087129
3    8.0  0.020218  0.832620  0.778157  0.870012
4   10.0  0.978618  0.799159  0.461479  0.780529
5   12.0  0.118274  0.639921  0.143353  0.944669
6   14.0  0.521848  0.414662  0.264556  0.774234

目标是创建这些缺失值的代理行，并将其附加到原始df。这样，输出应如下所示

   lapse    (a, i)    (a, j)    (b, k)         c
0    0.0       NaN       NaN       NaN       NaN
0    2.0  0.423655  0.645894  0.437587  0.891773
1    4.0  0.963663  0.383442  0.791725  0.528895
2    6.0  0.568045  0.925597  0.071036  0.087129
3    8.0  0.020218  0.832620  0.778157  0.870012
4   10.0  0.978618  0.799159  0.461479  0.780529
5   12.0  0.118274  0.639921  0.143353  0.944669
6   14.0  0.521848  0.414662  0.264556  0.774234
1   16.0       NaN       NaN       NaN       NaN
2   18.0       NaN       NaN       NaN       NaN

下面的代码片段能够回答上述目标。然而，在实际实现中，数据帧的数量级更大，我想知道是否有更好的方法，或者内置的 pandas 来做到这一点？

生成原始df的行

import numpy as np
import pandas as pd

nshape=5
increment=2
max_val=20
np.random.seed(0)
aran=np.arange(0,max_val,increment).astype(int)
nshape=aran.shape[0]
arr=np.concatenate((aran.reshape(-1,1), np.random.random((nshape,4))), axis=1)

# Extracted only selected, other non selected index are assume case to solve
idx_available=[3, 5, 4, 2, 1, 7, 6]

df=pd.DataFrame(arr[sorted(idx_available),:],columns=['lapse',('a','i'),('a','j'),('b','k'),'c'])

建议的解决方案

name_other=[i for i in df.columns.tolist() if i!='lapse']
lapse_available=df['lapse'].to_numpy()
lapse_not_available = np.setdiff1d(aran,lapse_available)

an_array = np.empty((len(lapse_not_available),len(name_other)))
an_array[:] = np.NaN
arr2=np.concatenate((lapse_not_available.reshape(-1,1), an_array), axis=1)
df2=pd.DataFrame(arr2,columns=['lapse']+name_other)
df=pd.concat([df,df2],axis=0).sort_values(by=['lapse'])

Answer 1

您还可以使用:

df.set_index('lapse', inplace=True)
df = df.reindex(np.arange(0,20,2)).reset_index()

输出

   lapse    (a, i)    (a, j)    (b, k)         c
0      0       NaN       NaN       NaN       NaN
1      2  0.423655  0.645894  0.437587  0.891773
2      4  0.963663  0.383442  0.791725  0.528895
3      6  0.568045  0.925597  0.071036  0.087129
4      8  0.020218  0.832620  0.778157  0.870012
5     10  0.978618  0.799159  0.461479  0.780529
6     12  0.118274  0.639921  0.143353  0.944669
7     14  0.521848  0.414662  0.264556  0.774234
8     16       NaN       NaN       NaN       NaN
9     18       NaN       NaN       NaN       NaN