CODEWITHSUNDEEP
pythonpandasdataframeconditional-statements
Rafael Neves
Rafael Neves

Reputation: 467

Create a new column in pandas dataframe based on multiple conditions

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'] = ?????

Upvotes: 1

Views: 174

Answers (1)

jezrael
jezrael

Reputation: 863531

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

Upvotes: 3

Related Questions