Use python to remove blank cells from column and shift populated cells up

Question

I have an excel file that I want to use Python to move the filled-in cells up and eliminate all the empty cells in column A.

I'm not sure how to achieve this; any suggestions? I am aware that I can perform this task manually in Excel, but I need to automate this procedure. I need to automatically update this so that all the filled-in cells in column A really sit next to the rest of the data in columns B, C, D, and E in the several files that will be created that will all look exactly like this.

Answer 1

Use pandas to import and modify your excel.

import pandas as pd
df = pd.read_excel('yourfile.xlsx')
df = df.apply(lambda x: pd.Series(x.dropna().values)).fillna(' ')