Creating a Column Based on Column Text in Pandas Python

Question

I have a Dataframe (series) 'df' that looks like the following:

Name
A1001
A1002
B1001
C1001
A1003
B1002
B1003
C1002
D1001
D1002

I want to create a new column called Company which should read 'Alpha' if text starts with 'A', 'Bravo' if text starts with 'B' and 'Others' if text starts with anything else.

I tried:

df['Company'] = 'Alpha' if df.Name.str.startswith('A') else 'Other'

But it gave me an error:

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Expected Output:

Name     Company
A1001    Alpha
A1002    Alpha
B1001    Bravo
C1001    Other
A1003    Alpha
B1002    Bravo
B1003    Bravo
C1002    Other
D1001    Other
D1002    Other

How can it be done?

jezrael · Accepted Answer · 2020-01-11 12:22:30Z

3

Use numpy.select with Series.str.startswith:

df['Company'] = np.select([df.Name.str.startswith('A'), 
                           df.Name.str.startswith('B')], 
                           ['Alpha', 'Bravo'], 
                           default='Other')
print (df)
    Name Company
0  A1001   Alpha
1  A1002   Alpha
2  B1001   Bravo
3  C1001   Other
4  A1003   Alpha
5  B1002   Bravo
6  B1003   Bravo
7  C1002   Other
8  D1001   Other
9  D1002   Other

edited Jan 11, 2020 at 12:22

answered Jan 11, 2020 at 12:12

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

1 Comment

therion Over a year ago

It' working, but the values are not matching. Some of the As are assigned 'Other'.

Erfan · Accepted Answer · 2020-01-11 12:15:49Z

2

Use np.select to create multi-conditional column:

letter = df['Name'].str[0]
df['Company'] = np.select([letter.eq('A'), letter.eq('B')], ['Alpha', 'Bravo'], default='Other')

    Name Company
0  A1001   Alpha
1  A1002   Alpha
2  B1001   Bravo
3  C1001   Other
4  A1003   Alpha
5  B1002   Bravo
6  B1003   Bravo
7  C1002   Other
8  D1001   Other
9  D1002   Other

Same approach, but now we use a more "self explaining" code:

letter = df['Name'].str[0]
conditions = [letter.eq('A'), letter.eq('B')]
choices = ['Alpha', 'Bravo']

df['Company'] = np.select(conditions, choices, default='Other')

answered Jan 11, 2020 at 12:15

Erfan

43.3k10 gold badges75 silver badges86 bronze badges

Collectives™ on Stack Overflow

Creating a Column Based on Column Text in Pandas Python

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related