Pandas merge data based on duplicate dates

Question

I have a pandas dataframe that has empty input. I made a dummy dataframe below. Is there a way to merge on the date time and have the values be added to the empty space. There will always be empty space for the data to fit in. I have shown dummy result of what I am trying to achieve.

Any help or guidance would be highly appriciated!

Date time         LifeTime1   LifeTime2  LifeTime3 LifeTime4 LifeTime5
2020-02-11 17:30      6          7                                3
2020-02-11 17:30                              3       3            
2020-02-12 15:30      2          2                                3
2020-02-16 14:30      4                                           1
2020-02-16 14:30                 7                                
2020-02-16 14:30                              8       2

Results:

Date time         LifeTime1   LifeTime2  LifeTime3 LifeTime4 LifeTime5
2020-02-11 17:30      6          7            3       3           3
2020-02-12 15:30      2          2                                3
2020-02-16 14:30      4          7            8       2           1

ArchAngelPwn · Accepted Answer · 2022-08-25 20:24:16Z

1

This should get you the desired results

df.groupby(['Date time']).agg('first').reset_index()

answered Aug 25, 2022 at 20:24

ArchAngelPwn

3,0461 gold badge6 silver badges17 bronze badges

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

Comments

SomeDude · Accepted Answer · 2022-08-25 20:24:43Z

1

You may fill first empty spaces with na values and get groupby.first and then fillna with '' back:

df.replace(r'^\s*$', np.nan, regex=True).groupby('Date time').first().fillna('')

output:

                  LifeTime1  LifeTime2 LifeTime3 LifeTime4  LifeTime5
Date time                                                            
2020-02-11 17:30        6.0        7.0       3.0       3.0        3.0
2020-02-12 15:30        2.0        2.0                            3.0
2020-02-16 14:30        4.0        7.0       8.0       2.0        1.0

answered Aug 25, 2022 at 20:24

SomeDude

14.3k5 gold badges26 silver badges49 bronze badges

Collectives™ on Stack Overflow

Pandas merge data based on duplicate dates

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related