有没有一种python方法来合并多个单元格与条件 [英] Is there a python way to merge multiple cells with condition

问题描述

我需要在多个单元格中搜索特定值，当找到该值时，应将其返回到新列中.

I needed to search multiple cells for a specific value and when it is found it should be returned in new column.

我在这里得到了答案； Python:在多列中找到字符串，然后在新列中返回，但是下面的这一行返回找到的第一个值

I got an answer here; Python: find string in multiple columns and return it in new column yet this line below return the first value found

df['b'] = (df[cols].where(df[cols].stack().str.contains('b')
         .unstack(fill_value=False)).ffill(1).iloc[:,-1])

cols

df = df[['col1', 'col2', 'col3', 'col4']]

我尝试了其他答案，它们都给了我错误ValueError: cannot reindex from a duplicate axis

I tried the other answers and they all gave me error ValueError: cannot reindex from a duplicate axis

任何人都知道如何在一个单元格中获得所有匹配的值.

Do any one have an idea how can I get all the matching values in one cell.

数据集

ID   col0  col1  col2  col3  col4  col5
1    jack  a/h   t/m   w/n   y/h    56
2    sam   z/n   b/w   null  b/n   93
3    john  b/i   y/d   b/d   null   33

我现在正在使用的代码:

The code I'm using now :

df['b'] = (df[cols].where(df[cols].stack().str.contains('b')
         .unstack(fill_value=False)).ffill(1).iloc[:,-1])

这是我现在得到的输出

ID   col0  col1  col2  col3  col4  col5  b
1    jack  a/h   t/m   w/n   y/h    56   -
2    sam   z/n   b/w   null  b/n   93   b/w
3    john  b/i   y/d   b/d   null   33   b/i

实际上我希望输出看起来像下面的数据框

And actually I want the output to look like the data-frame below

ID   col0  col1  col2  col3  col4  col5     b 
1    jack  a/h   t/m   w/n   y/h    56    null
2    sam   z/n   b/w   null  b/n    93   b/w - b/n
3    john  b/i   y/d   b/d   null   33   b/i - b/d

推荐答案

使用level=0上/reference/api/pandas.Series.groupby.html"rel =" nofollow noreferrer> Series.groupby 并使用join进行聚合:

Use DataFrame.filter to filter dataframe containing columns col1-col4 and use DataFrame.stack, then using Series.str.contains filter the stacked dataframe finally use Series.groupby on level=0 and aggregate using join:

s = df.filter(regex=r'col[1-4]').stack()
s = s[s.str.contains('b')].groupby(level=0).agg(' - '.join)
df['b'] = s

结果:

# print(df)

   ID  col0 col1 col2 col3 col4  col5          b
0   1  jack  a/h  t/m  w/n  y/h    56        NaN
1   2   sam  z/n  b/w  NaN  b/n    93  b/w - b/n
2   3  john  b/i  y/d  b/d  NaN    33  b/i - b/d

这篇关于有没有一种python方法来合并多个单元格与条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！