基于特定条件创建新行并迭代 pandas 中的列表 [英] create new rows based specific condition and iterate over a list in pandas
问题描述
我有一个如下所示的 df
I have a df as shown below
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
在df上面创建的代码如下所示.
The code to create above df is shown below.
import pandas as pd
import numpy as np
df = pd.DataFrame({'B_ID': [1,2,3,4,5,6,7,8,9,12,13,14,15,20,16,17,19],
'No_Show': [0.4,0.3,0.8,0.3,0.6,0.8,0.9,0.4,0.6,0.9,0.5,0.3,0.7,0.7,0.6,0.8,0.3],
'Session': ['s1','s1','s1','s1','s1','s1','s1','s1','s1','s2','s2','s2','s2','s2','s2','s2','s2'],
'slot_num': [1,2,3,4,5,6,7,8,9,1,2,3,4,5,6,7,8],
})
df['Cumulative_no_show'] = df.groupby(['Session'])['No_Show'].cumsum()
和一个名为 walkin_no_show = [ 0.3, 0.4, 0.3, 0.4, 0.3, 0.4 等等的列表，长度为 1000]
and a list called walkin_no_show = [ 0.3, 0.4, 0.3, 0.4, 0.3, 0.4 and so on with length 1000]
从上面当 u_cumulative > 0.8 创建一个新行就在其下方
From the above when ever u_cumulative > 0.8 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.
预期输出:
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.4 S1 4 1.8 0.5
5 0.6 S1 5 2.4 1.1
walkin3 0.3 S1 5 2.4 0.4
6 0.8 S1 6 3.2 1.2
walkin4 0.4 S1 6 3.2 0.6
7 0.9 S1 7 4.1 1.5
walkin5 0.3 S1 7 4.1 0.8
8 0.4 S1 8 4.5 1.2
walkin6 0.4 S1 8 4.5 0.6
9 0.6 S1 9 5.1 1.2
12 0.9 S2 1 0.9 0.9
walkin1 0.3 S2 1 0.9 0.2
13 0.5 S2 2 1.4 0.7
14 0.3 S2 3 1.7 1.0
walkin2 0.4 S2 3 1.7 0.4
15 0.7 S2 4 2.4 1.1
walkin3 0.3 S2 4 2.4 0.4
20 0.7 S2 5 3.1 1.1
walkin4 0.4 S2 5 3.1 0.5
16 0.6 S2 6 3.7 1.1
walkin5 0.3 S2 6 3.7 0.4
17 0.8 S2 7 4.5 1.2
walkin6 0.4 S2 7 4.5 0.6
19 0.3 S2 8 4.8 0.9
我尝试了下面的代码小修改.正如@Ben.T 在下面提到的我的问题所回答的那样.
I tried below code minor edit. As answered by @Ben.T on the below mentioned my question.
谢谢@Ben.T.完全归功于你..
Thanks @Ben.T. Full credit to you..
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-walkin_no_show[arr_idx])
# 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)
# 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[mask_idx]
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
new_df_0.8 = pd.concat([df,df_toAdd]).sort_index()\
.reset_index(drop=True).drop('mask_idx', axis=1)
我还想遍历一个列表.我们可以在其中更改 (arr_ns[i]>0.8) [0.8, 0.9, 1.0] 并创建 3 个 df，例如 new_df_0.8、new_df_0.9 和 new_df_1.0
Also I would like to iterarate over a list. where we can change (arr_ns[i]>0.8) [0.8, 0.9, 1.0] and create 3 df such as new_df_0.8, new_df_0.9 and new_df_1.0
推荐答案
IIUC，你可以这样做:
IIUC, you can do it this way:
def create_u_columns (ser, threshold_ns = 0.8):
arr_ns = ser.to_numpy()
# 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 to u_no_show
arr_ns[i+1:] -= (1-walkin_no_show[walkin_id]) #this is slightly different
# increment later idx to add
arr_idx[i] = walkin_id+1
walkin_id +=1
#return a dataframe with both columns
return pd.DataFrame({'u_cumulative': arr_ns, 'mask_idx':arr_idx}, index=ser.index)
#create empty dict for storing the dataframes
d_dfs = {}
#iterate over the value for the threshold
for th_ns in [0.8, 0.9, 1.0]:
#create a copy and do the same kind of operation
df_ = df.copy()
df_[['u_cumulative', 'mask_idx']]= \
df_.groupby(['Session'])['Cumulative_no_show']\
.apply(lambda x: create_u_columns(x, threshold_ns=th_ns))
# select the rows
df_toAdd = df_.loc[df_['mask_idx'].astype(bool), :].copy()
# replace the values as wanted
df_toAdd['No_Show'] = np.array(walkin_no_show)[df_toAdd.groupby('Session').cumcount()]
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
d_dfs[th_ns] = pd.concat([df_,df_toAdd]).sort_index()\
.reset_index(drop=True).drop('mask_idx', axis=1)
然后如果你想访问数据帧，你可以这样做:
Then if you want to have access to the dataframes, you can do for example:
for th, df_ in d_dfs.items():
print (th)
print (df_.head(4))
这篇关于基于特定条件创建新行并迭代 pandas 中的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
