CODEWITHSUNDEEP
pandas
mLstudent33
mLstudent33

Reputation: 1175

Pandas set column value to 1 if other column value is NaN

I've looked everywhere tried .loc .apply and using lambda but I still cannot figure this out.

I have the UCI congressional vote dataset in a pandas dataframe and some votes are missing for votes 1 to 16 for each Democrat or Republican Congressperson.

So I inserted 16 columns after each vote column called abs.

I want each abs column to be 1 if the corresponding vote column is NaN.

None of those above methods I read on this site worked for me.

So I have this snippet below that also does not work but it might give a hint as to my current attempt using basic iterative Python syntax.

for i in range(16):
    for j in range(len(cvotes['v1'])):
        if cvotes['v{}'.format(i+1)][j] == np.nan:
            cvotes['abs{}'.format(i+1)][j] = 1
        else:
            cvotes['abs{}'.format(i+1)][j] = 0

Any suggestions?

The above currently gives me 1 for abs when the vote value is NaN or 1.

edit:

I saw the given answer so tried this with just one column

cols = ['v1']

for col in cols:
    cvotes = cvotes.join(cvotes[col].add_prefix('abs').isna().
                         astype(int))

but it's giving me an error:

ValueError: columns overlap but no suffix specified: Index(['v1'], dtype='object')

My dtypes are:

party     object
v1       float64
v2       float64
v3       float64
v4       float64
v5       float64
v6       float64
v7       float64
v8       float64
v9       float64
v10      float64
v11      float64
v12      float64
v13      float64
v14      float64
v15      float64
v16      float64
abs1       int64
abs2       int64
abs3       int64
abs4       int64
abs5       int64
abs6       int64
abs7       int64
abs8       int64
abs9       int64
abs10      int64
abs11      int64
abs12      int64
abs13      int64
abs14      int64
abs15      int64
abs16      int64
dtype: object

Upvotes: 0

Views: 64

Answers (1)

BENY
BENY

Reputation: 323316

Let us just do join with add_prefix

col=[c1,c2...]
s=pd.DataFrame(df[col].values.tolist(),index=df.index)
s.columns=s.columns+1
df=df.join(s.add_prefix('abs').isna().astype(int))

Upvotes: 1

Related Questions