CODEWITHSUNDEEP
pythonpandasdata-wrangling
Will Al
Will Al

Reputation: 3

Moving rows value to another existing column

I have a messy datasets as attached below

  Sales Credit type      Year     Status
0   NaN          GS      2000  Confirmed
1   NaN           V      2000   Assigned
2    GS        2001  Assigned        NaN
3     V        2004  Received        NaN

I am trying to move over the corresponding value into the right columns. So ideally should be like this one.

  Sales Credit type  Year     Status
0   NaN          GS  2000  Confirmed
1   NaN           V  2000   Assigned
2   NaN          GS  2001   Assigned
3   NaN           V  2004   Received

I have tried to find the solutions in this platforms but no luck. I used df.loc to placed the datasets but seems like the result is not like what I expected. I would really appreciate your support for solving this issue. Thank you

*Update

It works with @jezrael solution, thanks! but is it possible if we use it for this case?

   ID  Sales Credit_type      Year     Status
0  1   Aston      GS        2000   Confirmed
1  1   NaN        V        2000    Assigned
2  2    GS        2001    Assigned        NaN
3  3    V         2004    Received        NaN

And the result should be like this:

   ID  Sales Credit_type      Year     Status
0  1   Aston         GS       2000       Confirmed
1  1   NaN           V       2000       Assigned
2  2   NaN          GS       2001       Assigned   
3  3   NaN           V       2004       Received

Upvotes: 0

Views: 146

Answers (1)

jezrael
jezrael

Reputation: 862671

You can create mask by last column for test if missing values by Series.isna and then use DataFrame.shift with axis=1 only for filtered rows:

m = df.iloc[:, -1].isna()
df[m] = df[m].shift(axis=1)
print (df)
  Sales Credit type  Year     Status
0   NaN          GS  2000  Confirmed
1   NaN           V  2000   Assigned
2   NaN          GS  2001   Assigned
3   NaN           V  2004   Received

If need set all columns without first use DataFrame.iloc with indexing .iloc[m, 1:]:

m = df.iloc[:, -1].isna().to_numpy()
df.iloc[m, 1:] = df.iloc[m, 1:].shift(axis=1)
print (df)
   ID  Sales Credit_type  Year     Status
0   1  Aston          GS  2000  Confirmed
1   1    NaN           V  2000   Assigned
2   2    NaN          GS  2001   Assigned
3   3    NaN           V  2004   Received

Upvotes: 6

Related Questions