4

Here is the example data set

id         firstname    lastname      email               update date
A1         wendy         smith         [email protected]        2018-01-02
A1         wendy         smith         [email protected]     2019-02-03 
A2         harry         lynn          [email protected]      2016-04-03
A2         harry                       [email protected]  2019-03-12
A3         tinna         dickey        [email protected]     2016-04-03
A3         tinna         dickey        [email protected]     2013-06-12
A4         Tom           Lee           [email protected]       2012-06-12
A5         Ella                        [email protected]      2019-07-12
A6         Ben           Lang          [email protected]       2019-03-12

I have sorted the data set by id and update date, I want to merge the rows with same id, if one row with empty value, fill the other one with same id, if confilct, use the latest one. For rows with no duplicate id leave the empty cell as it is.

the output should be:

id         firstname    lastname      email               update date
A1         wendy         smith         [email protected]     2019-02-03 
A2         harry         lynn          [email protected]  2019-03-12
A3         tinna         dickey        [email protected]     2019-03-12
A4         Tom           Lee           [email protected]       2012-06-12
A5         Ella                        [email protected]      2019-07-12
A6         Ben           Lang          [email protected]       2019-03-12

my attempt was using ffill() to merge rows with empty and keep last duplicate, but the result seems to affect other cells which should have empty values(like lastname in A5 should be empty ). 

df=df.ffill().drop_duplicates('id',keep='last')
Erfan
  • 40,971
  • 8
  • 66
  • 78
user2748930
  • 83
  • 1
  • 2
  • 8

4 Answers4

4

Use GroupBy.ffill to only forward fill for the same group. Then use drop_duplicates:

df['lastname'] = df.groupby('id')['lastname'].ffill()
df = df.drop_duplicates('id', keep='last')

Or in one line (but less readable in my opinion), using assign:

df.assign(lastname=df.groupby('id')['lastname'].ffill()).drop_duplicates('id', keep='last')

Output

   id firstname lastname              email update date
1  A1     wendy    smith     [email protected]  2019-02-03
3  A2     harry     lynn  [email protected]  2019-03-12
5  A3     tinna   dickey     [email protected]  2013-06-12
6  A4       Tom      Lee       [email protected]  2012-06-12
7  A5      Ella      NaN      [email protected]  2019-07-12
8  A6       Ben     Lang       [email protected]  2019-03-12
Erfan
  • 40,971
  • 8
  • 66
  • 78
  • 1
    if other columns contain empty values that need to be grouped, can I add more lines like `df['email'] = df.groupby('id')['email'].ffill()` ... before drop duplicates? Thanks – user2748930 Oct 03 '19 at 14:32
1

Use

Ex.

df = df.replace('',np.nan, regex=True)
df1 = df.groupby('id',as_index=False,sort=False).last()
print(df1)

   id firstname lastname              email  updatedate
0  A1     wendy    smith     [email protected]  2019-02-03
1  A2     harry     lynn  [email protected]  2019-03-12
2  A3     tinna   dickey     [email protected]  2013-06-12
3  A4       Tom      Lee       [email protected]  2012-06-12
4  A5      Ella      NaN      [email protected]  2019-07-12
5  A6       Ben     Lang       [email protected]  2019-03-12
bharatk
  • 4,202
  • 5
  • 16
  • 30
1

Try this:

df.groupby('id').ffill().drop_duplicates('id', keep='last')

output:

   id firstname lastname              email  update date
1  A1     wendy    smith     [email protected]  2019-02-03 
3  A2     harry     lynn  [email protected]   2019-03-12
5  A3     tinna   dickey     [email protected]   2013-06-12
6  A4       Tom      Lee       [email protected]   2012-06-12
7  A5      Ella      NaN      [email protected]   2019-07-12
8  A6       Ben     Lang       [email protected]   2019-03-12
Divya Dass
  • 166
  • 1
  • 10
  • because of `ffill()` , `id` won't be set as an index – Divya Dass Oct 03 '19 at 12:30
  • 1
    @ Divya Dass When I tried your code, I got an error `KeyError: Index(['id'], dtype='object')` Do you know what might cause the error? thanks – user2748930 Oct 03 '19 at 14:27
  • @user2748930 Not sure why you are getting this error. Erfan also stated to have got this error earlier. This error means that `id` has become an index in your case. In my case it had become a column after I had used ffill(). Therefore, I used drop_duplicates() after this. In your case `df.groupby('id').ffill().reset_index().drop_duplicates('id', keep='last')` might help. This will make `id` a column if it has become index in your code. – Divya Dass Oct 03 '19 at 15:20
0

Use a combination of groupby, apply, and iloc:

df.groupby('id', as_index=False).apply(lambda x: x.fillna(method='ffill').iloc[0])

   id firstname lastname              email  update date
0  A1     wendy    smith     [email protected]  2019-02-03
1  A2     harry     lynn  [email protected]  2019-03-12
2  A3     tinna   dickey     [email protected]  2019-03-12
3  A4       Tom      Lee       [email protected]  2019-06-12
4  A5      Ella      NaN      [email protected]  2019-07-12
5  A6       Ben     Lang       [email protected]  2019-03-12
  • groupby groups the dataframe by unique ids
  • fillna fills all the NaN values with the row with non-NaN values
  • iloc[-1] gets you the row with the latest data
adrianp
  • 999
  • 1
  • 8
  • 13