重新组合熊猫 df 中的列值

Question

我有一个script是分配价值为基础的假两件columns的pandas df。下面的代码能够实现第一步，但我正在为第二步而苦苦挣扎。所以脚本最初应该：1）分配Person为每个单独的string在[Area]与所述第一3 unique values中[Place]2）寻找重新分配People小于3 unique values 示例。在df下面有6 unique values中[Area]和[Place]。但是3 People被分配了。理想情况下，2人们将2 unique values每个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 gpsdf = df.groupby('Area', sort=False).apply(g)s = df['Person'].astype(str) + df['Area']df['Person'] = pd.Series(pd.factorize(s)[0] + 1).map(str).radd('Person ')输出：       Time    Place Area    Person0   8:03:00  House 1    X  Person 11   8:17:00  House 2    X  Person 12   8:20:00  House 1    Y  Person 23  10:15:00  House 3    X  Person 14  10:15:00  House 4    X  Person 35  11:48:00  House 5    X  Person 36  12:00:00  House 1    X  Person 17  12:10:00  House 1    X  Person 1如您所见，第一步工作正常。或者每个人stringin [Area]，第一个3 unique valuesin[Place]都分配给一个Person。这使得Person 1有3 values，Person 2与1 value和Person 3带2 values。第二步是我挣扎的地方。如果 aPerson少于3 unique values分配给他们，请更改此设置，以便每个Person人最多3 unique values预期输出：       Time    Place Area    Person0   8:03:00  House 1    X  Person 11   8:17:00  House 2    X  Person 12   8:20:00  House 1    Y  Person 23  10:15:00  House 3    X  Person 14  10:15:00  House 4    X  Person 25  11:48:00  House 5    X  Person 26  12:00:00  House 1    X  Person 17  12:10:00  House 1    X  Person 1

Answer 1

据我了解，您对 Person 分配之前的一切都感到满意。所以这里有一个即插即用的解决方案来“合并”少于 3 个唯一值的人，所以每个人最终都有 3 个唯一值，除了最后一个显然（基于你发布的倒数第二个 df（“输出：”），没有触摸那些已经有 3 个唯一值的值，然后合并其他值。

编辑：大大简化的代码。同样，将您的 df 作为输入：

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)

Answer 2

第 2 步的情况如何：

def reduce_df(df):

    values = df['Area'] + df['Place']

    df1 = df.loc[~values.duplicated(),:] # ignore duplicate values for this part..

    person_count = df1.groupby('Person')['Person'].agg('count')

    leftover_count = person_count[person_count < 3] # the 'leftovers'

    # try merging pairs together

    nleft = leftover_count.shape[0]

    to_try = np.arange(nleft - 1)

    to_merge = (leftover_count.values[to_try] + 

                leftover_count.values[to_try + 1]) <= 3

    to_merge[1:] = to_merge[1:] & ~to_merge[:-1]

    to_merge = to_try[to_merge]

    merge_dict = dict(zip(leftover_count.index.values[to_merge+1], 

                    leftover_count.index.values[to_merge]))

    def change_person(p):

        if p in merge_dict.keys():

            return merge_dict[p]

        return p

    reduced_df = df.copy()

    # update df with the merges you found

    reduced_df['Person'] = reduced_df['Person'].apply(change_person)

    return reduced_df

print(

    reduce_df(reduce_df(df)) # call twice in case 1,1,1 -> 2,1 -> 3

)

输出：

Area    Place      Time    Person

0    X  House 1   8:03:00  Person 1

1    X  House 2   8:17:00  Person 1

2    Y  House 1   8:20:00  Person 2

3    X  House 3  10:15:00  Person 1

4    X  House 4  10:15:00  Person 2

5    X  House 5  11:48:00  Person 2

6    X  House 1  12:00:00  Person 1

7    X  House 1  12:10:00  Person 1