Python get value from different rows and columns

Question

I have a dataframe like: the dataframe stores phone number, favorite food, and favorite toy for the kids (signed with different Ids). The data is separate in different rows and columns. Some rows may only has Id but nothing else. Input may looks like:

|Id|phone_number|food  |toy |
|--|------------|------|----|
|01|            |apple |    |
|01|9995552222  |banana|    |
|01|            |      |ball|
|01|9995552222  |orange|    |
|02|3332226666  |      |    |
|02|            |boba  |    |
|02|            |      |    |

What I want to get: I want to combine the value in different rows together to make each row unique. The output may looks like:

|Id|phone_number|food  |toy |
|--|------------|------|----|
|01|9995552222  |apple |ball|
|01|9995552222  |banana|ball|
|01|9995552222  |orange|ball|
|02|3332226666  |boba  |    |

Thank you

test = pd.DataFrame({'Id': ['01', '01', '01', '01', '02', '02', '02'], 
                'phone_number': ['', '9995552222', '', '9995552222', '3332226666', '', ''], 
                'food': ['apple', 'banana', '', 'orange', '', 'boba', ''], 
                'toy': ['', '', 'ball', '', '', '', '']})

Ynjxsjmh · Accepted Answer · 2022-05-18 19:37:00Z

2

You can try groupby Id column then fill the NaN column with bfill and ffill. At last drop the duplicates in 'phone_number', 'food', 'toy'.

test = test.replace('', pd.NA)

out = (test.groupby('Id')
       .apply(lambda g: g.bfill().ffill())
       .drop_duplicates(['phone_number', 'food', 'toy']) # 'toy ' in your given dataframe
       .fillna('')
       )

print(df)

   Id phone_number    food   toy
0  01   9995552222   apple  ball
1  01   9995552222  banana  ball
2  01   9995552222  orange  ball
4  02   3332226666    boba

answered May 18, 2022 at 19:37

Ynjxsjmh

30.3k7 gold badges43 silver badges64 bronze badges

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

3 Comments

1st Person Over a year ago

Thank you so much. It works as what I want!

Onyambu Over a year ago

Why do apply? test.mask(test == '', np.nan).groupby('Id').ffill().bfill().drop_duplicates() should be enough

Ynjxsjmh Over a year ago

@onyambu Only the first ffill() works on group, the second bfill() works on Series.

Collectives™ on Stack Overflow

Python get value from different rows and columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related