1

I have a dataframe like the one below, and I have to create a new column year_val that is equal to the values of col2016 through col2019 based on the Years column, so that the value for year_val will be the value of col#### when Years is equal to the suffix of col####

import pandas as pd

sampleDF = pd.DataFrame({'Years':[2016,2016,2017,2017,2018,2018,2019,2019],
                        'col2016':[1,2,3,4,5,6,7,8],
                        'col2017':[9,10,11,12,13,14,15,16],
                        'col2018':[17,18,19,20,21,22,23,24],
                        'col2019':[25,26,27,28,29,30,31,32]})

sampleDF['year_val'] = ?????

1 Answer 1

3

Use DataFrame.lookup with change values in Years column with prepend col and cast to string:

sampleDF['year_val'] = sampleDF.lookup(sampleDF.index, 'col' + sampleDF['Years'].astype(str))

print (sampleDF)
   Years  col2016  col2017  col2018  col2019  year_val
0   2016        1        9       17       25         1
1   2016        2       10       18       26         2
2   2017        3       11       19       27        11
3   2017        4       12       20       28        12
4   2018        5       13       21       29        21
5   2018        6       14       22       30        22
6   2019        7       15       23       31        31
7   2019        8       16       24       32        32

EDIT: If check definition of lookup function:

result = [df.get_value(row, col) for row, col in zip(row_labels, col_labels)]

you can modify it with try-except statement with Series.at for prevent:

FutureWarning: get_value is deprecated and will be removed in a future release. Please use .at[] or .iat[] accessors instead oup.append(sampleDF.at[row, col] )

sampleDF = pd.DataFrame({'Years':[2015,2016,2017,2017,2018,2018,2019,2019],
                        'col2016':[1,2,3,4,5,6,7,8],
                        'col2017':[9,10,11,12,13,14,15,16],
                        'col2018':[17,18,19,20,21,22,23,24],
                        'col2019':[25,26,27,28,29,30,31,32]})

print (sampleDF)
   Years  col2016  col2017  col2018  col2019
0   2015        1        9       17       25
1   2016        2       10       18       26
2   2017        3       11       19       27
3   2017        4       12       20       28
4   2018        5       13       21       29
5   2018        6       14       22       30
6   2019        7       15       23       31
7   2019        8       16       24       32

out= []
for row, col in zip(sampleDF.index, 'col' + sampleDF['Years'].astype(str)):
    try:
        out.append(sampleDF.at[row, col] )
    except KeyError:
        out.append(np.nan)

sampleDF['year_val'] = out
print (sampleDF)
   Years  col2016  col2017  col2018  col2019  year_val
0   2015        1        9       17       25       NaN
1   2016        2       10       18       26       2.0
2   2017        3       11       19       27      11.0
3   2017        4       12       20       28      12.0
4   2018        5       13       21       29      21.0
5   2018        6       14       22       30      22.0
6   2019        7       15       23       31      31.0
7   2019        8       16       24       32      32.0
Sign up to request clarification or add additional context in comments.

1 Comment

How to I ignore values that are not found? Like if I have 2020 values on the Year colum, but do not have a col_2020

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.