0

My dataset looks something like this:

Product ID Sales Year Sales_percentage_2016 Sales_percentage_2017 Sales_percentage_2018
1 2016 5 8 5
2 2017 7 9 6

Output should be something like this:

Product ID Sales Year Sales_Percentage
1 2016 5
2 2017 9

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

user17475933
  • 55
  • 4
  • I cannot find any `condition` in your question, - looks like you want simply select few columns – NoobVB Sep 02 '22 at 06:27
  • Read about select columns using loc and iloc. Regards. – Luis Alejandro Vargas Ramos Sep 02 '22 at 06:29
  • @NoobVB sales year contains year information. Need sales percentage information from that year alone in that row. – user17475933 Sep 02 '22 at 06:36
  • https://stackoverflow.com/search?q=%5Bpandas%5D+column+conditional – Paul H Sep 02 '22 at 06:41
  • Can we assume that in the row which contains "Product ID" = 1, "Sales Year" = 2017, we will find same values as above for year 2017 (8) and 2018 (5)? – BloomShell Sep 02 '22 at 06:42
  • 1
    @PaulH it's not a simple conditional ;) – mozway Sep 02 '22 at 06:46
  • @A259 I closed it b/c as presented, this could be solved with `numpy.select` or unstacking the data frame & manipulating the column names. There are *so many* questions and answers on all of those topics – Paul H Sep 02 '22 at 07:28
  • @A259 to be fair, there are **so many** "*I have this, I want this*" questions, with little or no research, most of them being on the same 3-4 topics, it's quite easy to close... Here it could indeed be solved with melting and filtering (although not so efficient). – mozway Sep 02 '22 at 07:42

1 Answers1

1

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
mozway
  • 194,879
  • 13
  • 39
  • 75