Getting a new column in pandas based on null values from another column

Question

This is how my dataframe looks like:

account_type  picture                                          video 
twitter       NULL                                             NULL
twitter       https://pbs.twimg.com/media/EPlqKxKUEAARR_x.jpg  NULL
twitter       https://pbs.twimg.com/media/EPlqKxKUEAARR_x.jpg  https://video.twimg.com/a
twitch        NULL                                             https://twitch.tv/
instagram     https://scontent-lga3-1.cdninstagram.com         NULL
instagram     https://video-iad3-1.xx.fbcdn.net                https://www.instagram.com/p
facebook      https://graph.facebook.com/2                     NULL
facebook      NULL                                             https://www.facebook.com/t
youtube       https://i.ytimg.com/vi                           https://www.youtube.com/w

And this is what I want to make it look:

account_type  picture                                          video                          post_type
twitter       NULL                                             NULL                           text
twitter       https://pbs.twimg.com/media/EPlqKxKUEAARR_x.jpg  NULL                           picture
twitter       https://pbs.twimg.com/media/EPlqKxKUEAARR_x.jpg  https://video.twimg.com/a      video
twitch        NULL                                             https://twitch.tv/             video
instagram     https://scontent-lga3-1.cdninstagram.com         NULL                           picture
instagram     https://video-iad3-1.xx.fbcdn.net                https://www.instagram.com/p    video
facebook      https://graph.facebook.com/2                     NULL                           picture
facebook      NULL                                             https://www.facebook.com/t     video
youtube       https://i.ytimg.com/vi                           https://www.youtube.com/w      video

Basically I am trying to segregate each row into a picture/video/text.

For twitter, instagram 
    > if columns 'picture' and 'video are NULL,'post_type'= text 
    > if columns 'picture' is NOT NULL and 'video' is NULL, 'post_type'= picture  
    > if columns 'picture' is NOT NULL and 'video' is NOT NULL, 'post_type'= video 

for twitch, youtube 
    > if 'video' is NOT NULL ,'post_type' = video 

for facebook 
    > if 'video' is NULL ,'post_type' = picture 
    > if 'video' is NOT NULL ,'post_type' = video

I am trying to create this based on null/notnull criterias. This is what I tried:

df['newtype'] = np.where(df['picture'].isnull(), '', 'picture')
df['newtype2'] = np.where(df['video'].isnull(), '', 'video')

But this creates new columns. I want everything in one column with the conditions specified. Please tell me if there is a better way to do this.

azro · Accepted Answer · 2020-09-17 17:25:29Z

4

You may use the construction df.loc[condition, 'column'] and write yours

# twitter-instagram
df.loc[(df['account_type'].isin(['twitter', 'instagram'])) &
       df['video'].isnull() &
       df['picture'].isnull(), 'post_type'] = 'text'

df.loc[(df['account_type'].isin(['twitter', 'instagram'])) &
       df['video'].isnull() &
       ~df['picture'].isnull(), 'post_type'] = 'picture'

df.loc[(df['account_type'].isin(['twitter', 'instagram'])) &
       ~df['video'].isnull() &
       ~df['picture'].isnull(), 'post_type'] = 'video'

# twitch-youtube
df.loc[(df['account_type'].isin(['twitch', 'youtube'])) & ~df['video'].isnull(), 'post_type'] = 'video'

# facebook
df.loc[(df['account_type'] == 'facebook') & df['video'].isnull(), 'post_type'] = 'picture'
df.loc[(df['account_type'] == 'facebook') & ~df['video'].isnull(), 'post_type'] = 'video'

answered Sep 17, 2020 at 17:25

azro

54.2k9 gold badges39 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zlatan Over a year ago

Thank you so much. This works like a charm. I had no idea we could use df.loc like that as well. Any tips/sources where I can sharpen my pandas skills?

anky Over a year ago

May be also you can look at np.select for multiple condition and multiple results :) didnt test it but looks like it would fit the usecase

Collectives™ on Stack Overflow

Getting a new column in pandas based on null values from another column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related