Regroup column values in a pandas df

As far as I understand, you're happy with everything before the Person allocation. So here's a plug and play solution to "merge" Persons with less than 3 unique values so each Person ends up with 3 unique values except for the last one obviously (based on the second to last df you posted ("Output:") without touching the ones that have already 3 unique values and just merges the others.

EDIT: Greatly simplified code. Again, taking your df as input:

n = 3
df['complete'] = df.Person.apply(lambda x: 1 if df.Person.tolist().count(x) == n else 0)
df['num'] = df.Person.str.replace('Person ','')
df.sort_values(by=['num','complete'],ascending=True,inplace=True) #get all persons that are complete to the top

c = 0
person_numbers = []
for x in range(0,999): #Create the numbering [1,1,1,2,2,2,3,3,3,...] with n defining how often a person is 'repeated'
    if x % n == 0:
        c += 1        
    person_numbers.append(c) 

df['Person_new'] = person_numbers[0:len(df)] #Add the numbering to the df
df.Person = 'Person ' + df.Person_new.astype(str) #Fill the person column with the new numbering
df.drop(['complete','Person_new','num'],axis=1,inplace=True)

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:

  1. 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?).

  2. 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?).

  3. 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...).

  4. 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?