XLRD - Extract Data from two columns into a dict with multiple values
Question: I have an excel sheet with data that looks like this.

    
    
    Column1 Column2 Column3
    1       23      1
    1       5       2
    1       2       3
    1       19      5
    2       56      1
    2       22      2
    3       2       4
    3       14      5
    4       59      1
    5       44      1
    5       1       2
    5       87      3
    

What I'd like to do is to extract Column1 and Column3 into a dictionary with
keys that have multiple values. Something like this:

    
    
    1: 1,2,3,5
    2: 1,2
    3: 4,5
    4: 1
    5: 1,2,3
    

I'm new to Python, so any help you can provide would be greatly appreciated!

I can extract the data from the two columns and put it into a dict. But the
multiple values per key I'm unsure about. And also unsure how to group all of
the 1's, 2's, 3's from column1 into a single entry.

    
    
    for rownum in range(sheet.nrows):
        results = dict((sheet.cell_value(rownum, 0), sheet.cell_value(rownum, 2)) for rownum in range(sheet.nrows))
    return results
    

EDIT: Thanks to the help of this website, this is where I currently sit.

    
    
    xl = pandas.read_excel(r"e:\py_projects\py_test\test_data.xlsx", sheetname='stockTestColumn1')
    grouped = xl.groupby("columnid")
    myData = grouped["volumeid"].apply(lambda x: [e for e in x]) 
    

What I'm now hoping to do is check for the presence of x for any given key.

So, if this is the output:

    
    
    1: 1,2,3,4,5
    2: 1,2
    3: 4,5
    4: 1
    5: 1,2,3
    

I'm hoping to do something like:

    
    
    check = myData.get('1')
    if '4' in check:
        print "do something"
    


Answer: this is a typical usecase of pandas [groupby](http://pandas.pydata.org/pandas-
docs/stable/groupby.html),

    
    
    #!/usr/bin/python
    import pandas as pd
    from StringIO import StringIO
    
    s = """Column1 Column2 Column3
    1       23      1
    1       5       2
    1       2       3
    1       19      5
    2       56      1
    2       22      2
    3       2       4
    3       14      5
    4       59      1
    5       44      1
    5       1       2
    5       87      3
    """
    
    sio = StringIO(s)
    df = pd.read_table(sio, sep=r"\s+")
    
    grouped = df.groupby("Column1")
    print grouped["Column3"].apply(lambda x: [e for e in x])
    

output:

    
    
    1    [1, 2, 3, 5]
    2          [1, 2]
    3          [4, 5]
    4             [1]
    5       [1, 2, 3]
    

check the `sum` signature, `apply` actually passed in a iterator. basically,
pandas is the right tool to do such tabular data processing.

