Write Your Own XLWings Python Functions For Excel - Tutorial
Learn to write python for Excel. In this video, I demonstrate some Python XlWings functions and how to write and create them using Python and Excel.
Combining Python with Excel is a great way to learn Python because you can make Python useful from the get go, as your Python functions results are returned to a spreadsheet or VBA macro that you're used to.
Previous Python Excel Video - • Download and install xlwings and pyth...
✅ My Favourite Python Book https://amzn.to/3mvpb1K
✅ Python for Excel Book https://amzn.to/41Rrpb3
Blog Post For This Video https://www.businessprogrammer.com/xl...
☕
If you found this tutorial helpful consider supporting this channel with a steaming hot cup of coffee... https://Ko-fi.com/seanbjohnson
☕
Video Production
===============
Sony Camera: https://amzn.to/3mqyqfu
Elgato HDMI Stream Capture: https://amzn.to/2Yfyrum
Microphones
-----------
Screen Casting: https://amzn.to/3a2TnHN
Microphone and Arm : https://amzn.to/3iwvL2S
Talking To Camera: https://amzn.to/3FqXI6b
Video Kit
-------------
Lights
Elgato video Lights: https://amzn.to/3sSK3xg
Background Light: https://amzn.to/3uTXyPh
Note Taking
-----------
Rocketbook reusable notebook https://amzn.to/3sSK3xg
#======================================
Start Functions mentioned in the video
#======================================
Put following two lines at top of module
import pandas as pd
import csv
def writeFileToList(szFileName):
I've had to use the csv module with reader because csv files's aren't always clean, meaning commas can appear
for non parsing purposes, the quotechar=',' detects when this happens and acts accordingly.
this works when the csv follows the practice of quoting fields that contain a comma as part of the grammar.
lstData = []
with open(szFileName, 'r', encoding='latin-1') as oF:
reader = csv.reader(oF, quotechar='"')
for row in reader:
lstData.append(row)
return lstData
def internalBuildOfContents(szFileName):
Open file and load to list
lstData = writeFileToList(szFileName)
Write list to dataframe
dfContents = pd.DataFrame(lstData)
Copy first row of data frame to the header (as row one contains the header data)
dfContents.columns = dfContents.iloc[0]
Remove this first row now from the dataframe as it's now represented in the header.
This is done by rewriting the dataframe to hold column 1 (as opposed to 0) onwards
dfContents = dfContents[1:]
After reviewing the dataframe in an interactive session I know that I only want the first 10 columns (0,9) so ..
Give me all rows but restrict columns to list items below
dfContents = dfContents.loc[ : , ['Song Clean', 'ARTIST CLEAN', 'CALLSIGN']]
Now get rid of any duplicates
dfContents = dfContents.drop_duplicates()
Add an new field which concatenates the two text fields.
dfContents["UniqueC"] = dfContents["Song Clean"] + dfContents["ARTIST CLEAN"]
return dfContents
#======================================
End Functions mentioned in the video
#======================================
DISCLAIMER: This video and description contain affiliate links, this means if you click on one of the product links, I’ll receive a small commission.
If you do use one of the links, thanks I appreciate it.
