How to create a conditional column in Python that picks data from different column based on the year?

My dataset looks something like this:

Output should be something like this:

So that this was unwanted data can be removed and relevant can be kept.

>Solution :

You can use indexing lookup:

# get Year to use for indexing
idx, cols = pd.factorize(df['Sales Year'])
# add prefix
cols = 'Sales_percentage_' + cols.astype(str)
# index
df['Sales'] = df.reindex(cols, axis=1).to_numpy()[np.arange(len(df)), idx]
# get rid of unnecessary columns
df = df.filter(regex=r'^(?!Sales_percentage_)')

output:

   Product ID  Sales Year  Sales
0           1        2016      5
1           2        2017      9