I have browsed multiple other related questions about Pandas, and read documentation for groupby and duplicated, but I cannot seem to find a way to fit all the pieces together in Pandas. I could do this by iterating over the rows in my CSV multiple times and doing pairwise comparison, but it seems like I should be able to do it in Pandas. Essentially, I would like to define my own function for comparing two rows and deciding if they are a 'duplicate'. If two rows are 'duplicates' of each other, then merge by keeping columns 0 and 1 of the first row, and use sum() on the rest of the columns. I have a CSV file that has entries something like the following: English Spanish Count AF ... # other numerical columns 0 'hello' 'hola' 23 0 1 'helo' 'hola' 2 1 2 'hello' 'hola_a' 1 0 3 'hallo' 'a_aureola' 1 1 ... I would like to consider rows as 'duplicated' based on these criteria: If the Levenshtein string edit distance between two rows' English entries is below a threshold, and the Spanish is an exact match, then they are duplicates e.g. rows 0 and 1 have an English edit distance of 1, and the Spanish is an exact match If the English of two rows is an exact match, and the Spanish entries have a non-zero overlap when split into lists on _, then they are duplicates e.g. rows 0 and 2 match in English, and row0 'hola'.split('_') -> ['hola'] has an overlap with row2 'hola_a'.split('_') -> ['hola','a'] since both lists have 'hola' However, if neither the English nor the Spanish is an exact match, then they ar enot duplicates e.g. row 2 and row 3 should not be counted as duplicates, even though the English edit distance is 1, and the Spanish lists overlap when split on _ The duplicates should then be merged, with the non-language column entries added up. (I think I could figure this part out with groupby, the main difficulty is getting the duplicates.) The final output should look like: English Spanish Count AF ... # other numerical columns 0 'hello' 'hola' 26 1 1 'hallo' 'a_aureola' 1 1 Continue reading...
