Combine multiple columns into a single column keeping the column names?

9 views (last 30 days)
Dear all,
I have a matrix with dimensions 24*366. Each row corresponds to a time in the day (hour 1 to hour 24) and each column to a year's day (01-Jan-2020 to 31-Dec-2020). How do I convert the multiple columns to a single column? Also, I want to create a new column with the date that each cell corresponds to. I have seen in the forum that to transform multiple columns into one I can do:
A=[ 1 2 3 4
5 6 7 8
9 1 2 3]
out=A(:)
or
out=reshape(A,[],1)
However, the date ID column is essential, and I don't know how to get it. I have attached a picture of a simple example of what I want.
Thank you in advance!
  6 Comments
the cyclist
the cyclist on 30 Sep 2021
As will have been mentioned in a warning message, readtable appended the 'x' at the beginning to the column headers, so that they would valid names for MATLAB variables.
I think that probably the most intuitive way to deal with that is to choose the input argument prevents that from happening:
readtable(...,'VariableNamingRule','preserve')
and then convert using datetime:
dt = datetime('01_Jan_2020','InputFormat','dd_MMM_yyyy')
dt = datetime
01-Jan-2020
That being said, I'm not sure that this is the best start-to-finish solution to achieve the final result you want (doing the pivot, etc.). People might be able to think about this a bit more holistically, if you post your Excel file (or a small, representative sample) here to test solutions.
Angelavtc
Angelavtc on 1 Oct 2021
@the cyclist Thank you, it worked! I wrote the following code:
t = readtable('Spot2020.xlsx','PreserveVariableNames',true);
I am sorry for not showing the excel file before. I am attaching an small example. However, I have the solution. Thank you, again!

Sign in to comment.

Answers (0)

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!