How do i populate a dataframe with the results of a function that searches other dataframes?

I am trying to construct a dataframe that is populated by the results of a series of search functions across a number of data frames, and i don't know where to start - i'm new to python.

The results table i'm constructing is a matrix, with each indexed row referencing a data frame, and each column representing a list. The desired Data frame looks like:

answer_df
            List 1                List 2             List 3
       
P1            ?                     ?                   ?
P2            ?                     ?                   ?
P3            ?                     ?                   ?
P4            ?                     ?                   ?
P5            ?                     ?                   ?
P6            ?                     ?                   ?

The values need to come from the results of an "is in" search function, where P1 is searched with the contents of each list.

Example dataframe:

P1

Index     Diagnosis      Meds     Tests      Obs
0             A12         NAN       NAN      NAN
1             B15         NAN       NAN      NAN
2             C28         NAN       NAN      NAN
3             NAN         D22       NAN      NAN
4             NAN         E91       NAN      NAN
5             NAN         NAN       F14      NAN
6             NAN         NAN       NAN      M55

P2

Index     Diagnosis      Meds     Tests      Obs
0             K11         NAN       NAN      NAN
1             L01         NAN       NAN      NAN
2             C28         NAN       NAN      NAN
3             NAN         X94       NAN      NAN
4             NAN         E91       NAN      NAN
5             NAN         NAN       F14      NAN
6             NAN         NAN       Y02      NAN

A list example is:

List 1

A12  
L01
D22
K88
F14
M55
N67

List 2
A12
F14
N64
P01
Y02
M55

I want to populate the answer_df by counting the number of matches between P1 / P2' and List 1' / List 2 so that it looks like this:

answer_df
            List 1                List 2             List 3
       
P1            4                     3                   ?
P2            2                     1                   ?
P3            ?                     ?                   ?
P4            ?                     ?                   ?
P5            ?                     ?                   ?
P6            ?                     ?                   ?

But i also need to repeat this function for all other lists and dataframes (3*6 = 18 searches in total). Any help would be much appreciated

1 answer

  • answered 2021-10-24 19:57 Corralien

    You can use melt and isin to count the number of matches (intersection):

    # Setup 2 dict for dataframes and lists
    P = {'P1': p1, 'P2': p2}
    L = {'List 1': l1, 'List 2': l2}
    
    data = {}
    for pname, p in P.items():
        for lname, l in L.items():
            count = p.melt().dropna()['value'].isin(l).sum()
            print(f"{pname} - {lname} = {count}")
            data[(pname, lname)] = count
    
    df = pd.Series(data).unstack()
    

    Output

    >>> df
        List 1  List 2
    P1       4       3
    P2       2       2
    

    Setup

    import pandas as pd
    import numpy as np
    from numpy import nan
    
    data1 = {'Diagnosis': ['A12', 'B15', 'C28', nan, nan, nan, nan],
             'Meds': [nan, nan, nan, 'D22', 'E91', nan, nan],
             'Tests': [nan, nan, nan, nan, nan, 'F14', nan],
             'Obs': [nan, nan, nan, nan, nan, nan, 'M55']}
    
    p1 = pd.DataFrame(data1)
    
    data2 = {'Diagnosis': ['K11', 'L01', 'C28', nan, nan, nan, nan],
             'Meds': [nan, nan, nan, 'X94', 'E91', nan, nan],
             'Tests': [nan, nan, nan, nan, nan, 'F14', 'Y02'],
             'Obs': [nan, nan, nan, nan, nan, nan, nan]}
    
    p2 = pd.DataFrame(data2)
    
    l1 = ['A12', 'L01', 'D22', 'K88', 'F14', 'M55', 'N67']
    l2 = ['A12', 'F14', 'N64', 'P01', 'Y02', 'M55']
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum