How to fix displaced rows in excel with pandas?

Question

I need to fix a large excel database where in some columns some cells are blank and all the data from the row is moved one cell to the right. For example:

In this example I need a script that would detect that the first cell form the last row is blank and then it would move all the values one cell to the left.

I'm trying to do it with this function. Vencli_col is the dataset, df1 and df2 are copies. In df2 I drop column 12, which is where the error originates. I index the rows where the error happens and then I try to replace them with the values from df2.

df1 = vencli_col.copy()
df2 = vencli_col.copy()
df2 = df1.drop(columns=['Column12'])
df2['droppedcolumn'] = np.nan

i = 0
col =[]
for k, value in vencli_col.iterrows():
    i +=1
    if str(value['Column12']) == '' or str(value['Column12']) == str(np.nan):
        col.append(i+1)
      

for j in col:
    df1.iloc[j] = df2.iloc[j]
    
df1.head(25)

Answer 1

You could do something like the below. It is not very pretty but it does the trick.

# Select the column names that are correct and the ones that are shifted 
# This is assuming the error column is the second one as in the image you have
correct_cols = df.columns[1:-1]
shifted_cols = df.columns[2:]
# Get the indexes of the rows that are NaN or ""
df = df.fillna("")
shifted_indexes = df[df["col1"] == ""].index

# Shift the data 1 column to the left
# It has to be transformed in numpy because if you don't the column names 
# prevent from copying in the destination columns
df.loc[shifted_indexes ,correct_cols] = df.loc[shifted_indexes, shifted_cols].to_numpy()

EDIT: just realised there is an easier way using df.shift()

columns_to_shift = df.columns[1:]
shifted_indexes = df[df["col1"] == ""].index
df.loc[shifted_indexes, columns_to_shift] = df.loc[shifted_indexes, columns_to_shift].shift(-1, axis=1)