Clean Excel Data With Python Pandas – Removing Unwanted Characters

Hey Everyone, in this one we’re looking at the replace method in pandas to remove characters from your spreadsheet columns.

Be sure to post what you want to see next!

Support the Channel on Patreon —

Join The Socials —
Reddit –
FB –
Insta –
Twitter –
LinkedIn –
GitHub –
Full code from the video:

import pandas as pd

excel_file_path = ‘office_info.xlsx’
df = pd.read_excel(excel_file_path)


for column in df.columns:
df[column] = df[column].str.replace(r’W’,””)


Packages (& Versions) used in this video:
Python 3.8
Pandas 0.25
Atom 1.41

Code from this tutorial and all my others can be found on my GitHub:

Check out my website:

If you liked the video – please hit the like button. It means more than you know. Thanks for watching and thank you for all your support!!

Always looking for suggestions on what video to make next — leave me a comment with your project! Happy Coding!


Xem thêm bài viết khác:


  1. Thanks for being here! What do you want to see next?

  2. Thanks, Derrick. I love this cause I work with many spreadsheets.

  3. Thank you Derrick for sharing this. It helped me a lot. Can you please help me on how to do this –
    Remove special characters from the column headings and replace all spaces with single underscore

  4. How to create a dashboard in python by using importing raw data from Excel?

  5. HI please do a demo on searching for specific characters in a cell (Search and contain)

  6. Thanks for this great video! May I know what text editor do you use? Im using Jupyternotebook, and it seems like your text editor is better, as it has auto fill up function

  7. How to deal with blanks and nans!

  8. I love this much. Thanks.

  9. please consider placing your face further from camera xD

  10. Thanks man! This was very helpful also to remove unnecessary character from csv data.

  11. hey, what should i do if i need to remove all the special characters and also the numbers

  12. How to remove all special characters including space from email address column excluding @ and dot.

  13. I love your videos, but I want to make an interface data entry form by python which feeds the data to the sql server or excel shee or google sheet. Thanks in advance.

  14. Great Work Dude… Can you make a video on extracting and cleaning data from excel file? e.g. those excel sheets contain some row as headers or uneven number of rows.

  15. Hi Derrick! I have a problem… (r'W', '') deleted all the special characters but also it deleted the spaces ! how can i keep the spaces?

  16. Great!! Thankyou. But when i execute the for loop for removing the special character ,cell consisting of ''no special character'' or noise word is giving empty cell . While exporting, I am getting only the clean data..I dont know why is this happening.. Please help!!

  17. What would be the other solutions?

  18. But for the r'' is for raw string?, Can we use it for regex also?

  19. Thanks for this. Oddly my excel file has all columns formatted as text. However, running the script throws an error AttributeError: Can only use .str accessor with string values! This is due to one of the columns is not a string, its a float64 or something. Any advice on how to correct this?

  20. Excellent job
    How about an advanced regex tutorial? That would be awesome to have.

  21. You got my subscribe , thanks so much

  22. Nice video. Very explanatory. The report I generate on a weekly basis is so messy. The software churns out four reports on a single sheet. When loaded in pandas most columns are lost in the dataframe and their corresponding column names in pandas becomes unnamed.. still figuring out the best way to handle that report. I could clean it in Excel but want to automate it with python

  23. Thanks. Very useful for me since I’m learning Pandas.

  24. Another great video! You are very good at explaining things.

  25. Thank you so much for sharing Derrick! This video is really helpful. Also, how can we remove the unwanted characters but still keep the space?

  26. Your a smart guy ty for your videos is there a way of making a username generator to generate a bunch of names so I can choose a name for my daughter's bday party

Leave a Reply

Your email address will not be published. Required fields are marked *