I have a dataframe with 28 column, 27 of which are datetime columns, 1 of which is an ID column. I need to convert the dataset into 3 total columns where there's one datatime column, a second column with the original header from which the datetime derived, and a third with the original ID from which the datetime derived.
Example of non-transformed data:
ID Buy Sell Hold
1 2/2/17 2/3/17 2/4/17
2 3/2/17 3/3/17 3/4/17
3 4/2/17 4/3/17 4/4/17
Example of transformed data:
Date Activity ID
2/2/17 Buy 1
2/3/17 Sell 1
2/4/17 Hold 1
3/2/17 Buy 2
3/3/17 Sell 2
3/4/17 Hold 2
4/2/17 Buy 3
4/3/17 Sell 3
4/4/17 Hold 3
My understanding is that this task can be achieved with pandas melt, however is it feasible with 27 datetime columns coupled with a single ID column?
How do I achieve this task with so many dimensions?
Use melt:
df_melt=df.melt(id_vars='ID',value_vars=['Buy','Sell','Hold'],var_name='Activity',value_name='Date')
You can also hyphen your 27 columns with a '_c' for example an then create a list similar to
list=[c for c in df if c.endwith('_c')] and reference the list in the melt function