想象一個在不同公司作業過的員工，你想知道他在每家公司作業了多少個月。問題是他可能在兩家公司作業過，有一段時間重疊（公司 D 和 C 的情況）。該重疊時間不應包括在內。還有一點就是他作業的每個月都算一個月，不管他作業多長時間，比如在A公司，工期都是3個月（就像他在1、2、3個月作業一樣），雖然實際持續時間為 2 個月。
Company Start End Duration (months)
A 2021-01-01 2021-03-01
B 2021-03-03 2021-06-07
C 2021-06-10 2021-08-28
D 2021-04-10 2021-10-02
考慮到我在這里收到的輸入，我將代碼更改如下：
import io
import pandas as pd
import numpy as np
import calendar
from datetime import datetime
from dateutil.relativedelta import relativedelta
data = io.StringIO("""
Company,Start,End
A,2021-01-01,2021-03-01
B,2021-03-03,2021-06-07
C,2021-06-10,2021-08-28
D,2021-04-10,2021-10-02
""")
df = pd.read_csv(data)
def to_datetime(date):
return datetime.strptime(date, "%Y-%m-%d")
df["Start"] = df["Start"].apply(to_datetime)
df["End"] = df["End"].apply(to_datetime)
for row in df:
overlapping = df.Start<df.End.shift()
df['Overlapping'] = overlapping
if not overlapping.empty:
duplicates = df.End.shift() - df.Start
df['Duplicates'] = duplicates/np.timedelta64(1, 'M')
else:
df['Duplicates'] = 0
print(df)
問題是我不明白為什么在遵循 else 條件時“重復”不為零。它們顯示為陰性。
輸出：
Company Start End Overlapping Duplicates
0 A 2021-01-01 2021-03-01 False NaN
1 B 2021-03-03 2021-06-07 False -0.065710
2 C 2021-06-10 2021-08-28 False -0.098565
3 D 2021-04-10 2021-10-02 True 4.599684
謝謝
uj5u.com熱心網友回復：
問題是他可能在兩家公司作業過，有一段時間重疊（公司 D 和 C 的情況）。該重疊時間不應包括在內。
我是這樣讀的：在 2 個或更多公司作業時不考慮時間。
下面的示例遵循此要求。但是，在此答案的后面，有一個示例至少考慮了重疊時間一次。
你可以這樣做：
import io
import pandas as pd
from datetime import datetime
# mock data / simulating CSV file
data = io.StringIO("""
Company,Start,End
A,2021-01-01,2021-03-01
B,2021-03-03,2021-06-07
C,2021-06-10,2021-08-28
D,2021-04-10,2021-10-02
""")
df = pd.read_csv(data)
def to_datetime(date):
return datetime.strptime(date, "%Y-%m-%d")
df["Start"] = df["Start"].apply(to_datetime)
df["End"] = df["End"].apply(to_datetime)
def get_full_months(start, end):
""" Returns the number of months including
non-full months. """
if end.year == start.year:
return end.month - start.month 1
months = (end.year - start.year - 1) * 12
return (12 - start.month 1) end.month months
def evaluate_duration(start, end):
""" Evaluates the duration taking other employment periods
into account. Overlapping periods are not considered
at all. Single days within a month are rounded up to
a full month. """
left = df[(df["End"] > start) & (df["Start"] < start)]
right = df[(df["Start"] > start) & (df["End"] > end)]
overlaps = df[(df["Start"] < start) & (df["End"] > end)]
within = df[(df["Start"] > start) & (df["End"] < end)]
if not left.empty:
start = left["End"].max()
if not right.empty:
end = right["Start"].min()
if not overlaps.empty:
# if there are any records which overlaps the current
# period full, the current period will not be considered
return 0
if not within.empty:
# if there are other records within the current period,
# these months need to be removed since overlapping
duration = df.apply(
lambda row: get_full_months(row["Start"], row["End"]),
axis=1
)
return get_full_months(start, end) - duration.sum()
return get_full_months(start, end)
df["Duration"] = df.apply(
lambda row: evaluate_duration(row["Start"], row["End"]),
axis=1
)
輸出為print(df)：
Company Start End Duration
0 A 2021-01-01 2021-03-01 3
1 B 2021-03-03 2021-06-07 2
2 C 2021-06-10 2021-08-28 0
3 D 2021-04-10 2021-10-02 2
解釋：
Considered Not Considered Why
A: Jan to Mrz
B: Mrz, Apr May, Jun Overlapped on the right with D
NB: Mrz and Apr are not (fully) overlapped
C: Jun to Aug Fully overlapped by D
D: Sep, Oct Apr to Aug Overlapped on the left with B, overlaps C
您需要考慮 4 種不同的重疊情況：
Left Right
|-------| |------| df["Start"] and df["End"]
|***ooooo| |oooooo***| start and end
^ ^
left["End"].max() right["Start"].min()
Overlaps Within
|----------| |------| df["Start"] and df["End"]
|*****| |oo******oooooo| start and end
Legend
|----| employment period
oooo considered duration
*** ignored date range within employment period
在您的評論之一中，您寫道：
[...] 不包括重疊，這意味著持續時間應該只計算一次。[...]
If you need overlapping time at least once being considered, change the function evaluate_duration to:
def evaluate_duration(start, end):
left = df[(df["End"] > start) & (df["Start"] < start)]
overlaps = df[(df["Start"] < start) & (df["End"] > end)]
if not left.empty:
start = left["End"].max()
if not overlaps.empty:
return 0
return get_full_months(start, end)
Output for print(df) would be:
Company Start End Duration
0 A 2021-01-01 2021-03-01 3
1 B 2021-03-03 2021-06-07 4
2 C 2021-06-10 2021-08-28 0
3 D 2021-04-10 2021-10-02 5
Explanation:
Considered Not Considered Why
A: Jan to Mrz
B: Mrz to Jun Note: Mrz does not overlap
C: Jun to Aug Fully overlapped by D, therefore ignored
D: Jun to Oct Apr, May Jun only partially overlaps with B
Another thing is that in the each month he worked, it counts as one month, no matter how long he worked [...]
You can achieve this with:
def get_full_months(start, end):
""" Returns the number of months including
non-full months. """
if end.year == start.year:
return end.month - start.month 1
months = (end.year - start.year - 1) * 12
return (12 - start.month 1) end.month months
Please note: The code above may not be fully tested, especially not with edge cases and other data than the provided example data.
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/321368.html