Map values to separate col using conditional statement: python

Question

I'm hoping to use a conditional statement to create a new column but I'm unsure on the best way to proceed.

Using below, I essentially have various Items that contain a specific Direction for a given point in time. I want to use the ID to provide the correct Direction. So match the values in Items and ID to determine the correct Direction.

I've manually inserted this below as Main Direction but will need to automate this. I then want to pass a conditional statement to X. Specifically, if == 'Up' then add 10, if == 'Down' then subtract 10.

import pandas as pd

df = pd.DataFrame({              
    'Time' : [1,1,1,1,2,2,2,2,3,3,3,3],  
    'ID' : ['A','A','A','A','B','B','B','B','A','A','A','A'],      
    'Items' : ['A','B','A','A','B','A','A','B','A','A','B','B'],
    'Direction' : ['Up','Down','Up','Up','Down','Up','Up','Down','Up','Up','Down','Down'],           
    'Main Direction' : ['Up','Up','Up','Up','Down','Down','Down','Down','Up','Up','Up','Up'],    
    'X' : [1,2,3,4,6,7,8,9,3,4,5,6],      
    })

df['Dist'] = [df['X'] + 10 if x == 'Up' else df['X'] -10 for x in df['Main Direction']]

intended output:

    Time ID All Direction Main Direction  X       Dist
0      1  A   A        Up             Up  1         11
1      1  A   B      Down             Up  2         12
2      1  A   A        Up             Up  3         13
3      1  A   A        Up             Up  4         14
4      2  B   B      Down           Down  6         -4
5      2  B   A        Up           Down  7         -3
6      2  B   A        Up           Down  8         -2
7      2  B   B      Down           Down  9         -1
8      3  A   A        Up             Up  3         13
9      3  A   A        Up             Up  4         14
10     3  A   B      Down             Up  5         15
11     3  A   B      Down             Up  6         16

Your Question has already been answered: https://stackoverflow.com/questions/9535954/printing-lists-as-tabular-data — KH241, Jan 07 '21 at 02:33

score 3 · Accepted Answer · answered Jan 07 '21 at 02:34

Try with np.where

df.X=np.where(df['Main Direction'].eq('Up'),df.X+10,df.X-10)
df
    Time ID Items Direction Main Direction   X
0      1  A     A        Up             Up  11
1      1  A     B      Down             Up  12
2      1  A     A        Up             Up  13
3      1  A     A        Up             Up  14
4      2  B     B      Down           Down  -4
5      2  B     A        Up           Down  -3
6      2  B     A        Up           Down  -2
7      2  B     B      Down           Down  -1
8      3  A     A        Up             Up  13
9      3  A     A        Up             Up  14
10     3  A     B      Down             Up  15
11     3  A     B      Down             Up  16

score 1 · Answer 2 · answered Jan 07 '21 at 02:39

Here is how to do the Main direction column

df['testDirec'] = np.where(df['ID'] == df['Items'],df['Direction'],None)
df['testDirec'] = df['testDirec'].ffill()

Gives the same as Main Direction

    Time    ID  Items   Direction   Main Direction  X   testDirec
0   1       A   A       Up          Up              1   Up
1   1       A   B       Down        Up              2   Up
2   1       A   A       Up          Up              3   Up
3   1       A   A       Up          Up              4   Up
4   2       B   B       Down        Down            6   Down
5   2       B   A       Up          Down            7   Down
6   2       B   A       Up          Down            8   Down
7   2       B   B       Down        Down            9   Down
8   3       A   A       Up          Up              3   Up
9   3       A   A       Up          Up              4   Up
10  3       A   B       Down        Up              5   Up
11  3       A   B       Down        Up              6   Up

score 1 · Answer 3 · answered Jan 07 '21 at 02:39

You can use any custom function with apply. If you're operating row-wise (e.g. need more than one column) you'll call this with axis=1, and it will pass a row. Otherwise you can just do df['my_col'].apply(lambda x: do_something_to_x(x)) and it will pass values of the col. For your example:

def calc_dist(row):
    if row['Main Direction'] == 'Up':
        return row['X'] + 10
    else:
        return row['X'] - 10

df['Dist'] = df.apply(calc_dist, axis=1)

Map values to separate col using conditional statement: python

3 Answers3