Thursday, 7 November 2019

Conditional generation of new column - Pandas

I am trying to create a new column based on conditional logic on pre-existing columns. I understand there may be more efficient ways to achieve this but I have a few conditions that need to be included. This is just the first step.

The overall scope is to create two new columns that are mapped from 1 and 2. These are referenced to the Object column as I can have multiple rows for each time point.

Object2 and Value determine how to map the new columns. So if Value is == X, I want to match both Object columns to return the corresponding 1 and 2 for that time point to a new column. The same process should occur if Value is == Y. if Value is == Z, I want to insert 0, 0. Everything else should be NaN

df = pd.DataFrame({   
    'Time' : ['2019-08-02 09:50:10.1','2019-08-02 09:50:10.1','2019-08-02 09:50:10.2','2019-08-02 09:50:10.3','2019-08-02 09:50:10.3','2019-08-02 09:50:10.4','2019-08-02 09:50:10.5','2019-08-02 09:50:10.6','2019-08-02 09:50:10.6'],
    'Object' : ['A','B','A','C','A','C','C','B','B'],
    '1' : [1,1,5,7,9,11,13,15,17],  
    '2' : [0,1,4,6,8,10,12,14,16],     
    'Object2' : ['A','A',np.nan,'C','C',np.nan,'B','B','B'],                 
    'Value' : ['X','X',np.nan,'Y','Y',np.nan,np.nan,'Z',np.nan],
    })

def map_12(df):

for i in df['Value']:
    if i == 'X':
        df['A1'] = df['1']
        df['A2'] = df['2']
    elif i == 'Y':
        df['A1'] = df['1']
        df['A2'] = df['2']     
    elif i == 'Z':
        df['A1'] = 0
        df['A2'] = 0             
    else:
        df['A1'] = np.nan
        df['A2'] = np.nan              

return df

Intended Output:

                    Time Object   1   2 Object2 Value    A1    A2
0  2019-08-02 09:50:10.1      A   1   0       A     X   1.0   0.0
1  2019-08-02 09:50:10.1      B   3   1       A     X   1.0   0.0
2  2019-08-02 09:50:10.2      A   5   4     NaN   NaN   NaN   NaN
3  2019-08-02 09:50:10.3      C   7   6       C     Y   7.0   6.0
4  2019-08-02 09:50:10.3      A   9   8       C     Y   7.0   6.0
5  2019-08-02 09:50:10.4      C  11  10       C     Y  11.0  10.0
6  2019-08-02 09:50:10.5      C  13  12       C     Y  13.0  12.0
7  2019-08-02 09:50:10.6      B  15  14       B     Z   0.0   0.0
8  2019-08-02 09:50:10.6      B  17  16       A   NaN   NaN   NaN


from Conditional generation of new column - Pandas

No comments:

Post a Comment