The Deuce
[ Message Archive | The Deuce ]

    I think countif works, but I don't know excel. This python would work, too... Archived Message

    Posted by illinibone on October 23, 2023, 11:02:47, in reply to "Excel question: I have a column with a couple letters in each. Some have two sets of letters"

     
    #!/usr/bin/python3 
     
    from collections import defaultdict 
    import pandas as pd 
    import argparse 
     
    if __name__ == '__main__': 
        parser = argparse.ArgumentParser() 
        parser.add_argument('-f', '--filename', help='excel file', required=True) 
        parser.add_argument('-c', '--col', help='column to insepct', required=True) 
        parser.add_argument('-n', '--num_headers', help='number of header rows', default=0) 
        args = parser.parse_args() 
     
        if args.num_headers == 0: 
            headers = None 
        else: 
            headers = args.num_headers - 1 
     
        col = ord(args.col.lower()) - ord('a') 
     
        df = pd.read_excel(args.filename, header=headers) 
     
        results = defaultdict(int) 
     
        for row in df[col]: 
            x = row.split('&') 
            for pair in x: 
                pair = pair.strip() 
     
                pair = pair.lower() # <-- remove this line if case matters 
                 
                results[pair] += 1 
                 
        for key in results.keys(): 
            print('{}: {}'.format(key, results[key]))


    Example:

    Input data: 
     
    asdfMN 
    qwergf 
    zxvcpt 
    oiuoiuMN & gf


    Output: 
     
    python3 excel-parse.py --filename test.xlsx --column B 
    mn: 2 
    gf: 2 
    pt: 1

    Previous Message

    Separated by an ampersand. So for example, MN, gf, pt, gf & mn, etc.

    Is there a way to get excel to total each set and tell me how many there are?

    Message Thread: