如何根据另一列的特定值对python数据帧进行操作?

Question

我是python数据分析的新手。下面是一个示例数据集：

d2 = {'Index': [0,0,0,0,0,0,0,0,1,1,1,1,1,1,1,1], 'journey_time':[95.546,132.945,147.538,301.307,42.907,129.008,102.900,112.620,234.334,103.321,82.337,154.817,20.076,85.717,94.362,45.032],'edge':['s_b','c_d','b_d','c_e','d_f','s_a','a_c','d_c','c_e','a_c','d_c','s_a','d_f','s_b','b_d','c_d']}
df2=pd.DataFrame(data=d2)

我想创建一个新的数据框，其中每个索引都有一行，其中包含新列。新列的规则如下：

se1 = s_a + a_c + c_e
se2 = s_b + b_d + d_c + c_e
sf1 = s_b + b_d + d_f
sf2 = s_a + a_c + c_d + d_f 

此外，我的计算还有其他变化，例如

eq_time1 = (200/(s_a + a_c)) + c_e
eq_time2 = (200/(s_b + b_d + d_c)) + c_e 

规则中边的值是每个唯一索引的相应行程时间。我不确定如何在python数据帧中编写它。以下是我的预期输出：

df3 = {'Index':[0,1],'se1':[129.008+102.900+301.307,154.817+103.321+234.334],'se2':[95.546+147.538+112.620+301.307,85.717+94.362+82.337+234.334],'sf1':[95.546+147.538+42.907,85.717+94.362+20.076],'sf2':[129.008+102.900+132.945+42.907,154.817+103.321+45.032+20.076 ],'eq_time1':[(200/(129.008+102.900))+301.307,(200/(154.817+103.321))+234.334   ], 'eq_time2' : [(200/(95.546+147.538+112.620))+301.307,(200/(85.717+94.362+82.337))+234.334]}

请帮忙！

Answer 1

如果你的数据中只有这 4 条路径，你可以按如下方式计算熊猫的时间：

paths = {
'se1': ['s_a', 'a_c', 'c_e'],
'se2': ['s_b', 'b_d', 'd_c', 'c_e'],
'sf1': ['s_b', 'b_d', 'd_f'],
'sf2': ['s_a', 'a_c', 'c_d', 'd_f']
}
paths = {
'se1': ['s_a', 'a_c', 'c_e'],
'se2': ['s_b', 'b_d', 'd_c', 'c_e'],
'sf1': ['s_b', 'b_d', 'd_f'],
'sf2': ['s_a', 'a_c', 'c_d', 'd_f']
}
df3 = pd.DataFrame({'Index': df2['Index'].unique()}).set_index('Index')
for k, v in paths.items():
df3[k] = df2[df2.edge.isin(v)].groupby('Index')['journey_time'].sum()
last_edge_times = df2[df2.edge==v[-1]].set_index('Index')
df3['eq_time_'+k] = 200.0/(df3[k] - last_edge_times.journey_time) + last_edge_times.journey_time

对于任何路径p，eq_time_p列存储方程给出的eq_times。