CODEWITHSUNDEEP
pythonpandasdataframedata-analysis
Pyd
Pyd

Reputation: 6159

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

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.

Upvotes: 2

Views: 2722

Answers (2)

cs95
cs95

Reputation: 402523

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

Upvotes: 1

Joe
Joe

Reputation: 12417

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

Upvotes: 1

Related Questions