Data wrangling of Excel file produced by Wandb.ai
It’s best to keep acting real. — Author: Liam Neeson
Nutshell: Deleting Excel column containing a specific word in the header using VBA.
Wandb.ai provides a central dashboard to keep track of your hyperparameters, system metrics, and predictions so you can compare models live and share your findings. This allows researchers to plot charts out of the logged data; Simultaneously, it also plots the default values logged over a period of time.
Motivation
As lots of information gets logged into the Wandb, it becomes easier to utilise all those information to log meaningful charts.
A problem arises when someone wishes to download those charts' data into an Excel file for more processing. As lovely as the data on those charts look, underneath supporting data come out to be huge in Excel.
Last night I was working on a nice nifty sci-fi chart which was great, but then I realised I needed to do more processing on that data. For that, I downloaded the chart data as Excel and found a massive number of data fields, roughly around 256 attributes; And more than half of them were not needed. So I searched for filter functionality in wandb, but it wasn't present at the time of writing: (Hopefully, it will come out sooner but after my post).
One fix one can think of is to do Ctrl +F and find your column of interest and move them to a new sheet, but I took the opportunity to learn a handy macro that is worth sharing with the DataScience community.
Resolution
Contribution: How to remove unnecessary columns in Excel if their name contains ('somethingThatIDontKnowYet')?
Steps to resolve the issue:
- Find the pattern in your header column in your Excel file, which is not required for further processing. For example, I wanted to remove the columns with suffixes as _steps, _MAX, and _MIN.
2. Once you have identified the 'text' in your columns that you wish to delete, next is to fire some VBA commands to get it removed.
For Windows 10 users, it's Ctrl + F11. This will open a new Macro sheet for you. Copy all your data from the wandb sheet to the Macro sheet. Then right-click on any cell of the macro sheet and select the Run menu item.
3. Give a name to your macro. I called it DeleteSpecificCell (which should be a DeleteColumnsContaining; the brain got its funny ways of doing things 😐). A VBA editor will open once you click on create, as shown in the right-hand side image underneath.
In your VBA editor, you will be seeing two lines initially:
Sub DeleteSpecificCell() <<We will be writing code in here>>End Sub
Inside the code block, write:
Set MR = Range("A1:EI1")
For Each cell In MR
If InStrB(1, cell.Value, "step", vbBinaryCompare) <> 0 Then cell.EntireColumn.Delete
NextCode block explanation:
# Underneath line represents the selection of columns in the workheet which will be considered while running macro
Set MR = Range("A1:EI1")
For Each cell In MR
# Underneath line tells macro to delete entire column where header contains "step" replace this with your identified text.
If InStrB(1, cell.Value, "step", vbBinaryCompare) <> 0 Then cell.EntireColumn.Delete
Next
3. Voila!! The recipe is cooked. Press F5 once you fill in the data and go back to the Macro sheet to see the magic. I was able to reduce 256 columns to 65 columns for further processing.
This way, Wandb customers processed their data happily ever after.
Happy Coding!!
