从 pandas 数据框中提取相关行，当存在重复列数值时。

huangapple
go评论101阅读模式
英文:

Extract relevant rows from pandas dataframe when duplicate column values are present

问题

我有一个如下的pandas数据框架：

id left top width height Text
1 12 34 12 34 commercial
2 99 42 99 42 general
3 1 47 9 4 liability
4 10 69 32 67 commercial
5 99 72 79 88 available

我想要根据列值Text 提取特定行。所以我想要在列Text中使用re.search搜索某些关键词短语，例如liability commercial，如果匹配成功，则提取相应的行，即第3行和第4行。因此，如果输入是liability commercial，那么输出应该是以下提取的行：

id left top width height Text
3 1 47 9 4 liability
4 10 69 32 67 commercial

请注意，列Text 可能包含重复值。所以在上面的情况中，有2行包含单词commercial。

英文:

I have a pandas data frame as follows:

id left top width height Text
1 12 34 12 34 commercial
2 99 42 99 42 general
3 1 47 9 4 liability
4 10 69 32 67 commercial
5 99 72 79 88 available

I want to extract specific rows based on the column value Text. So I want to search for certain keyphrases like liability commercial using re.search in the column Text and if I get a match then extract the rows i.e. 3rd and 4th row. So if the input is liability commercial then the output should be the following rows extracted:

id left top width height Text
3 1 47 9 4 liability
4 10 69 32 67 commercial

Keep in mind that the column Text may contain duplicate values. So in the above case, there are 2 rows with the word commerial present.

Thanks in advance!

答案1

得分: 1

以下是翻译好的代码部分：

  1. 使用：
  3. phrase = 'liability commercial'
  5. #按子字符串匹配 - 使用空格分隔的值
  6. m = df['Text'].str.contains(phrase.replace(' ','|'))
  7. #按使用空格分隔的值匹配
  8. m = df['Text'].isin(phrase.split())
  10. #根据掩码筛选行并获取Text列中的最后重复值
  11. df = df[m].drop_duplicates(['Text'], keep='last')
  12. print (df)
  13.    id  left  top  width  height        Text
  14. 2   3     1   47      9       4   liability
  15. 3   4    10   69     32      67  commercial
  17. 或者，如果需要根据匹配行分组，可以更改条件掩码，这里不考虑分割值的位置和可能的重复项：
  19. phrase = 'liability commercial'
  20. m = ~df['Text'].str.contains(phrase.replace(' ','|'))
  21. #m = ~df['Text'].isin(phrase.split())
  23. df = df[m.cumsum().duplicated(keep=False) & ~m]
  24. print (df)
  25.    id  left  top  width  height        Text
  26. 2   3     1   47      9       4   liability
  27. 3   4    10   69     32      67  commercial
  29. 如果需要根据分割值的确切匹配进行匹配，可以修改[此解决方案](https://stackoverflow.com/a/49005205/2901002)：
  31. phrase = 'liability commercial'
  33. #https://stackoverflow.com/a/49005205/2901002
  34. pat = np.asarray(phrase.split())
  35. N = len(pat)
  37. def rolling_window(a, window):
  38.     shape = a.shape[:-1] + (a.shape[-1] - window + 1, window)
  39.     strides = a.strides + (a.strides[-1],)
  40.     c = np.lib.stride_tricks.as_strided(a, shape=shape, strides=strides)
  41.     return c
  43. arr = df['Text'].values
  44. b = np.all(rolling_window(arr, N) == pat, axis=1)
  45. c = np.mgrid[0:len(b)][b]
  47. d = [i  for x in c for i in range(x, x+N)]
  48. df = df[np.in1d(np.arange(len(arr)), d)]
  49. print (df)
  50.    id  left  top  width  height        Text
  51. 2   3     1   47      9       4   liability
  52. 3   4    10   69     32      67  commercial

希望这对你有所帮助！

英文:

Use:

  1. phrase = 'liability commercial'
  2. #match by substrings - splitted values by spaces
  3. m = df['Text'].str.contains(phrase.replace(' ','|'))
  4. #match by splitted values by spaces
  5. m = df['Text'].isin(phrase.split())
  6. #filter rows by mask and get last duplicated values in Text column
  7. df = df[m].drop_duplicates(['Text'], keep='last')
  8. print (df)
  9. id  left  top  width  height        Text
  10. 2   3     1   47      9       4   liability
  11. 3   4    10   69     32      67  commercial

Or if need groups by matched rows by conditions change mask, here position of splitted values ad possible duplicates not counts:

  1. phrase = 'liability commercial'
  2. m = ~df['Text'].str.contains(phrase.replace(' ','|'))
  3. #m = ~df['Text'].isin(phrase.split())
  4. df = df[m.cumsum().duplicated(keep=False) & ~m]
  5. print (df)
  6. id  left  top  width  height        Text
  7. 2   3     1   47      9       4   liability
  8. 3   4    10   69     32      67  commercial

If need match by exactly matched by splitted values is possible modify this solution:

  1. phrase = 'liability commercial'
  2. #https://stackoverflow.com/a/49005205/2901002
  3. pat = np.asarray(phrase.split())
  4. N = len(pat)
  5. def rolling_window(a, window):
  6. shape = a.shape[:-1] + (a.shape[-1] - window + 1, window)
  7. strides = a.strides + (a.strides[-1],)
  8. c = np.lib.stride_tricks.as_strided(a, shape=shape, strides=strides)
  9. return c
  10. arr = df['Text'].values
  11. b = np.all(rolling_window(arr, N) == pat, axis=1)
  12. c = np.mgrid[0:len(b)][b]
  13. d = [i  for x in c for i in range(x, x+N)]
  14. df = df[np.in1d(np.arange(len(arr)), d)]
  15. print (df)
  16. id  left  top  width  height        Text
  17. 2   3     1   47      9       4   liability
  18. 3   4    10   69     32      67  commercial

huangapple
  • 本文由 发表于 2023年7月6日 14:43:16
  • 转载请务必保留本文链接：https://go.coder-hub.com/76626134.html
  • dataframe
  • numpy
  • pandas
  • python
  • python-3.x
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定