Access values in a dataframe based on index and values in another

Question

How do i get the value from a dataframe based on a list of index and headers?

These are the dataframes i have:

a = pd.DataFrame([[1,2,3],[4,5,6],[7,8,9]], columns=['a','b','c'])
referencingDf = pd.DataFrame(['c','c','b'])

Based on the same index, i am trying to get the following dataframe output:

outputDf = pd.DataFrame([3,6,8])

Currently, i tried this but would need to take the diagonal values. Am pretty sure there is a better way of doing so:

a.loc[referencingDf.index.values, referencingDf[:][0].values]

How i can reference a to get the following outputDf based on referencingDF. essentially a[0]['c'], a[1]['c'], a[2]['b']. — smallcat31
– smallcat31, Commented Sep 4, 2017 at 3:55

jezrael · Accepted Answer · 2017-09-04 05:04:45Z

5

You need lookup:

b = a.lookup(a.index, referencingDf[0])
print (b)
[3 6 8]

df1 = pd.DataFrame({'vals':b}, index=a.index)
print (df1)
   vals
0     3
1     6
2     8

answered Sep 4, 2017 at 5:04

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DYZ · Accepted Answer · 2017-09-04 04:13:49Z

3

Another way to use list comprehension:

vals = [a.loc[i,j] for i,j in enumerate(referencingDf[0])]
# [3, 6, 8]

answered Sep 4, 2017 at 4:13

DYZ

57.3k10 gold badges73 silver badges101 bronze badges

Comments

cs95 · Accepted Answer · 2017-09-04 04:17:48Z

2

IIUC, you can use df.get_value in a list comprehension.

vals = [a.get_value(*x) for x in referencingDf.reset_index().values]
# a simplification would be [ ... for x in enumerate(referencingDf[0])] - DYZ
print(vals) 
[3, 6, 8]

And then, construct a dataframe.

df = pd.DataFrame(vals)
print(df)

   0
0  3
1  6
2  8

edited Sep 4, 2017 at 4:17

answered Sep 4, 2017 at 3:54

cs95

406k106 gold badges744 silver badges797 bronze badges

2 Comments

DYZ Over a year ago

... for x in enumerate(referencingDf[0]) ?

cs95 Over a year ago

@DYZ Definitely another choice, assuming referencingDf has a rangeIndex for columns (might not always be so).

Divakar · Accepted Answer · 2017-09-04 05:45:47Z

0

Here's one vectorized approach that uses column_index and then NumPy's advanced-indexing for indexing and extracting those values off each row of dataframe -

In [177]: col_idx = column_index(a, referencingDf.values.ravel())

In [178]: a.values[np.arange(len(col_idx)), col_idx]
Out[178]: array([3, 6, 8])

answered Sep 4, 2017 at 5:45

Divakar

222k19 gold badges273 silver badges374 bronze badges

Collectives™ on Stack Overflow

Access values in a dataframe based on index and values in another

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related