python - How can I merge rows from the same dataframe with a mix of null values

Question

This problem started when I used pivot_table on my initial table that contained:

col_1         col_2      col_3  col_4   col_5   year    index     value
Company X   category 1  A Ltd.  1234    CAD    2019       1     -0.788363
Company X   category 1  A Ltd.  1234    CAD    2018       2     -1.381539
Company X   category 1  A Ltd.  1234    CAD    2017       3     -1.073418
Company Y   category 1  B Ltd.  4321    USD    2019       4     -1.475593
Company Y   category 1  B Ltd.  4321    USD    2018       5     -2.839125
Company Y   category 1  B Ltd.  4321    USD    2017       6     -2.243862

I used this code to pivot the value into three years:

df= pd.pivot_table(df, index=['col_1', 'col_2', 'col_3', 'col_4',
   'col_5', 'index'], columns='year', values='value')
    .reset_index()
df.columns.name=None

So it resulted in this:

col_1        col_2     col_3    col_4   col_5   index   2017     2018      2019
Company X   category 1  A Ltd.  1234    CAD       1              -0.788363  
Company X   category 1  A Ltd.  1234    CAD       2   -1.381539     
Company X   category 1  A Ltd.  1234    CAD       3                       -1.073418
Company Y   category 1  B Ltd.  4321    USD       4                       -1.475593
Company Y   category 1  B Ltd.  4321    USD       5   -2.839125     
Company Y   category 1  B Ltd.  4321    USD       6               -2.243862 

How can I merge those value in the years so that they are in the same row? Or should I have pivoted differently? Thanks.

question from:https://stackoverflow.com/questions/65939082/how-can-i-merge-rows-from-the-same-dataframe-with-a-mix-of-null-values

Answer 1

Waitting for answers