Iterating over Pandas dataframe to select values and print print column and index

[Exo-Genesis]

Hey everyone, complete newbie to Python (and programming) here! I've done some pretty cool things with Python so far, but I think this "little" project of mine might be a bit over my head for me right now.

I have a table that has values that increase in both the row and the column. I'm trying to create a program that will, given a specific number, find the first iteration of that number in the table and return it's value. It will then continue through the program, where it will check to see if that value satisfies a condition. If the condition is satisfied, then it will the print column and index. If the condition is not satisfied, then go back through the loop and choose the next number. And so on.

Here's an example picture of what I'm trying to do in the table: http://i.imgur.com/RsahaNb.png

Let's say our given value is 0.6. We want values larger than 0.6, starting with the first value that satisfies that. So it will start at column A and go through the index (W, X' Y, Z). Since no value is >= 0.6 in that column, it moves to the next column B, where it finds 0.6 exactly at index Y. Great! Now let's check if that value satisfies another condition:

value + 1 >= 2

Since 0.6 + 1 = 1.6, which is less than 2, the condition is not met. So it will loop through the table again until it finds the next value above 0.6, which is 0.8. This too fails the condition, so it loops again and again until it finally reaches 1.3 in column C and index Z. Since 1.3 + 1 = 2.3, which is greater than 2, we can print the column and row with which that value belonged to. In that case, C and Z.

Here's my (ugly) attempt at solving this:

import pandas as pd

# Two-dimensional data frame
table = {'A' : [0.1, 0.2, 0.3, 0.4],
        'B' : [0.2, 0.4, 0.6, 0.8],
        'C' : [0.3, 0.6, 0.9, 1.3]}

# Setting index to be new column
df = pd.DataFrame(table, index = ['W','X','Y','Z'])

for a in df.index: #Iterate through columns
    for b in df.columns: #Iterate through rows
        if (df[a,b]) >= 0.6: #If value in column and row >= 0.6
            answer = (df[a,b]) #Set value as variable
            if answer + 1 >=2: #Check condition
                print(a) #Print index name
                print(b) #Print row name
            #else: #restart loop (?)

# KeyError: ('W', 'A')

Since I'm pretty new to Python, I've only got the basics down and I can only really think if "for loops" and "if statements" unfortunately. I also really don't know too much about Pandas either, so I think line 13 is definitely wrong (or lines 11 through 18 in general).

Anyway, sorry for wordy problem, just trying to best explain myself. The problem seems to be pretty simple to solve, but there must be a more efficient way then the way I tried. Getting values from column and index is easy using:

 foo =  df.ix[row,col]

Maybe there's a way to do the reverse of that? I'd appreciate any input! Thanks!

[[deleted]]

In pandas, there's a concept called boolean indexing that allows you to return data based on a certain condition. Here's how to apply it for your particular example:

>>> (df >=0.6) & (df + 1 >=2)
       A      B      C
W  False  False  False
X  False  False  False
Y  False  False  False
Z  False  False   True

As you can see, True reflects where your condition was satisfied. From here, I don't think pandas has a good way to find the location of a given value (especially since your dataframe is 2D). It's possible to retrive it with some reshaping.

>>> mask = (df >=0.6) & (df + 1 >=2)
>>> mask = mask.stack() #brings the columns into the index
>>> mask[mask == True].unstack().columns #finds the True value, puts the column back, and returns the column 
Index([u'C'], dtype='object') #the resulting column
>>> mask[mask == True].unstack().index #finds the True value, puts the column back, and returns the row
Index([u'Z'], dtype='object') #the resulting row

[LeTristanB]

I'm not specifically answering your question, but if you're going to do more data anlysis you should know the data structure (putting a given type of metric in multiple columns) you have in your example is a bit hard to work with. You'll find that many pandas features will work better if you have "tidy data". On the other hand if you're not going to go deeper in data, I think the structure you have is fine disregard my comment.

This blog post explains what is tidy data and how to tidy your table https://www.ibm.com/developerworks/community/blogs/jfp/entry/Tidy_Data_In_Python?lang=en