Select specific index, column pairs from pandas da

Question

I have a dataframe x:

x = pd.DataFrame(np.random.randn(3,3), index=[1,2,3], columns=['A', 'B', 'C'])
x


       A    B   C
1   0.256668    -0.338741   0.733561
2   0.200978    0.145738    -0.409657
3   -0.891879   0.039337    0.400449

and I would like to select a bunch of index column pairs to populate a new Series. For example, I could select [(1, A), (1, B), (1, A), (3, C)] which would generate a list or array or series with 4 elements:

[0.256668, -0.338741, 0.256668, 0.400449]

Any idea of how I should do that?

Answer 1

Use ix should be able to locate the elements in the data frame, like this:

import pandas as pd

# using your data sample
df = pd.read_clipboard()

df
Out[170]: 
          A         B         C
1  0.256668 -0.338741  0.733561
2  0.200978  0.145738 -0.409657
3 -0.891879  0.039337  0.400449

# however you cannot store A, B, C... as they are undefined names
l = [(1, 'A'), (1, 'B'), (1, 'A'), (3, 'C')]

# you can also use a for/loop, simply iterate the list and LOCATE the element
map(lambda x: df.ix[x[0], x[1]], l)
Out[172]: [0.25666800000000001, -0.33874099999999996, 0.25666800000000001, 0.400449]

Answer 2

If your pairs are positions instead of index/column names,

row_position = [0,0,0,2]
col_position = [0,1,0,2]

x.values[row_position, col_position]

Or get the position from np.searchsorted

row_position = np.searchsorted(x.index,row_labels,sorter = np.argsort(x.index))

Answer 3

I think get_value() and lookup() is faster:

import numpy as np
import pandas as pd
x = pd.DataFrame(np.random.randn(3,3), index=[1,2,3], columns=['A', 'B', 'C'])

locations = [(1, "A"), (1, "B"), (1, "A"), (3, "C")]

print x.get_value(1, "A")

row_labels, col_labels = zip(*locations)
print x.lookup(row_labels, col_labels)