How to shift values in a column based on a condition in pandas dataframe

Question

Hi I have a df like this,

    Name    sl no   details                 score
0   Ram     1       ram is going to ooty    NaN
1   Ram     2       ram sings well          1.5
2   Ravi    1       ravi play cricket       1.0
3   Ravi    2       ravi is in chennai      NaN
4   Kumar   1       kumar passed the exam   NaN
5   Kumar   2       kumar is in town        NaN
6   Kumar   3       he left                 3.0

I am trying to shift the values in the score column. values should moved to the cell where df[sl no]==1 or df[Name] is the first occurence of a name

My expected output should be like,

    Name    sl no   details                 score
0   Ram     1       ram is going to ooty    1.5
1   Ram     2       ram sings well          NaN
2   Ravi    1       ravi play cricket       1.0
3   Ravi    2       ravi is in chennai      NaN
4   Kumar   1       kumar passed the exam   3.0
5   Kumar   2       kumar is in town        NaN
6   Kumar   3       he left                 NaN

Please help.

cs95 · Accepted Answer · 2018-05-30 08:10:19Z

1

`next` in a List Comprehension

Conditionally call next on an iterator inside a list comprehension.

assert df['sl no'].eq(1).sum() == df['score'].notna().sum()

it = iter(df.score.dropna().tolist())
df['score'] = [
    next(it) if i else np.nan for i in df['sl no'].eq(1)
]

df
    Name  sl no                details  score
0    Ram      1   ram is going to ooty    1.5
1    Ram      2         ram sings well    NaN
2   Ravi      1      ravi play cricket    1.0
3   Ravi      2     ravi is in chennai    NaN
4  Kumar      1  kumar passed the exam    3.0
5  Kumar      2       kumar is in town    NaN
6  Kumar      3                he left    NaN

If your assert statement fails, there's something wrong with your data and what you're asking isn't feasible.

`loc`-based Assignment

v = df.score.dropna().tolist()

df['score'] = np.nan
df.loc[df['sl no'].eq(1), 'score'] = v

df
    Name  sl no                details  score
0    Ram      1   ram is going to ooty    1.5
1    Ram      2         ram sings well    NaN
2   Ravi      1      ravi play cricket    1.0
3   Ravi      2     ravi is in chennai    NaN
4  Kumar      1  kumar passed the exam    3.0
5  Kumar      2       kumar is in town    NaN
6  Kumar      3                he left    NaN

edited May 30, 2018 at 8:10

answered May 30, 2018 at 7:43

cs95

406k106 gold badges744 silver badges797 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Pyd Over a year ago

assert is not failing but when I run the last line I am getting StopIteration: error

cs95 Over a year ago

@pyd I think you ran the first it = ... command once and the other line twice. Run both of them together, and just once

cs95 Over a year ago

@pyd Added another simple option. No replace, no groupby, no transform.

Pyd Over a year ago

can you answer this question @coldspeed datascience.stackexchange.com/questions/32578/…

cs95 Over a year ago

@pyd good question. I'm not sure, but if you are looking to compare vectors, I'd recommend taking a look at similarity measures like cosine or haversine distance.

|

Joe · Accepted Answer · 2018-05-30 08:02:00Z

You can try so:

df['score'] = (df['score'].replace('',np.nan).groupby(df['Name']).transform(lambda x: x.bfill().ffill()))
df.loc[df['sl no'] != 1, 'score'] = np.NaN

First fill the column score with the same values:

    Name  sl no               details  score
0    Ram   1     ram is going to ooty    1.5
1    Ram   2           ram sings well    1.5
2   Ravi   1        ravi play cricket    1.0
3   Ravi   2       ravi is in chennai    1.0
4  Kumar   1    kumar passed the exam    3.0
5  Kumar   2         kumar is in town    3.0
6  Kumar   3                  he left    3.0

And then remove where the column sl no is not 1

    Name  sl no              details  score
0    Ram   1    ram is going to ooty    1.5
1    Ram   2          ram sings well    NaN
2   Ravi   1       ravi play cricket    1.0
3   Ravi   2      ravi is in chennai    NaN
4  Kumar   1   kumar passed the exam    3.0
5  Kumar   2        kumar is in town    NaN
6  Kumar   3                 he left    NaN

Collectives™ on Stack Overflow

How to shift values in a column based on a condition in pandas dataframe

2 Answers 2

`next` in a List Comprehension

`loc`-based Assignment

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

next in a List Comprehension

loc-based Assignment

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related

`next` in a List Comprehension

`loc`-based Assignment