Find one - many matches between columns in a Data frame

user_15367564 0
Viewed 46

I have a Data frame as shown below. enter image description here

It has 189437 rows and 2 columns.What I need to do is find out how many Num has more than one ID linked to it (i.e same Num but different ID) and also The visa-versa (same ID but different Num). I need to get how many rows out of the whole has this kind of data . How do I do it?

pandas dataframe python
1 Answers

You can use groupby for this purpose. To find how many Num with different ID :

groups = df.groupby("Num").apply(lambda x: len(set(x["ID"])))
result = sum(groups > 1)

And for the other way around :

groups = df.groupby("ID").apply(lambda x: len(set(x["Num"])))
result = sum(groups > 1)

Edit 1: As proposed by @Grzegorz Skibinski in comment, nunique() may be more concise, so :

result = sum(df.groupby("Num")["ID"].nunique() > 1)

Edit 2: Benchmark

I created dataframe with 2 columns (integer and str)

import pandas as pd
import numpy as np
import random
import string

df = pd.DataFrame({
    "col1": np.random.randint(0, 100, 1000_000),
    "col2": ["".join(random.sample(string.ascii_lowercase[:5], 3)) for _ in range(1000_000)]
})

Counting on the str column: nunique() wins over len(set()). However Counting on the int column: len(set()) wins over nunique()

%%timeit -n 10
df.groupby("col1").apply(lambda x: len(set(x["col2"])))
# output: 10 loops, best of 5: 467 ms per loop

%%timeit -n 10
df.groupby("col1")["col2"].nunique()
# output: 10 loops, best of 5: 296 ms per loop

%%timeit -n 10
df.groupby("col2").apply(lambda x: len(set(x["col1"])))
# output: 10 loops, best of 5: 286 ms per loop

%%timeit -n 10
df.groupby("col2")["col1"].nunique()
# output: 10 loops, best of 5: 306 ms per loop
user_74375240
Related