我有一个看起来像这样的df:
col1 col2 now previous target
A 1 1-1-2015 4-1-2014 0.2
B 0 2-1-2015 2-5-2014 0.33
A 0 3-1-2013 3-9-2011 0.1
A 1 1-1-2014 4-9-2011 1.7
A 1 31-12-2014 4-9-2014 1.9我按col1和col2对df进行分组,对于每个组中的每个成员,我只想对其他组成员的now日期值小于(早于)当前成员的previous日期值的target值求和。
例如:
col1 col2 now previous target
A 1 1-1-2015 4-1-2014 0.2我想对以下的目标值求和:
col1 col2 now previous target
A 0 3-1-2013 3-9-2011 0.1
A 1 1-1-2014 4-9-2011 1.7最终拥有:
col1 col2 now previous target sum
A 1 1-1-2015 4-1-2014 0.2 1.8发布于 2017-02-21 07:45:27
有趣的问题,我有一些我认为可能有用的东西。虽然Worst case: O(n**3)和Best case: O(n**2)的时间复杂度较低。
设置数据
import pandas as pd
import numpy as np
import io
datastring = io.StringIO(
"""
col1 col2 now previous target
A 1 1-1-2015 4-1-2014 0.2
B 0 2-1-2015 2-5-2014 0.33
A 0 3-1-2013 3-9-2011 0.1
A 1 1-1-2014 4-9-2011 1.7
A 1 31-12-2014 4-9-2014 1.9
C 1 31-12-2014 4-9-2014 1.9
""")
# arguements for pandas.read_csv
kwargs = {
"sep": "\s+", # specifices that it's a space separated file
"parse_dates": [2,3], # parse "now" and "previous" as dates
}
# read the csv into a pandas dataframe
df = pd.read_csv(datastring, **kwargs)算法的伪代码
For each row:
For each *other* row:
If "now" of *other* row comes before "previous" of row
Then add *other* rows "target" to "sum" of row运行算法
首先设置一个函数f(),该函数将应用于由df.groupby(["col1","col2"])计算的所有组。f()所做的就是尝试实现上面的伪代码。
def f(df):
_sum = np.zeros(len(df))
# represent the desired columns of the sub-dataframe as a numpy object
data = df[["now","previous","target"]].values
# loop through the rows in the sub-dataframe, df
for i, outer_row in enumerate(data):
# for each row, loop through all the rows again
for j, inner_row in enumerate(data):
# skip iteration if outer loop row is equal to the inner loop row
if i==j: continue
# get the dates from rows
outer_prev = outer_row[1]
inner_now = inner_row[0]
# if the "previous" datetime of the outer loop is greater than
# the "now" datetime of the inner loop, then add "target" to
# to the cumulative sum
if outer_prev > inner_now:
_sum[i] += inner_row[2]
# add a new column for this new "sum" that we calculated
df["sum"] = _sum
return df现在,只需对分组的数据应用f()。
done = df.groupby(["col1","col2"]).apply(f)输出
col1 col2 now previous target sum
0 A 1 2015-01-01 2014-04-01 0.20 1.7
1 B 0 2015-02-01 2014-02-05 0.33 0.0
2 A 0 2013-03-01 2011-03-09 0.10 0.0
3 A 1 2014-01-01 2011-04-09 1.70 0.0
4 A 1 2014-12-31 2014-04-09 1.90 1.7https://stackoverflow.com/questions/42350367
复制相似问题