1

I have a dataframe like so:

ID   |   Timestamp  |    x.val_1   |  x.val_2  |  y.val_1  |  y.val_2  | ...
1js  |      1pm     |       0      |     0     |     10    |     3     | ...
1js  |      2pm     |       3      |     4     |     11    |     1     | ...

I would like to transform it into this data frame:

ID   |   Timestamp  |    LABEL    | x   |  y   |
1js  |      1pm     |     val_1   | 0   |  10  |
1js  |      1pm     |     val_2   | 0   |  3   |
1js  |      2pm     |     val_1   | 3   |  11  |
1js  |      2pm     |     val_2   | 4   |  1   |

...

I know that I can stack pandas dataframes, but here the columns have to be split to be able to do that, and I am wondering if there is an elegant way of handling this.

I looked at this question: How to melt 2 columns at the same time?

However, that does not address my question. In the example above, the answer assumes limited number of known columns. In my case, I have a lot of columns, in format "label.value_type", and I can have multiple labels and multiple value types, so the only guarantee is that structure. I would like a systematic way of reshaping this data frame using "." split in the column, and the question above does not address it.

RebeccaK375
  • 871
  • 3
  • 17
  • 28
  • @WeNYoBen are you sure? OP specifically states that question is not helping im – Erfan Jun 04 '19 at 19:19
  • @WeNYoBen, StackOverflow told me to edit the question to explain how the question is different, so I edited to explain why my question is different. I have no problem re-posting this question, however. – RebeccaK375 Jun 04 '19 at 19:25
  • 1
    May I know why you think the link does not work ? `pd.wide_to_long(df,['x','y'],sep='.',i=['ID','Timestamp'],j='Label',suffix='\w+') ` – BENY Jun 04 '19 at 19:25
  • 1
    I did a quick modification to your code and it worked: set([x.split('.')[0] for x in df.columns if '.' in x]) <- put this instead of ['x', 'y']. If you post it, I will accept as an answer. (Or nvm if this question will remain closed), but thank you! – RebeccaK375 Jun 04 '19 at 19:33
  • @RebeccaK375 no worry ,glad can help, happy coding :-) – BENY Jun 04 '19 at 19:36

0 Answers0