Use groupby to see if a value appears before another in a do not call list

Harvest 0
Viewed 20

If anyone could please help me with this, I'd really appreciate it:

I have this dataframe which contains phone numbers ('called_to') and whether that number is placed on a call or do not call list column: ('dispo'). Each row is a separate call and an ok to call is designated with 'c' and do not call is designated with 'd'.

These calls are made in chronological order, so the index serves as a sort of datetime indicator.

What I want to do is only print numbers that had a number placed on the do not call list and then had an ok to call designation. All of these numbers have more than one call but can have anywhere between 2 and n total calls.

what I don't understand is how to use groupby and then sort out the numbers that meet the criteria. I was thinking maybe I could try seeing if the group failed alphabetical order (c, c, d, c would fail while c, c, c, d would pass). Again, not sure how to do this within a groupby and I'm trying to see if I could use .apply.

create sample dataframe

edf = pd.DataFrame.from_dict({'called_to' : ['11' , '22' , '33', '44', '11' , '22' , '33', '44','11' , '22' , '33', '44','11' , '22' , '33', '44'], 'dispo': ['c' , 'c', 'd', 'c', 'c', 'c', 'c', 'c', 'c', 'c', 'c', 'd', 'c', 'd', 'c', 'c']})

edf
called_to   dispo
0   11  c
1   22  c
2   33  d
3   44  c
4   11  c
5   22  c
6   33  c
7   44  c
8   11  c
9   22  c
10  33  c
11  44  d
12  11  c
13  22  d
14  33  c
15  44  c

I can display the information using groupby, and it looks like this:

by_number = edf.groupby('called_to')
for key, item in by_number:
    print(by_number.get_group(key), "\n\n")

   called_to dispo
0         11     c
4         11     c
8         11     c
12        11     c 


   called_to dispo
1         22     c
5         22     c
9         22     c
13        22     d 


   called_to dispo
2         33     d
6         33     c
10        33     c
14        33     c 


   called_to dispo
3         44     c
7         44     c
11        44     d
15        44     c

The desired output would be:

       called_to dispo
    2         33     d
    6         33     c
    10        33     c
    14        33     c 
    
    
       called_to dispo
    3         44     c
    7         44     c
    11        44     d
    15        44     c
group-by pandas python
1 Answers

You can filter the groups with:

# is the call OK?
m1 = edf['dispo'].eq('c')
# was a previous call not OK?
m2 = edf.groupby('called_to')['dispo'].shift(-1).eq('d')

edf[(m1&m2).groupby(edf['called_to']). transform('any')]

variant to use in a loop

by_number = edf.groupby('called_to')

m1 = edf['dispo'].eq('c')
m2 = by_number['dispo'].shift().eq('d')
m = m1&m2

keep = set(edf.loc[m, 'called_to'])

for key, item in by_number:
    if key in keep:
        print(by_number.get_group(key), "\n\n")

Output:

   called_to dispo
2         33     d
6         33     c
10        33     c
14        33     c 


   called_to dispo
3         44     c
7         44     c
11        44     d
15        44     c
mozway0
Related