How to merge two rows having same values into single row in python?

Question

I am having a table called 'data' in that the values will be like following,

ID  NAME    DOB LOCATION
1   bob 08/10/1985  NEW JERSEY
1   bob 15/09/1987  NEW YORK
2   John    08/10/1985  NORTH CAROLINA
2   John    26/11/1990  OKLAHOMA

For example I want output like,

ID  NAME    No.of.Days                 
1   bob difference of two given dates in days   
2   John    difference of two given dates in days

Please help me to form a python code to get the expected output.

iamklaus · Accepted Answer · 2019-05-14 12:51:12Z

1

If there will be only two dates in a for a given ID then below works!

df.groupby(['ID','NAME'])['DOB'].apply(lambda x: abs(pd.to_datetime(list(x)[0]) - pd.to_datetime(list(x)[1]))).reset_index(name='No.Of.Days')

Output

   ID   NAME  No.Of.Days
0    1    bob   766 days
1    2   John  1934 days

you can use np.diff also

df.groupby(['ID','NAME'])['DOB'].apply(lambda x: np.diff(list(x))[0]).reset_index(name='No.Of.Days')

edited May 14, 2019 at 12:51

answered May 14, 2019 at 12:12

iamklaus

3,7682 gold badges14 silver badges21 bronze badges

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

3 Comments

sara Over a year ago

Thanks for your help.. but it didnt work,.. I got following error: Attribute error:'datetime.date' object has no attribute 'strip;..

iamklaus Over a year ago

apply the code on the posted df..and then please post the complete df since the code works only on case like above..for generic i need more data

sara Over a year ago

Shall I use the same logic without grouping id and name ..? If I just need a difference of two dates..

Rheatey Bash · Accepted Answer · 2019-05-14 13:06:16Z

1

First, You need to convert Date column into date format. Lets suppose you are reading from .csv then read your .csv file as follows

df = pd.read_csv('yourfile.csv', parse_dates = ['DOB'])

otherwise, convert your existing dataframe column into date format as follows.

df['DOB'] = pd.to_datetime(df['DOB'])

now, you can perform the usual numeric operations.

df.groupby(['ID','NAME'])['DOB'].apply(lambda x: abs(pd.to_datetime(list(x)[0]) - pd.to_datetime(list(x)[1]))).reset_index(name='No.Of.Days')

answered May 14, 2019 at 13:06

Rheatey Bash

8477 silver badges20 bronze badges

Collectives™ on Stack Overflow

How to merge two rows having same values into single row in python?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related