创建一个函数以根据给定的参数在数据框中创建新行作为 pandas 列表 [英] create a function to create new rows in data frames based on the given parameters as list in pandas
问题描述
我有一个如下所示的数据框.数据总是有一个会话.这意味着会话"列中唯一值的数量将始终为一个.
I have a data frame as shown below. where the data always will have one session. That means number of unique value in a column 'Session' will be one always.
df:
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
我尝试下面的代码来创建上面的 df.
I tried below code to create above df.
df = pd.DataFrame({'B_ID': [1, 2, 3, 4, 5, 6, 7, 8, 9],
'No_Show': [0.4, 0.3, 0.8, 0.3, 0.6, 0.8, 0.9, 0.4, 0.6],
'Session': ['s1', 's1', 's1', 's1', 's1', 's1', 's1', 's1', 's1'],
'slot_num': [1, 2, 3, 4, 5, 6, 7, 8, 9],
'Cumulative_no_show': [0.4, 0.7, 1.5, 1.8, 2.4, 3.2, 4.1, 4.5, 5.1]})
df['Cumulative_no_show'] = df.groupby(['Session'])['No_Show'].cumsum()
我还有一个名为的列表，它可以是任意长度，这里是 9.
also I have a list called which can be of any length here it is 9.
walkin_no_show = [ 0.3, 0.2, 0.1, 0.4, 0.5, 0.4, 0.2, 0.7, 0.8]
我还有一个名为长度为 4 的列表
And I have a another list called is of length 4
threshold_p = [0.8, 0.9, 1.0, 1.1]
从上面当 u_cumulative > threshold_p[j] 创建一个新行时，就在其下方
From the above when ever u_cumulative > threshold_p[j] create a new row just below that with
df[No_Show] = walkin_no_show[i]
及其 Session 和 slot_num 应与前一个相同，并通过从前一个中减去 (1 - walkin_no_show[i]) 创建一个名为 u_cumulative 的新列.
and its Session and slot_num should be same as previous one and create a new column called u_cumulative by subtracting (1 - walkin_no_show[i]) from the previous.
我想创建一个名为 overbook_dfs 的函数
I would like create a fuction called overbook_dfs
def overbook_dfs (df, walkin_no_show, threshold_p ):
return df_0_8, df_0_9, df_1_0, df_1_1
预期输出 dfs 如下所示:
where the expected output dfs are shown below:
预期输出:
df_0_8:
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.3 S1 3 1.5 0.8
4 0.3 S1 4 1.8 1.1
walkin2 0.2 S1 4 1.8 0.3
5 0.6 S1 5 2.4 0.9
walkin3 0.1 S1 5 2.4 0.0
6 0.8 S1 6 3.2 0.8
7 0.9 S1 7 4.1 1.7
walkin4 0.4 S1 7 4.1 1.1
8 0.4 S1 8 4.5 1.5
walkin5 0.5 S1 8 4.5 1.1
9 0.6 S1 9 5.1 1.7
walkin6 0.4 S1 9 5.1 1.1
df_0_9:
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.3 S1 3 1.5 0.8
4 0.3 S1 4 1.8 1.1
walkin2 0.2 S1 4 1.8 0.3
5 0.6 S1 5 2.4 0.9
6 0.8 S1 6 3.2 1.7
walkin3 0.1 S1 6 3.2 0.8
7 0.9 S1 7 4.1 1.7
walkin4 0.4 S1 7 4.1 1.1
8 0.4 S1 8 4.5 1.5
walkin5 0.5 S1 8 4.5 1.1
9 0.6 S1 9 5.1 1.7
walkin6 0.4 S1 9 5.1 1.1
df_1_0:
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.3 S1 3 1.5 0.8
4 0.3 S1 4 1.8 1.1
walkin2 0.2 S1 4 1.8 0.3
5 0.6 S1 5 2.4 0.9
6 0.8 S1 6 3.2 1.7
walkin3 0.1 S1 6 3.2 0.8
7 0.9 S1 7 4.1 1.7
walkin4 0.4 S1 7 4.1 1.1
8 0.4 S1 8 4.5 1.5
walkin5 0.5 S1 8 4.5 1.0
9 0.6 S1 9 5.1 1.6
walkin6 0.4 S1 9 4.5 1.0
df_1_1:
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.3 S1 3 1.5 0.8
4 0.3 S1 4 1.8 1.1
5 0.6 S1 5 2.4 1.6
walkin2 0.2 S1 5 2.4 0.8
6 0.8 S1 6 3.2 1.6
walkin3 0.1 S1 6 3.2 0.7
7 0.9 S1 7 4.1 1.6
walkin4 0.4 S1 7 4.1 1.0
8 0.4 S1 8 4.5 1.4
walkin5 0.5 S1 8 4.5 0.9
9 0.6 S1 9 5.1 1.5
walkin6 0.4 S1 8 5.1 0.9
推荐答案
这是一种方法
# function to create the u_cumulative
def create_u_columns (ser, threshold_ns = 0.8):
# create a copy
arr_ns = ser.to_numpy().copy()
# array for latter insert
arr_idx = np.zeros(len(ser), dtype=int)
walkin_id = 0 #start at 0 not 1 for list indexing
for i in range(len(arr_ns)-1):
if arr_ns[i]>threshold_ns:
# remove 1 - walkin
arr_ns[i+1:] -= (1-walkin_no_show[walkin_id])
# increment later idx to add
arr_idx[i] = walkin_id+1
walkin_id +=1
# for the last row
if arr_ns[-1]>threshold_ns:
arr_idx[-1] = walkin_id+1
#return a dataframe with both columns
return pd.DataFrame({'u_cumulative': arr_ns, 'mask_idx':arr_idx}, index=ser.index)
现在定义另一个函数overbook_dfs
def overbook_dfs (df0, walkin_no_show, threshold_p ):
l_res = [] #for result
for th_p in threshold_p: #loop on threshold
# create a copy of original dataframe
df = df0.copy()
df[['u_cumulative','mask_idx']] = create_u_columns(df['Cumulative_no_show'],
threshold_ns=th_p)
# select the rows
df_toAdd = df.loc[df['mask_idx'].astype(bool), :].copy()
# replace the values as wanted
df_toAdd['No_Show'] = walkin_no_show[:len(df_toAdd)]
df_toAdd['B_ID'] = 'walkin'+df_toAdd['mask_idx'].astype(str)
df_toAdd['u_cumulative'] -= (1 - df_toAdd['No_Show'])
# add 0.5 to index for later sort
df_toAdd.index += 0.5
#append the result to a list
l_res.append(pd.concat([df,df_toAdd])
.sort_index()
.reset_index(drop=True)
.drop('mask_idx', axis=1)
)
return l_res
最后和参数一起使用
# parameters
walkin_no_show = [ 0.3, 0.2, 0.1, 0.4, 0.5, 0.4, 0.2, 0.7, 0.8]
threshold_p = [0.8, 0.9, 1.0, 1.1]
# call your function
df_0_8, df_0_9, df_1_0, df_1_1 = overbook_dfs(df, walkin_no_show, threshold_p)
print (df_0_9)
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.3 s1 3 1.5 0.8
4 4 0.3 s1 4 1.8 1.1
5 walkin2 0.2 s1 4 1.8 0.3
6 5 0.6 s1 5 2.4 0.9
7 6 0.8 s1 6 3.2 1.7
8 walkin3 0.1 s1 6 3.2 0.8
9 7 0.9 s1 7 4.1 1.7
10 walkin4 0.4 s1 7 4.1 1.1
11 8 0.4 s1 8 4.5 1.5
12 walkin5 0.5 s1 8 4.5 1.0
13 9 0.6 s1 9 5.1 1.6
14 walkin6 0.4 s1 9 5.1 1.0
请注意，如果列表 walkin_no_show 不够长，这将失败
Note this would fail if the list walkin_no_show is not long enough
这篇关于创建一个函数以根据给定的参数在数据框中创建新行作为 pandas 列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
