current attempt
In the following I have added a few lines before last lines of your code:
d = ({'Time': ['8:03:00', '8:17:00', '8:20:00', '10:15:00', '10:15:00', '11:48:00', '12:00:00', '12:10:00'],
'Place': ['House 1', 'House 2', 'House 1', 'House 3', 'House 4', 'House 5', 'House 1', 'House 1'],
'Area': ['X', 'X', 'Y', 'X', 'X', 'X', 'X', 'X']})
df = pd.DataFrame(data=d)
def g(gps):
s = gps['Place'].unique()
d = dict(zip(s, np.arange(len(s)) // 3 + 1))
gps['Person'] = gps['Place'].map(d)
return gps
df = df.groupby('Area', sort=False).apply(g)
s = df['Person'].astype(str) + df['Area']
# added lines
t = s.value_counts()
df_sub = df.loc[s[s.isin(t[t < 3].index)].index].copy()
df_sub["tag"] = df_sub["Place"] + df_sub["Area"]
tags = list(df_sub.tag.unique())
f = lambda x: f'R{int(tags.index(x) / 3) + 1}'
df_sub['reassign'] = df_sub.tag.apply(f)
s[s.isin(t[t < 3].index)] = df_sub['reassign']
df['Person'] = pd.Series(pd.factorize(s)[0] + 1).map(str).radd('Person ')
To be honest I am not so sure it works in all cases, but it gives your intended output in the test case.
Previous attempts
Let's see if I am able to help with a limited understanding of what you are trying to do.
You have sequential data (I'll call them events) and you want to assign to each event a "person" identifier. The identifier you will assign on each successive event depends on previous assignments and it seems to me it need to be governed by the following rules to be applied sequentially:
I know you: I can reuse a previous identifier if: same values for "Place" and "Area" already appeared for a given identifier (has time something to do with it?).
I do NOT know you: I will create a new identifier if: a new value of Area appears (so Place and Area play different roles?).
do I know you?: I might reuse a previously used identifier if: an identifier has not been assigned to at least three events (what if this happens for multiple identifiers? I will assume I use the oldest...).
nah, I don't: in case none of the preceding rules apply, I will create a new identifier.
Having assumed the above the following is an implementation of a solution:
# dict of list of past events assigned to each person. key is person identifier
people = dict()
# new column for df (as list) it will be appended at the end to dataframe
persons = list()
# first we define the rules
def i_know_you(people, now):
def conditions(now, past):
return [e for e in past if (now.Place == e.Place) and (now.Area == e.Area)]
i_do = [person for person, past in people.items() if conditions(now, past)]
if i_do:
return i_do[0]
return False
def i_do_not_know_you(people, now):
conditions = not bool([e for past in people.values() for e in past if e.Area == now.Area])
if conditions:
return f'Person {len(people) + 1}'
return False
def do_i_know_you(people, now):
i_do = [person for person, past in people.items() if len(past) < 3]
if i_do:
return i_do[0]
return False
# then we process the sequential data
for event in df.itertuples():
print('event:', event)
for rule in [i_know_you, i_do_not_know_you, do_i_know_you]:
person = rule(people, event)
print('\t', rule.__name__, person)
if person:
break
if not person:
person = f'Person {len(people) + 1}'
print('\t', "nah, I don't", person)
if person in people:
people[person].append(event)
else:
people[person] = [event]
persons.append(person)
df['Person'] = persons
Output:
event: Pandas(Index=0, Time='8:00:00', Place='House 1', Area='X', Person='Person 1')
i_know_you False
i_do_not_know_you Person 1
event: Pandas(Index=1, Time='8:30:00', Place='House 2', Area='X', Person='Person 1')
i_know_you False
i_do_not_know_you False
do_i_know_you Person 1
event: Pandas(Index=2, Time='9:00:00', Place='House 1', Area='Y', Person='Person 2')
i_know_you False
i_do_not_know_you Person 2
event: Pandas(Index=3, Time='9:30:00', Place='House 3', Area='X', Person='Person 1')
i_know_you False
i_do_not_know_you False
do_i_know_you Person 1
event: Pandas(Index=4, Time='10:00:00', Place='House 4', Area='X', Person='Person 2')
i_know_you False
i_do_not_know_you False
do_i_know_you Person 2
event: Pandas(Index=5, Time='10:30:00', Place='House 5', Area='X', Person='Person 2')
i_know_you False
i_do_not_know_you False
do_i_know_you Person 2
event: Pandas(Index=6, Time='11:00:00', Place='House 1', Area='X', Person='Person 1')
i_know_you Person 1
event: Pandas(Index=7, Time='11:30:00', Place='House 6', Area='X', Person='Person 3')
i_know_you False
i_do_not_know_you False
do_i_know_you False
nah, I don't Person 3
event: Pandas(Index=8, Time='12:00:00', Place='House 7', Area='X', Person='Person 3')
i_know_you False
i_do_not_know_you False
do_i_know_you Person 3
event: Pandas(Index=9, Time='12:30:00', Place='House 8', Area='X', Person='Person 3')
i_know_you False
i_do_not_know_you False
do_i_know_you Person 3
and the final dataframe is, as you want:
Time Place Area Person
0 8:00:00 House 1 X Person 1
1 8:30:00 House 2 X Person 1
2 9:00:00 House 1 Y Person 2
3 9:30:00 House 3 X Person 1
4 10:00:00 House 4 X Person 2
5 10:30:00 House 5 X Person 2
6 11:00:00 House 1 X Person 1
7 11:30:00 House 6 X Person 3
8 12:00:00 House 7 X Person 3
9 12:30:00 House 8 X Person 3
Remark: Note that I intentionally avoided using grouped by operations and processed data sequentially. I think this kind of complexity (and not really understanding what you want to do...) calls for that approach. Also, you can adapt the rules to be more complicated (is time really playing a role or not?) using the same structure above.
Updated answer for new data
Looking at new data it is evident I did not understand what you are trying to do (in particular, the assignement does not seem to follow sequential rules).
I would have a solution that would work on your second dataset, but it would give a different result for the first dataset.
The solution is much simpler and will add a column (that you can drop later if you want):
df["tag"] = df["Place"] + df["Area"]
tags = list(df.tag.unique())
f = lambda x: f'Person {int(tags.index(x) / 3) + 1}'
df['Person'] = df.tag.apply(f)
On the second dataset, it would give:
Time Place Area tag Person
0 8:00:00 House 1 X House 1X Person 1
1 8:30:00 House 2 X House 2X Person 1
2 9:00:00 House 3 X House 3X Person 1
3 9:30:00 House 1 Y House 1Y Person 2
4 10:00:00 House 1 Z House 1Z Person 2
5 10:30:00 House 1 V House 1V Person 2
On the first dataset it gives:
Time Place Area tag Person
0 8:00:00 House 1 X House 1X Person 1
1 8:30:00 House 2 X House 2X Person 1
2 9:00:00 House 1 Y House 1Y Person 1
3 9:30:00 House 3 X House 3X Person 2
4 10:00:00 House 4 X House 4X Person 2
5 10:30:00 House 5 X House 5X Person 2
6 11:00:00 House 1 X House 1X Person 1
7 11:30:00 House 6 X House 6X Person 3
8 12:00:00 House 7 X House 7X Person 3
9 12:30:00 House 8 X House 8X Person 3
This is different from your intended output on index 2 and 3. Is this output fine with your requirement? Why not?
