基于模糊匹配的列表中值的查找与替换

Question

我试图循环使用熊猫中某列的值，并更改所有类似的值，使它们协调一致。我首先将该列提取为一个列表，并希望对每一行进行循环，在找到类似的值时将其替换为类似的值，然后将列表放回dataframe中替换该列。例如，一个列，如：

Cool
Awesome
cool
CoOl
Awesum
Awesome
Mathss
Math
Maths
Mathss

将成为：

CoOl
Awesome
coOol
CoOl
Awesome
Awesome
Mathss
Mathss
Mathss
Mathss

代码如下：

def matchbrands():
    conn = sqlite3.connect('/Users/XXX/db.sqlite3')
    c = conn.cursor()
    matchbrands_df = pd.read_sql_query("SELECT * from removeduplicates", conn)

    brands = [x for x in matchbrands_df['brand']]

    i=1

    for x in brands:
        if fuzz.token_sort_ratio(x, brands[i]) > 85:
            x = brands[i]
        else:
            i += 1

    n = matchbrands_df.columns[7]
    matchbrands_df.drop(n, axis=1, inplace=True)
    matchbrands_df[n] = brands

    matchbrands_df.to_csv('/Users/XXX/matchedbrands.csv')
    matchbrands_df.to_sql('removeduplicates', conn, if_exists="replace")

但是，这根本不会更改列。我不知道为什么。任何帮助都将不胜感激

Answer 1

你的代码毫无意义

第一：使用x =...不能更改列表brands上的值。你需要brands[index] = ...

第二：它需要嵌套的for-loop将x与brands中的所有其他单词进行比较

for index, word in enumerate(brands):
    for other in brands[index+1:]:
        #print(word, other, fuzz.token_sort_ratio(word, other))
        if fuzz.token_sort_ratio(word, other) > 85:
            brands[index] = other

---

最小工作代码

import pandas as pd
import fuzzywuzzy.fuzz as fuzz

data = {'brands':
'''Cool
Awesome
cool
CoOl
Awesum
Awesome
Mathss
Math
Maths
Mathss'''.split('\n')
}  # rows

df = pd.DataFrame(data)

print(' - before  -')
print(df)

brands = df['brands'].to_list()

print(' - changes  -')
for index, word in enumerate(brands):
    #for other_index, other_word in enumerate(brands):
    for other_index, other_word in enumerate(brands[index+1:], index+1):
        #if word != other_word:
            result = fuzz.token_sort_ratio(word, other_word)
            
            if result > 85:
                print(f'OK | {result:3} | {index:2} {word:7} -> {other_index:2} {other_word}')                
            elif result > 50:
                print(f'   | {result:3} | {index:2} {word:7} -> {other_index:2} {other_word}')
                
            if result > 85:
                brands[index] = other_word
                #break
                #word = other_word

df['brands'] = brands

print(' - after  -')
print(df)

结果:

 - before  -
    brands
0     Cool
1  Awesome
2     cool
3     CoOl
4   Awesum
5  Awesome
6   Mathss
7     Math
8    Maths
9   Mathss
 - changes  -
OK | 100 |  0 Cool    ->  2 cool
OK | 100 |  0 Cool    ->  3 CoOl
   |  77 |  1 Awesome ->  4 Awesum
OK | 100 |  1 Awesome ->  5 Awesome
OK | 100 |  2 cool    ->  3 CoOl
   |  77 |  4 Awesum  ->  5 Awesome
   |  80 |  6 Mathss  ->  7 Math
OK |  91 |  6 Mathss  ->  8 Maths
OK | 100 |  6 Mathss  ->  9 Mathss
OK |  89 |  7 Math    ->  8 Maths
   |  80 |  7 Math    ->  9 Mathss
OK |  91 |  8 Maths   ->  9 Mathss
 - after  -
    brands
0     CoOl
1  Awesome
2     CoOl
3     CoOl
4   Awesum
5  Awesome
6   Mathss
7    Maths
8   Mathss
9   Mathss

它不会将Awesum更改为Awesome，因为它得到77

它不会将Math更改为Mathss，因为它得到80。但是Maths会得到89

如果在for-循环中使用word = other_word，那么它可以将Math转换为Maths（89），然后将Maths转换为Mathss（91）。但这样一来，它可能会改变很多次，最后它会变成一个单词，这个单词最初可以给出比85小得多的值。您还可以为75而不是85获得预期结果

但是这种方法得到的最后一个单词的值是>85，而不是最大的值-因此可以有更好的匹配单词，并且不会使用它。使用break-it获得第一个单词和>85。也许它应该获取所有带有>85的单词，并选择具有最大值的单词。它必须跳过相同但在不同行中的单词。但所有这些都会造成奇怪的情况

在代码注释中，我保留了其他修改意见

---

编辑：

与{}和颜色相同

import pandas as pd
import fuzzywuzzy.fuzz as fuzz
from colorama import Fore as FG, Back as BG, Style as ST

data = {'brands':
'''Cool
Awesome
cool
CoOl
Awesum
Awesome
Mathss
Math
Maths
Mathss'''.split('\n')
}  # rows

df = pd.DataFrame(data)

print(' - before  -')
print(df)

brands = df['brands'].to_list()

print(' - changes  -')
for index, word in enumerate(brands):
    print('-', index, '-')
    #for other_index, other_word in enumerate(brands):
    for other_index, other_word in enumerate(brands[index+1:], index+1):
        #if word != other_word:
            result = fuzz.token_sort_ratio(word, other_word)
            
            if result > 85:
                color = ST.BRIGHT + FG.GREEN
                info  = 'OK'
            elif result > 75:
                color = ST.BRIGHT + FG.YELLOW
                info  = ' ?'
            elif result > 50:
                color = ST.BRIGHT + FG.WHITE
                info  = '  '
            else:
                color = ST.BRIGHT + FG.RED
                info  = ' -'
            
            print(f'{color}{info} | {result:3} | {index:2} {word:7} -> {other_index:2} {other_word}{ST.RESET_ALL}')
                
            if result > 75:
                brands[index] = other_word
                #break
                #word = other_word
    
df['brands'] = brands

print(' - after  -')
print(df)