Pythonic way to filter columns and then create a new column

Question

I have a .xlsx file that I am opening with this code:

import pandas as pd

df = pd.read_excel(open('file.xlsx','rb'))
df['Description'].head

and I have the following result, which looks pretty good.

ID     | Description
:----- | :-----------------------------
0      | Some Description with no hash
1      | Text with #one hash
2      | Text with #two #hashes

Now I want to create a new column, keeping only words started with #, like this one:

ID     | Description                      |  Only_Hash
:----- | :-----------------------------   |  :-----------------
0      | Some Description with no hash    |   Nan
1      | Text with #one hash              |   #one
2      | Text with #two #hashes           |   #two #hashes

I was able to count/separate lines with #:

descriptionWithHash = df['Description'].str.contains('#').sum()

but now I want to create the column like I described above. What is the easiest way to do that?

Regards!

PS: it is supposed to show a table format in the question but I can't figure out why it is showing wrong!

jezrael · Accepted Answer · 2017-07-31 11:25:28Z

5

You can use str.findall with str.join:

df['new'] =  df['Description'].str.findall('(\#\w+)').str.join(' ')
print(df)
   ID                    Description           new
0   0  Some Description with no hash              
1   1            Text with #one hash          #one
2   2         Text with #two #hashes  #two #hashes

And for NaNs:

df['new'] = df['Description'].str.findall('(\#\w+)').str.join(' ').replace('',np.nan)
print(df)
   ID                    Description           new
0   0  Some Description with no hash           NaN
1   1            Text with #one hash          #one
2   2         Text with #two #hashes  #two #hashes

edited Jul 31, 2017 at 11:25

answered Jul 31, 2017 at 11:19

jezrael

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

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

1 Comment

MaxU - stand with Ukraine Over a year ago

this solution is much more elegant !

MaxU - stand with Ukraine · Accepted Answer · 2017-07-31 11:20:55Z

4

In [126]: df.join(df.Description
     ...:           .str.extractall(r'(\#\w+)')
     ...:           .unstack(-1)
     ...:           .T.apply(lambda x: x.str.cat(sep=' ')).T
     ...:           .to_frame(name='Hash'))
Out[126]:
   ID                    Description          Hash
0   0  Some Description with no hash           NaN
1   1            Text with #one hash          #one
2   2         Text with #two #hashes  #two #hashes

answered Jul 31, 2017 at 11:20

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Collectives™ on Stack Overflow

Pythonic way to filter columns and then create a new column

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