I want to shift columns using panads from left and replce raws based on value of this cell-CodePudding

here is the original dataframe I want raw in with ":" on column 4 to be replace by next columns form 5 to 9]1

Here is the result I want to look like]2

I tried my best can't figure it out

CodePudding user response：

I tried to recreate the problem with a dummy data.

data = {'col_1': ['1:', '1:', '1:', 1, 1], 
        'col_2': [2, 2, 2, 2, 2], 
        'col_3': [':', ":" , 3, 3, ":"], 
        'col_4': [4, 4, 4, 4, 4], 
        'col_5': [5, 5, 5, 5, 5]}
df = pd.DataFrame.from_dict(data)

This is what it looks like.

print(df)

   col_1  col_2 col_3  col_4  col_5
0    1:      2     :      4      5
1    1:      2     :      4      5
2    1:      2     3      4      5
3     1      2     3      4      5
4     1      2     :      4      5

If your data is similar to the dummy one that I created, the following code works

col_names = df.columns

for idx, col in enumerate(col_names):  # loop every columns to ensure
  indices = df[df[col] == ":"].index  # get indices of rows that has ":" value

  if len(indices) > 0:  # only shift if the row has ":" values
    for i in range(idx, len(col_names) - 1):  # shift the columns to left 1 by 1
      df.loc[indices, col_names[i]] = df.loc[indices, col_names[i 1]]
    
    df.loc[indices, col_names[i 1]] = ""  # set the rows from last column as empty

After running the code, your dataframe should be shifted. Like this

print(df)

   col_1  col_2 col_3  col_4 col_5
0    1:      2     4      5      
1    1:      2     4      5      
2    1:      2     3      4     5
3     1      2     3      4     5
4     1      2     4      5