Pandas merge data based on duplicate dates-CodePudding

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

CodePudding user response：

This should get you the desired results

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

CodePudding user response：

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