根据pandas或numpy中的某一列的值创建新行 [英] create new rows based the values of one of the column in pandas or numpy
问题描述
我有一个数据框，如下所示.这是医生的约会数据.
I have a data frame as shown below. which is doctors appointment data.
B_ID No_Show Session slot_num Cumulative_no_show
1 0.4 S1 1 0.4
2 0.3 S1 2 0.7
3 0.8 S1 3 1.5
4 0.3 S1 4 1.8
5 0.6 S1 5 2.4
6 0.8 S1 6 3.2
7 0.9 S1 7 4.1
8 0.4 S1 8 4.5
9 0.6 S1 9 5.1
12 0.9 S2 1 0.9
13 0.5 S2 2 1.4
14 0.3 S2 3 1.7
15 0.7 S2 4 2.4
20 0.7 S2 5 3.1
16 0.6 S2 6 3.7
17 0.8 S2 7 4.5
19 0.3 S2 8 4.8
当u_cumulative> 0.8时从上面创建一个新行，紧靠No_Show = 0.0的那一行，其Session和slot_num应该与前一个相同，并通过从前一个减去1创建一个新的列u_cumulative.
From the above when ever u_cumulative > 0.8 create a new row just below that with No_Show = 0.0 and its Session and slot_num should be same as previous one and create a new column called u_cumulative by subtracting 1 from the previous.
预期输出:
B_ID No_Show Session slot_num Cumulative_no_show u_cumulative
1 0.4 S1 1 0.4 0.4
2 0.3 S1 2 0.7 0.7
3 0.8 S1 3 1.5 1.5
walkin1 0.0 S1 3 1.5 0.5
4 0.3 S1 4 1.8 0.8
5 0.6 S1 5 2.4 1.4
walkin2 0.0 S1 5 2.4 0.4
6 0.8 S1 6 3.2 1.2
walkin3 0.0 S1 6 3.2 0.2
7 0.9 S1 7 4.1 1.1
walkin4 0.0 S1 7 4.1 0.1
8 0.4 S1 8 4.5 0.5
9 0.6 S1 9 5.1 1.1
walkin5 0.0 S1 7 5.1 0.1
12 0.9 S2 1 0.9 0.9
walkin1 0.0 S2 1 0.9 -0.1
13 0.5 S2 2 1.4 0.4
14 0.3 S2 3 1.7 0.7
15 0.7 S2 4 2.4 1.4
walkin2 0.0 S2 4 2.4 0.4
20 0.7 S2 5 3.1 1.1
walkin3 0.0 S2 5 3.1 0.1
16 0.6 S2 6 3.7 0.7
17 0.8 S2 7 4.5 1.5
walkin4 0.0 S2 7 4.5 0.5
19 0.3 S2 8 4.8 0.8
我在下面尝试计算u_cumulative
I tried below to calculate u_cumulative
def create_u_columns (ser):
arr_ns = ser.to_numpy()
arr_sn = np.ones(len(ser))
for i in range(len(arr_ns)-1):
if arr_ns[i]>0.6:
# remove 1 to u_no_show
arr_ns[i+1:] -= 1
else:
# increment u_slot_num
arr_sn[i+1:] += 1
#return a dataframe with both columns
return pd.DataFrame({'U_slot_num':arr_sn, 'U_No_show': arr_ns}, index=ser.index)
df[['U_slot_num', 'u_cumulative']] = df.groupby(['Session'])['Cumulative_No_show'].apply(create_u_columns)
但是我无法根据上述逻辑创建新行.
But I am not able create new rows based on the logic explained above.
推荐答案
您可以通过以下方法来实现此目的:创建一个count列，在其中添加后面的walkin行:
you can do it by slightly modify the function by creating a count column where to add the later walkin rows:
def create_u_columns (ser):
l_index = []
arr_ns = ser.to_numpy()
# array for latter insert
arr_idx = np.zeros(len(ser), dtype=int)
walkin_id = 1
for i in range(len(arr_ns)-1):
if arr_ns[i]>0.8:
# remove 1 to u_no_show
arr_ns[i+1:] -= 1
# increment later idx to add
arr_idx[i] = walkin_id
walkin_id +=1
#return a dataframe with both columns
return pd.DataFrame({'u_cumulative': arr_ns, 'mask_idx':arr_idx}, index=ser.index)
df[['u_cumulative', 'mask_idx']]= df.groupby(['Session'])['Cumulative_no_show'].apply(create_u_columns)
现在，您需要处理需要添加的行:
Now you need to work on the row that need to be added:
# select the rows
df_toAdd = df.loc[df['mask_idx'].astype(bool), :].copy()
# replace the values as wanted
df_toAdd['No_Show'] = 0
df_toAdd['B_ID'] = 'walkin'+df_toAdd['mask_idx'].astype(str)
df_toAdd['u_cumulative'] -= 1
# add 0.5 to index for later sort
df_toAdd.index += 0.5
现在，您只需要将此数据框concat还原为原始数据框sort_index，reset_index即可，并且只需drop先前创建的额外列即可
now you just need to concat this dataframe to the original one, sort_index, reset_index if needed to get a cleaner one and drop the extra column created earlier
new_df = pd.concat([df,df_toAdd]).sort_index()\
.reset_index(drop=True).drop('mask_idx', axis=1)
print (new_df)
B_ID No_Show Session slot_num Cumulative_no_show u_cumulative
0 1 0.4 S1 1 0.4 0.4
1 2 0.3 S1 2 0.7 0.7
2 3 0.8 S1 3 1.5 1.5
3 walkin1 0.0 S1 3 1.5 0.5
4 4 0.3 S1 4 1.8 0.8
5 5 0.6 S1 5 2.4 1.4
6 walkin2 0.0 S1 5 2.4 0.4
7 6 0.8 S1 6 3.2 1.2
8 walkin3 0.0 S1 6 3.2 0.2
9 7 0.9 S1 7 4.1 1.1
10 walkin4 0.0 S1 7 4.1 0.1
11 8 0.4 S1 8 4.5 0.5
12 9 0.6 S1 9 5.1 1.1
13 12 0.9 S2 1 0.9 0.9
14 walkin1 0.0 S2 1 0.9 -0.1
15 13 0.5 S2 2 1.4 0.4
16 14 0.3 S2 3 1.7 0.7
17 15 0.7 S2 4 2.4 1.4
18 walkin2 0.0 S2 4 2.4 0.4
19 20 0.7 S2 5 3.1 1.1
20 walkin3 0.0 S2 5 3.1 0.1
21 16 0.6 S2 6 3.7 0.7
22 17 0.8 S2 7 4.5 1.5
23 walkin4 0.0 S2 7 4.5 0.5
24 19 0.3 S2 8 4.8 0.8
这篇关于根据pandas或numpy中的某一列的值创建新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
