Python pandas an efficient way to see which column contains a value and use its coordinates as an offset
Question: As part of trying to learn pandas I'm trying to reshape a spreadsheet. After
removing non zero values I need to get some data from a single column.

For the sample columns below, I want to find the most effective way of finding
the row and column index of the `cell` that contains the value `date` and get
the value next to it. (e.g. here it would be `38477`.

In practice this would be a much bigger DataFrame and the `date` row could
change and it may not always be in the first column.

What is the best way to find out where `date` is in the array and return the
value in the adjacent cell?

Thanks

    
    
    <bound method DataFrame.head of             0         1         2         4         5         7         8         10  \
    1   some title                                                                         
    2         date     38477                                                               
    5                   cat1                cat2                cat3                cat4   
    6                      a         b         c         d         e         f         g   
    8            Z  167.9404  151.1389   346.197  434.3589  336.7873  80.52901  269.1486   
    9            X   220.683   56.0029  73.73679  428.8939  483.7445  251.1877  243.7918   
    10           C  433.0189  390.1931  251.6636  418.6703  12.21859   113.093    136.28   
    12           V  226.0135  418.1141  310.2038  153.9018  425.7491  73.08073  277.5065   
    13           W   295.146  173.2747  2.187459  401.6453  51.47293   175.387  397.2021   
    14           S  306.9325  157.2772  464.1394   216.248  478.3903   173.948  328.9304   
    15           A  19.86611  73.11554   320.078  199.7598  467.8272  234.0331  141.5544   
    


Answer: This really just reformats a lot of the iteration you are doing to make it
clearer and take advantage of pandas ability to easily select, etc.

First, we need a dummy dataframe (with date in the last row and explicitly
ordered the way you have in your setup)

    
    
    import pandas as pd
    df = pd.DataFrame({"A": [1,2,3,4,np.NaN], 
                       "B":[5, 3, np.NaN, 3, "date"],
                       "C":[np.NaN,2, 1,3, 634]})[["A","B","C"]]
    

A clear way to do it is to find the row and then enumerate over the row to
find `date`:

    
    
    row = df[df.apply(lambda x: (x == "date").any(), axis=1)].values[0] # will be an array
    for i, val in enumerate(row):
        if val == "date":
            print row[i + 1]
            break
    

If your spreadsheet only has a few non-numeric columns, you could go by
column, check for date and get a row and column index (this may be faster
because it searches by column rather than by row, though I'm not sure)

    
    
    # gives you column labels, which are `True` if at least one entry has `date` in it
    # have to check `kind` otherwise you get an error.
    col_result = df.apply(lambda x: x.dtype.kind == "O" and (x == "date").any())
    
    # select only columns where True (this should be one entry) and get their index (for the label)
    column = col_result[col_result].index[0]
    col_index = df.columns.get_loc(column)
    
    # will be True if it contains date
    row_selector = df.icol(col_index) == "date"
    
    print df[row_selector].icol(col_index + 1).values
    

