I have a dataframe, dt as given below. Machine M1 has three possible start and end time and so does M2 and M3.
MID ST ET
M1 11/7/2021 4:52 11/7/2021 6:22
M1 11/7/2021 6:22 11/7/2021 7:52
M1 11/7/2021 7:52 11/7/2021 9:22
M2 11/7/2021 4:54 11/7/2021 6:24
M2 11/7/2021 6:24 11/7/2021 7:54
M2 11/7/2021 7:54 11/7/2021 9:24
M3 11/7/2021 4:56 11/7/2021 6:26
M3 11/7/2021 6:26 11/7/2021 7:56
M3 11/7/2021 7:56 11/7/2021 9:26
I want to find a combination of start and end time for each machine in the form of dataframe. Like if I fix the value of ST and ET of M2 and M3 respectively and vary M1 only three combinations possible are as below. I want all such combinations in a dataframe like df with unique ID for each combination (including the above analysis for M2 and M3).
Combination C1
MID ST ET
M1 11/7/2021 4:52 11/7/2021 6:22
M2 11/7/2021 4:54 11/7/2021 6:24
M3 11/7/2021 4:56 11/7/2021 6:26
Combination C2
MID ST ET
M1 11/7/2021 6:22 11/7/2021 7:52
M2 11/7/2021 4:54 11/7/2021 6:24
M3 11/7/2021 4:56 11/7/2021 6:26
Combination C3
MID ST ET
M1 11/7/2021 7:52 11/7/2021 9:22
M2 11/7/2021 4:54 11/7/2021 6:24
M3 11/7/2021 4:56 11/7/2021 6:26
Dataframe, df (Final dataframe will be this):
MID ST ET Cname
M1 11/7/2021 4:52 11/7/2021 6:22 C1
M2 11/7/2021 4:54 11/7/2021 6:24 C1
M3 11/7/2021 4:56 11/7/2021 6:26 C1
M1 11/7/2021 6:22 11/7/2021 7:52 C2
M2 11/7/2021 4:54 11/7/2021 6:24 C2
M3 11/7/2021 4:56 11/7/2021 6:26 C2
M1 11/7/2021 7:52 11/7/2021 9:22 C3
M2 11/7/2021 4:54 11/7/2021 6:24 C3
M3 11/7/2021 4:56 11/7/2021 6:26 C3
M1 11/7/2021 4:52 11/7/2021 6:22 C4
M2 11/7/2021 6:24 11/7/2021 7:54 C4
M3 11/7/2021 4:56 11/7/2021 6:26 C4
M1 11/7/2021 6:22 11/7/2021 7:52 C5
M2 11/7/2021 6:24 11/7/2021 7:54 C5
M3 11/7/2021 4:56 11/7/2021 6:26 C5
M1 11/7/2021 7:52 11/7/2021 9:22 C6
M2 11/7/2021 6:24 11/7/2021 7:54 C6
M3 11/7/2021 4:56 11/7/2021 6:26 C6
M1 11/7/2021 4:52 11/7/2021 6:22 C7
M2 11/7/2021 7:54 11/7/2021 9:24 C7
M3 11/7/2021 4:56 11/7/2021 6:26 C7
M1 11/7/2021 6:22 11/7/2021 7:52 C8
M2 11/7/2021 7:54 11/7/2021 9:24 C8
M3 11/7/2021 4:56 11/7/2021 6:26 C8
M1 11/7/2021 7:52 11/7/2021 9:22 C9
M2 11/7/2021 7:54 11/7/2021 9:24 C9
M3 11/7/2021 4:56 11/7/2021 6:26 C9
M1 11/7/2021 4:52 11/7/2021 6:22 C10
M2 11/7/2021 7:54 11/7/2021 9:24 C10
M3 11/7/2021 6:26 11/7/2021 7:56 C10
M1 11/7/2021 6:22 11/7/2021 7:52 C11
M2 11/7/2021 7:54 11/7/2021 9:24 C11
M3 11/7/2021 6:26 11/7/2021 7:56 C11
M1 11/7/2021 7:52 11/7/2021 9:22 C12
M2 11/7/2021 7:54 11/7/2021 9:24 C12
M3 11/7/2021 6:26 11/7/2021 7:56 C12
M1 11/7/2021 4:52 11/7/2021 6:22 C13
M2 11/7/2021 7:54 11/7/2021 9:24 C13
M3 11/7/2021 7:56 11/7/2021 9:26 C13
M1 11/7/2021 6:22 11/7/2021 7:52 C14
M2 11/7/2021 7:54 11/7/2021 9:24 C14
M3 11/7/2021 7:56 11/7/2021 9:26 C14
M1 11/7/2021 7:52 11/7/2021 9:22 C15
M2 11/7/2021 7:54 11/7/2021 9:24 C15
M3 11/7/2021 7:56 11/7/2021 9:26 C15
What I have tried?
I have used combination and product functions in itertools but didn't get desired result. I have also tried groupby.
from itertools import product
df = pd.DataFrame(list(product(*dt.values.T)))
CodePudding user response：
Try with groupby and itertools.product:
df["UID"] = df["MID"] (df.groupby("MID").transform("cumcount") 1).astype(str)
combinations = pd.Series(list(itertools.product(*df.groupby("MID")["UID"].agg(list).tolist())), name="UID")
combinations = combinations.explode()
output = df.merge(combinations.reset_index(), how="right", on="UID")
output["CName"] = "C" (output["index"] 1).astype(str)
output = output.drop(["UID", "index"], axis=1)
>>> output
MID ST ET CName
0 M1 2021-11-07 04:52:00 2021-11-07 06:22:00 C1
1 M2 2021-11-07 04:54:00 2021-11-07 06:24:00 C1
2 M3 2021-11-07 04:56:00 2021-11-07 06:26:00 C1
3 M1 2021-11-07 04:52:00 2021-11-07 06:22:00 C2
4 M2 2021-11-07 04:54:00 2021-11-07 06:24:00 C2
.. .. ... ... ...
76 M2 2021-11-07 07:54:00 2021-11-07 09:24:00 C26
77 M3 2021-11-07 06:26:00 2021-11-07 07:56:00 C26
78 M1 2021-11-07 07:52:00 2021-11-07 09:22:00 C27
79 M2 2021-11-07 07:54:00 2021-11-07 09:24:00 C27
80 M3 2021-11-07 07:56:00 2021-11-07 09:26:00 C27