当特定列中单元格的值为sam时，如何合并pandas数据帧中的行

Question

我有一个数据帧如下：

测向

         KEY    NAME      ID_LOCATION                                                    _GEOM
0       61196   name1    [(u'-88.121429', u'41.887726')]                            [[[lon00,lat00],[lon01, lat01]]]        
1       61197   name2    [(u'-75.161934', u'38.725163')]                            [[[lon10,lat10], [lon11,lat11],...]]
2       61199   name3    [(u'-88.121429', u'41.887726'), (-77.681931, 37.548851)]   [[[lon20, lat20],[lon21, lat21]]]

其中id_loc是元组的列表。我如何按id_loc分组，如果有匹配的(lon, lat) pair，用逗号分隔合并这2行和其他列。在

预期输出

^{pr2}$

我尝试了以下操作，但没有成功，并给出了错误信息unhashable type list：

def f(x):
    return pd.Series(dict(KEY='{%s}' % ', '.join(x['KEY']),
                          NAME='{%s}' % ', '.join(x['NAME']),
                          ID_LOCATION='{%s}' % ', '.join(x['ID_LOCATION']),
                          _GEOM='{%s}' % ', '.join(x['_GEOM']))
                     )
df = df.groupby('ID_LOCATION').apply(f) 

Answer 1

安托万·赞贝利的回答很好；作为练习，也希望它能有所帮助，我想分享一下我个人对这个问题的看法。它还没有完全测试过，但应该可以用。在

# fun to merge elements
def merge_elements(ensemble, column):
    upper_list = []
    for index in ensemble:
        element_list = []
        for item in index:
            if not isinstance(df.loc[item, column], list):
                if not df.loc[item, column] in element_list:
                    element_list.append(df.loc[item, column])
            else:
                for obj in df.loc[item, column]:
                    if not obj in element_list:
                        element_list.append(obj)
        upper_list.append([element_list, index])
    return upper_list

# put results in dataframe
def put_in_df(df, piped, column):
    for elem in piped:
        for i in range(len(elem[1])):
            if column == "NAME" or column == "_GEOM":
                df.loc[elem[1][i], column] = str(elem[0]).replace("'", "")
            else:
                df.loc[elem[1][i], column] = str(elem[0])


# get list from df
list_of_locations = df.ID_LOCATION.tolist()

# get list of rows that need to be merged (no itertools needed)
# the dictionary I used here is an "overkill", I had no actual need for it, so also a common list can suit perfectly
rows = {}
for i, item in enumerate(list_of_locations):
    if isinstance(item, list):
        for j in range(0, len(item)):
            if item[j] in rows:
                rows[item[j]] = [rows[item[j]], i]
            else:
                rows[item[j]] = i
    else:
        if item in rows:
            rows[item] = [rows[item], i]
        else:
            rows[item] = i
ensemble = []

# as I said there was no need for a dictionary, this step can be summarized
for item in rows.values():
    if isinstance(item, list):
        ensemble.append(item)
# conversion to tuple is optional
ensemble = tuple(ensemble)

# merge list of tuples according to indexes retrieved
put_in_df(df, merge_elements(ensemble, "ID_LOCATION"), "ID_LOCATION")
put_in_df(df, merge_elements(ensemble, "NAME"), "NAME")
put_in_df(df, merge_elements(ensemble, "KEYS"), "KEYS")
put_in_df(df, merge_elements(ensemble, "_GEOM"), "_GEOM")

# special thanks to: https://stackoverflow.com/questions/43855462/pandas-drop-duplicates-method-not-working?rq=1
df = df.iloc[df.astype(str).drop_duplicates().index]

正如我在评论中所说的，感谢Pandas drop_duplicates method not working即使在列表中也删除了重复项

Answer 2

我想这应该行得通。在

首先将事物转换成相同类型的列表（这样sum将把事物附加在一起）。在

df = pd.DataFrame(
    [[['61196'], ['name1'], [('-88.121429', '41.887726')]], [['61197'], ['name2'], [('-75.161934', '38.725163')]], [['61199'], ['name3'], [('-88.121429', '41.887726'), ('-77.681931', '37.548851')]]],
    columns=['KEY', 'NAME', 'id_loc']
)

然后得到成对的行组合（对于id_loc），即要合并到一起的行对。在

^{pr2}$

现在处理有3行或更多行的情况，即to_merge = [[1, 2], [2, 3]]应该是to_merge = [[1, 2, 3]]。在

def find_intersection(m_list):
    for i,v in enumerate(m_list) : 
        for j,k in enumerate(m_list[i+1:],i+1):  
           if v & k:
              s[i]=v.union(m_list.pop(j))
              return find_intersection(m_list)
    return m_list

to_merge = [set(i) for i in to_merge if i]
to_merge = find_intersection(to_merge)
to_merge = [list(x) for x in to_merge]

（从this answer找到）

遍历所有需要合并的行并求和（并删除预合并行）

for idx_list in to_merge:
    df.iloc[idx_list[0], :] = df.iloc[idx_list, :].sum()
    df.iloc[idx_list[1:], :] = np.nan

df = df.dropna()
df['id_loc'] = df['id_loc'].apply(lambda x: list(set(x)))  # shared coords would be duped.
print(df)