Creating a new column based on conditions for other columns-CodePudding

I have a DataFrame with columns consisting of some values and NaN where there were no values assigned for the specific column.

import pandas as pd
df = pd.DataFrame({'id': [10, 46, 75, 12, 99, 84],
                   'col1': ['Nan',         
                            15,
                            'Nan',
                            14,
                            'NaN',
                            'NaN'],
                   'col2': ['NaN', 'NaN', 'NaN', 12, 876, 4452],
                   'col3': ['NaN', 11, 13, 546, 9897, 1]
                                   })
                                
df

With the following output:

id  col1    col2    col3
0   10  Nan NaN NaN
1   46  15  NaN 11
2   75  Nan NaN 13
3   12  14  12  546
4   99  NaN 876 9897
5   84  NaN 4452  1

My objective is to create a new column (col4), which says 'original' for all the rows where all three columns (col1, col2, col3) have NaN and 'referenced' otherwise. I tried the np.where method (given below), but it doesn't work as 'NaN' is (probably) not picked up as a numerical value.

df['col4'] = np.where((df['col1'] == 'NaN') & (df['col2'] == 'NaN') & (df['col3'] == 'NaN'), 'original', 'referenced')

I am not that advanced in Python and cannot think of what the alternative should be. I would be grateful for any suggestions.

CodePudding user response：

You should replace the string NaN or Nan first

df = df.replace('(?i)nan', 'NaN', regex=True)
df['col4'] = np.where(df.filter(like='col').eq('NaN').all(axis=1), 'original', 'referenced')

# or

df = df.replace('(?i)nan', pd.NA, regex=True)
df['col4'] = np.where(df.filter(like='col').isna().all(axis=1), 'original', 'referenced')

print(df)

   id col1  col2  col3        col4
0  10  NaN   NaN   NaN    original
1  46   15   NaN    11  referenced
2  75  NaN   NaN    13  referenced
3  12   14    12   546  referenced
4  99  NaN   876  9897  referenced
5  84  NaN  4452     1  referenced

CodePudding user response：

Use DataFrame.isna for test all columns if missing and then DataFrame.all for test if all Trues per rows:

#If necessary
import numpy as np

df  = df.replace(['Nan', 'NaN'], np.nan)

df['col4'] = np.where(df[['col1','col2','col3']].isna().all(1), 'original', 'referenced')

Your solution with Series.isna:

df['col4'] = np.where(df['col1'].isna() & df['col2'].isna() & df['col3'].isna(), 
                     'original', 'referenced')