Python use dataframe column value in iloc (or shift)

user_2971591 0
Viewed 353

Although my previous question was answered here Python dataframe new column with value based on value in other row I still want to know how to use a column value in iloc (or shift or rolling, etc.)

I have a dataframe with two columns A and B, how do I use the value of column B in iloc? Or shift()?

d = {'A': [8, 2, 4, 5, 6, 4, 3, 5, 5, 3], 'B': [2, -1, 4, 5, 0, -3, 8, 2, 6, -1]}
df = pd.DataFrame(data=d)

Using iloc I get this error.

df['C'] = df['A'] * df['A'].iloc[df['B']]

ValueError: cannot reindex from a duplicate axis

Using shift() another one.

df['C'] = df['A'] * df['A'].shift(df['B'])

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Is it possible what I want to do? If yes, how? If no, why not?

pandas dataframe python
2 Answers

Use numpy indexing:

print (df['A'].to_numpy()[df['B'].to_numpy()])
[4 3 6 4 8 5 5 4 3 3]


df['C'] = df['A'] * df['A'].to_numpy()[df['B'].to_numpy()]
print (df)
   A  B   C
0  8  2  32
1  2 -1   6
2  4  4  24
3  5  5  20
4  6  0  48
5  4 -3  20
6  3  8  15
7  5  2  20
8  5  6  15
9  3 -1   9
user_29010020

numpy indexing is the fastest way i agree, but you can use list comprehension + iloc too:

d = {'A': [8, 2, 4, 5, 6, 4, 3, 5, 5, 3], 'B': [2, -1, 4, 5, 0, -3, 8, 2, 6, -1]}
df = pd.DataFrame(data=d)

df['C'] = df['A'] * [df['A'].iloc[i] for i in df['B']]

   A  B   C
0  8  2  32
1  2 -1   6
2  4  4  24
3  5  5  20
4  6  0  48
5  4 -3  20
6  3  8  15
7  5  2  20
8  5  6  15
9  3 -1   9
user_159797240
Related