CODEWITHSUNDEEP
pythonpandas
Tatsuya
Tatsuya

Reputation: 127

Conditionally copy certain row values to other rows

I have a dataframe that has the following structure:

    code name age char
    101  NaN  NaN  ts
    101  NaN  NaN  tt
    101  Carl 19   tt
    102  NaN  NaN  ts
    102  NaN  NaN  tt
    102  NaN  NaN  tt
    103  NaN  NaN  ts
    103  Aoi  23   tt
    103  NaN  NaN  tt

I would like to copy values from columns "name" and "age" to other rows conditionally on them existing, having the same "code" and "char" being "ts". The resulting dataframe I would like to have is the following:

    code name age char
    101  Carl 19   ts
    101  NaN  NaN  tt
    101  Carl 19   tt
    102  NaN  NaN  ts
    102  NaN  NaN  tt
    102  NaN  NaN  tt
    103  Aoi  23   ts
    103  Aoi  23   tt
    103  NaN  NaN  tt

Thanks in advance for you help!

Upvotes: 1

Views: 84

Answers (2)

Mabel Villalba
Mabel Villalba

Reputation: 2598

In one line:

result = df.copy()

result.update(df.groupby(['code']).bfill()[df['char']=='ts'])

result

    code  name   age char
0  101.0  Carl  19.0   ts
1  101.0   NaN   NaN   tt
2  101.0  Carl  19.0   tt
3  102.0   NaN   NaN   ts
4  102.0   NaN   NaN   tt
5  102.0   NaN   NaN   tt
6  103.0   Aoi  23.0   ts
7  103.0   Aoi  23.0   tt
8  103.0   NaN   NaN   tt

Explained

The dataframe is filled wiht rows with 'char' == 'ts' that have been filled with any available value for the code by doing:

df.groupby(['code']).bfill()
   code  name   age char
0   101  Carl  19.0   ts
1   101  Carl  19.0   tt
2   101  Carl  19.0   tt
3   102   NaN   NaN   ts
4   102   NaN   NaN   tt
5   102   NaN   NaN   tt
6   103   Aoi  23.0   ts
7   103   Aoi  23.0   tt
8   103   NaN   NaN   tt


df.groupby(['code']).bfill()[df['char']=='ts']

   code  name   age char
0   101  Carl  19.0   ts
3   102   NaN   NaN   ts
6   103   Aoi  23.0   ts

Upvotes: 1

jezrael
jezrael

Reputation: 862481

Use GroupBy.apply with forward and back filling missing values ànd then set values by boolean mask:

cols = ['name','age']
df1 = df.groupby(['code'])[cols].apply(lambda x: x.ffill().bfill())

mask = df['char'] == 'ts'
df.loc[mask, cols] = df1.loc[mask, cols]
print (df)
   code  name   age char
0   101  Carl  19.0   ts
1   101   NaN   NaN   tt
2   101  Carl  19.0   tt
3   102   NaN   NaN   ts
4   102   NaN   NaN   tt
5   102   NaN   NaN   tt
6   103   Aoi  23.0   ts
7   103   Aoi  23.0   tt
8   103   NaN   NaN   tt

Upvotes: 1

Related Questions