0

I have a df that looks like this:

     asset  valid_from    valid_to    history_table        value latitude
0  Asset_1  01/09/2020  31/12/2049        market bm  2__MSTAT001    53,80
1  Asset_1  01/09/2020  31/12/2049  energy_capacity           10    53,80
2  Asset_2  01/05/2022  01/01/2023        market bm  V__JZENO001    51,30
3  Asset_2  02/01/2023  31/12/2049        market bm   V__JZEN002    51,30
4  Asset_3  01/04/2018  31/12/2049            owner          ESB    52,97

It is a mix of a long and wide format.

The long id and value columns are 'history_table' & 'value' respectively.

I want to unstack these columns, so for each asset

The problem I have when using the code

test = df.pivot(index=['asset', 'valid_from', 'valid_to'], columns='history_table', values='value')

is that the index contains multiple duplicate entries. I've tried using pivot_table, but means aggregating the results and my 'value' column contains both text and numeric values.

Example DF

{'asset': ['Asset_1', 'Asset_1', 'Asset_2', 'Asset_2', 'Asset_3'],
 'valid_from': ['01/09/2020', '01/09/2020', '01/05/2022', '02/01/2023', '01/04/2018'],
 'valid_to': ['31/12/2049', '31/12/2049', '01/01/2023', '31/12/2049', '31/12/2049'],
 'history_table': ['market bm', 'energy_capacity', 'market bm', 'market bm', 'owner'],
 'value': ['2__MSTAT001', '10', 'V__JZENO001', 'V__JZEN002', 'ESB'],
 'latitude': ['53,80', '53,80', '51,30', '51,30', '52,97']}
wjandrea
  • 28,235
  • 9
  • 60
  • 81
jolene
  • 373
  • 2
  • 15
  • 2
    Can you please [edit] your question and put there expected output? – Andrej Kesely Jul 17 '23 at 16:56
  • 1
    What do you mean about duplicate entries? If you're getting `ValueError: Index contains duplicate entries, cannot reshape`, this code here doesn't raise that error. Please make a [mre]. For specifics, see [How to make good reproducible pandas examples](/q/20109391/4518341). On the other hand, maybe you're talking about how `Asset_2` is repeated? BTW, check out [ask] for tips like how to write a good title. – wjandrea Jul 17 '23 at 17:05

0 Answers0