Read Messy & Poorly Structured Excel Files Using Pandas (Python)

👉 Explore All My Excel Solutions: pythonandvba.com/solutions
𝗗𝗘𝗦𝗖𝗥𝗜𝗣𝗧𝗜𝗢𝗡
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
In the real world, Excel files are not always so neat & clean as the ones you will find in many tutorials. I will walk you through how to read messy and poorly structured Excel Files using pandas.
In particular, I will cover the following options in the pandas read_excel() function:
pandas.read_excel(
io,
sheet_name=0,
header=0,
usecols=None,
true_values=None,
skipfooter=0
converters=None,
dtype=None,
)
You can find the Jupyter Notebook from the tutorial here [Google Drive]:
👉 bit.ly/2M7iGzX
𝗧𝗢𝗢𝗟𝗦 𝗔𝗡𝗗 𝗥𝗘𝗦𝗢𝗨𝗥𝗖𝗘𝗦
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
🆓【𝗙𝗥𝗘𝗘】Excel Add-in (𝗠𝘆𝗧𝗼𝗼𝗹𝗕𝗲𝗹𝘁): pythonandvba.com/mytoolbelt
📊 Dashboard Excel Add-In (𝗚𝗿𝗮𝗳𝗹𝘆): pythonandvba.com/grafly
🎨 Cartoon Charts Excel Add-In (𝗖𝘂𝘁𝗲𝗣𝗹𝗼𝘁𝘀): pythonandvba.com/cuteplots
🤪 Fun Emoji Excel Add-In (𝗘𝗺𝗼𝗷𝗶𝗳𝘆): pythonandvba.com/emojify
📑 Excel Templates: pythonandvba.com/go/excel-tem...
🎓 My Courses: pythonandvba.com/go/courses
📚 Books, Tools, and More: pythonandvba.com/resources
𝗖𝗢𝗡𝗡𝗘𝗖𝗧 𝗪𝗜𝗧𝗛 𝗠𝗘
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
🔗 LinkedIn: / sven-bosau
📸 Instagram: / codingisfun_official
💻 GitHub: github.com/Sven-Bo
💬 Discord: pythonandvba.com/discord
📬 Contact: pythonandvba.com/contact
☕ 𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲?
If you want to support this channel, you can buy me a coffee here: pythonandvba.com/coffee-donation

[@pon00050]

Oh, I am delighted to see your new video!

[@CodingIsFun]

Thank you! I am happy to hear that 😃

[@SilentWarrior17]

Hey! just what I needed! Thank you for making this video the way you did it. . .Straightforward and efficient. . .

[@CodingIsFun]

Thanks for the positive feedback! Appreciate you taking the time to leave a comment. 👍

[@Perspicacious_pi]

Getting hooked on your videos! Awesome content as usual - Thanks!

[@CodingIsFun]

I am glad to hear you are enjoying them, thanks for watching and for the comment!

[@mohammedalbatati5529]

Loved it , many thanks for this great video I didn’t know about the xlwings until I saw your channel 👍👍

[@CodingIsFun]

*I am glad you liked the video, thanks for watching and for the comment!* I also have a separate tutorial on xlwings, perhaps you might want to check it out 😃 👉 kzbin.info/www/bejne/lZKzkmydj5amZq8

[@ermalgashimramori]

Great video, as always explanations are crystal clear.

[@CodingIsFun]

Thank you very much! I greatly appreciate your support 🙏

[@samratkundu8737]

By far this is the best video of this topic I've ever seen. subscribed and wating for more content.. Thank you so much 🙏

[@CodingIsFun]

Thanks for the kind words and welcome aboard! I am glad you enjoyed it.

[@fernandogimenez4403]

Thanks so much for this video! I learned a lot about pandas and data cleaning with Pythin. You are the best!

[@CodingIsFun]

Thank you very much for watching the video and for your kind feedback. Happy to hear that it was helpful! :)

[@KingLiongWong]

This video is so helpful to me. Thanks!

[@CodingIsFun]

Glad it was helpful!

[@godfreytan1001]

This is such an excellent tutorial.

[@CodingIsFun]

Thank you!

[@ailtonbiji5024]

Thanks for this video, really great content and help...

[@CodingIsFun]

Glad you liked it. Thanks for watching and taking the time to leave a comment! Cheers, Sven ✌️

[@brajeshahirwar]

Thanks! Just what i need!

[@CodingIsFun]

Happy to hear that it was useful; thank you for taking the time to leave a comment and for watching the video!

[@TheReluctantCoder]

very useful, thank you so much!

[@CodingIsFun]

Glad you liked it. Thanks for watching and taking the time to leave a comment!

[@aberebrian8880]

Best tutorial so far

[@CodingIsFun]

Thanks! :)

[@viper1424]

Great tutorial thanks

[@CodingIsFun]

Thank you! 🙂

[@florianroeseler2404]

Super useful!

[@CodingIsFun]

Thank you Florian. Glad you enjoyed the video!

[@datastako156]

Thank you sir!

[@CodingIsFun]

A pleasure, thanks for watching!

[@helmutfechner9436]

Vielen Dank fürs Teilen.

[@CodingIsFun]

Danke fürs Anschauen!

[@JuanPerez-iu9vk]

I didn't get why would you use xlwings for loading data, something which can be done with openpyxl so you only have to import one module instead ot two. Great video with everything else.

[@CodingIsFun]

Thanks for watching. I'm glad you liked the video. As mentioned in the video, the xlwings option was an additional method to convert data from your clipboard into a pandas DataFrame. It's just a quick and easy way to get your data into Python :)Cheers, Sven ✌️

[@Monduras]

how do you autofit the columns on export? ive tried diff methods and only some cols seem to get autofit?

[@CodingIsFun]

Have a look at the following solution: stackoverflow.com/a/40535454

[@gutihaz8913]

support!

[@CodingIsFun]

Thank you! 😃

[@maxbart1353]

ist das ein deutscher akzent? wie wäre es dann mit zusätzlichen videos auf deutsch? :-)

[@CodingIsFun]

Erwischt! 😅 Vielleicht in Zukunft 😉

[@semihguzel2525]

Thank you sir, its a great video. It helped me a lot.

[@CodingIsFun]

Happy to hear that it was useful; thank you for taking the time to leave a comment and for watching the video!