How to removing "mirror copy" rows in a pandas Dataframe?

user_5269850 0
Viewed 352

I have the following pandas DataFrame has "mirror copy" rows between A-B-E and C-D-F:

import numpy as np
import pandas as pd

df1 = pd.DataFrame(np.array([[1, 2, 3, 4, 47, 27], [5, 6, 7, 8, 21, 40], [9, 10, 11, 12, 45, 33], 
    [3, 4, 1, 2, 27, 47], [7, 8, 5, 6, 40, 21], [11, 12, 9, 10, 33, 45]]), 
    columns=['A', 'B', 'C', 'D', 'E', 'F'])

print(df1)

##     A   B   C   D   E   F
## 0   1   2   3   4  47  27
## 1   5   6   7   8  21  40
## 2   9  10  11  12  45  33
## 3   3   4   1   2  27  47
## 4   7   8   5   6  40  21
## 5  11  12   9  10  33  45

By "mirror copies", I mean the following: if you look at rows 3, 4, 5, these are "mirror copies" of rows 0, 1, 2. Columns A and B in rows 0, 1, 2 are columns C and D in rows 3, 4, 5.

For example, in row 0, ['A', 'B', 'C', 'D'] is [1, 2, 3, 4]. In row 3, ['A', 'B', 'C', 'D'] is [3, 4, 1, 2].

This is true with columns E and F as well---E values in rows 0, 1, 2 are the F values in rows 3, 4, 5.

I would like to remove these "mirror copy" rows and only leave the "unique" pairs. The correct output above should be either:

##     A   B   C   D   E   F
## 0   1   2   3   4  47  27
## 1   5   6   7   8  21  40
## 2   9  10  11  12  45  33

or

##     A   B   C   D   E   F
## 0   3   4   1   2  27  47
## 1   7   8   5   6  40  21
## 2  11  12   9  10  33  45

but not both---only one copy of the rows.

I don't know how to subset a pandas DataFrame in order to remove these sorts of rows. How could one do this in an algorithmically efficient (without an explosion of memory) way?

My first idea is wrong, as it only checks per row:

df1 = df1[~((df1.A == df1.C) & (df1.B == df1.D) & (df1.E == df1.F))]

Any help appreciated! Thanks

pandas subset dataframe python
1 Answers

You can do the following:

from collections import Counter

import numpy as np
import pandas as pd


def key(x):
    return frozenset(Counter(x).items())


df = pd.DataFrame(np.array([[1, 2, 3, 4, 47, 27], [5, 6, 7, 8, 21, 40], [9, 10, 11, 12, 45, 33],
                            [3, 4, 1, 2, 27, 47], [7, 8, 5, 6, 40, 21], [11, 12, 9, 10, 33, 45]]),
                  columns=['A', 'B', 'C', 'D', 'E', 'F'])

keys = df[['A', 'B', 'C', 'D', 'E', 'F']].apply(key, axis=1)
mask = keys.duplicated(keep='last')

print(df[mask])

Output

   A   B   C   D   E   F
0  1   2   3   4  47  27
1  5   6   7   8  21  40
2  9  10  11  12  45  33

The idea is to create a keys column (Series) that will have the same value on those rows that are mirror of each other.

This:

def key(x):
    return frozenset(Counter(x).items())


keys = df[['A', 'B', 'C', 'D', 'E', 'F']].apply(key, axis=1)

creates the keys column, then this marks the duplicated values:

mask = keys.duplicated(keep='last')

You can also use the key suggested by @piRSquared:

def key(x): 
    a, b, c, d, e, f = x
    return tuple(map(frozenset, [(a, c), (b, d), (e, f)]))
user_40015920
Related