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 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…