How to convert timeseries class mat file to excel

Question

Arif Hoq 2021-11-25

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1595369-how-to-convert-timeseries-class-mat-file-to-excel

评论： Peter Perkins 2021-12-2

Hello.

Facing probelm with timeseries class. i want to convert the mat file into excel. I have used the below code,but it's showing only the mat file name.

a=load('simout1.mat');
whos -file simout1.mat
b=struct2table(a);
writetable(b,'justtry6.xls');

Result: simout1

the problem is table b has another double timeseries cell. its difficult to convert into excel file form simout1.mat file.

Would you please help to convert into excel ? i have uploaded the mat file(simout1.mat) in google drive as i can not upload here.

mat file link: https://drive.google.com/file/d/1D1dh_07ybfEG5wLvOABFpdvwr6SRurCt/view?usp=sharing

4 个评论
显示 2更早的评论隐藏 2更早的评论

KSSV 2021-11-25

To download the mat file......you need to give permissions. Change the permissions to anyone with link can download the file.

Arif Hoq 2021-11-25

pardon me. now i think you can download.@KSSV

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

dpb 2021-11-26

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1595369-how-to-convert-timeseries-class-mat-file-to-excel#answer_841124

编辑：dpb 2021-11-26

It's not bad at all...I let the file download overnight last night and got a few minutes to poke around just now...I was almost right yesterday. I renamed the timeseries to tsS locally for a shorter variable name...

>> ttS=array2timetable(tsS.Data(1:10,:),'RowTimes',seconds(tsS.Time(1:10)))
ttS =
  10×4 timetable
      Time         Var1        Var2    Var3       Var4   
    ________    ___________    ____    ____    __________
    0 sec       -6.0029e-06     0       0      6.4033e-06
    0.05 sec        -82.029     0       0          82.711
    0.1 sec         -81.195     0       0          81.864
    0.15 sec        -33.807     0       0           33.92
    0.2 sec           -34.5     0       0          34.618
    0.25 sec        -34.068     0       0          34.183
    0.3 sec         -33.664     0       0          33.777
    0.35 sec        -33.263     0       0          33.374
    0.4 sec         -32.865     0       0          32.974
    0.45 sec        -32.471     0       0          32.577
>> 

created the timetable ttS from the first 10 rows that match the image you posted.

>> ttS=array2timetable(tsS.Data,'RowTimes',seconds(tsS.Time));
>> whos ttS
  Name             Size                Bytes  Class        Attributes
  ttS       12096001x4             483841676  timetable              
>> 

built the whole timetable in a pretty short amount of time...only a second or two here.

Of course, now you have a problem in that the maximum row limit in Excel is 1,048,576. This applies to Excel from Excel 2007 thru Microsoft 365 . Earlier versions were only 16K rows.

It'll take

>> tsS.Length/1048576
ans =
   11.5356
>> 

sheets if you were to try to go the absolute limit.

All in all, you're probably better off just leaving it in MATLAB in the timetable; it is simpler to deal with there although there is about a 25% memory penalty.

>> whos ttS tsS
  Name             Size                Bytes  Class         Attributes
  tsS              1x1             387072368  timeseries              
  ttS       12096001x4             483841676  timetable               
>> 

3 个评论
显示 1更早的评论隐藏 1更早的评论

dpb 2021-11-26

编辑：dpb 2021-11-27

I don't know when the note was added, but I saw when perusing the documentation for timeseris object TMW has added the following disclaimer--(emphasis added--dpb)

Time Series Objects

Create, modify, and analyze timeseries objects containing time-dependent data

A timeseries object contains data and time information within its properties that describes a dynamic process. You can use timeseries object functions to create, modify, and analyze the behavior of a time series.

"Consider using timetables instead of timeseries objects, where you can store time-stamped data as column-oriented data variables. Additionally, you can use time-specific functions to align, combine, and perform calculations with one or more timetables."

...

From that, one can infer that while the timeseries class has not yet been officially deprecated, there's little likelihood of it going anywhere in the future and will eventually make the grade.

It's another similar to the ill-fated Statistics TB dataset that has reached the next plateau...

dataset class

(Not Recommended) Arrays for statistical data

The dataset data type is not recommended. To work with heterogeneous data, use the MATLAB® table data type instead. See MATLAB table documentation for more information.

One surely wishes TMW would be more careful in evaluating the long term before releasing these soon-to-be-abandoned features into the wild. Now we've got all the baggage/overhead to carry around forever for backwards compatibility. Like the non-indigenous Burmese python introduced into the Everglades, they're an invasive species.

dpb 2021-11-27

编辑：dpb 2021-11-27

One last note -- I intended to add above

>> tsInfo=get(tsS)
tsInfo = 
  struct with fields:
               Events: []
                 Name: ''
             UserData: []
                 Data: [12096001×4 double]
             DataInfo: [1×1 tsdata.datametadata]
                 Time: [12096001×1 double]
             TimeInfo: [1×1 tsdata.timemetadata]
              Quality: []
          QualityInfo: [1×1 tsdata.qualmetadata]
          IsTimeFirst: 1
    TreatNaNasMissing: 1
               Length: 12096001
>>

shows the original timeseries does not include any Events data; the only real reason one could fabricate for utilizing the timeseries object over either a table or the timetable as it is the only feature included in the timeseries without a direct corollary in timetable. Of course, they can be introduced as added variables; they're just not a builtin feature.

Something similar could be a reasonable enhancement request for timeseries, possibly...

Arif Hoq 2021-11-29

found the idea to extract the table data into excel as you mentioned above. thank you very much @dpb. it was a tough time i guess.

请先登录，再进行评论。

Answer 2

dpb 2021-11-25

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1595369-how-to-convert-timeseries-class-mat-file-to-excel#answer_840389

编辑：dpb 2021-11-25

You've managed to put a timeseries object into a table as a cell. To do anything with it, you've got to dereference it back to the inherent timeseries.

In other words, "Don't do that!"

Work with the timerseries as it is; the timeseries object is a real oddball thingie -- it is quite cumbersome and not well supported in continuing development in that there are no methods like read/writetable that work with it.

To convert it to a real timetable, try some variation on

ttS=timetable(seconds(simout1.Time),simout1.Data);

where you get the time information and the data from the timetable and put it into something more usable.

The events that may be with the timeseries will have to be dealt with in some other fashion -- I've never had a case of my own nor found one in all the responses to Q? here of the forum that I could not more easily solve with other tools than the timeseries, so I don't know all the ins and outs of it, but it always was limiting in some way for any problem I was ever faced with.

The file is too big to download practically here in available time...but I'm sure some variant of the above will get you where you need.

3 个评论
显示 1更早的评论隐藏 1更早的评论

dpb 2021-11-26

It is/will be impossible to convert the TS object directly into an Excel file unless you delve into the guts of the TS implementation and add customized functionality into the class. I've not poked around to see how much of the implementation is still in m files. Either way, that undoubtedly would turn into more work than the problem would justify.

What I showed above should work...if you want some further assistance on going further, save a small portion of the original TS as a .mat file and attach it -- we don't need a 100MB+ file to illustrate a concept.

dpb 2021-11-26

" add customized functionality into the class"

Which functionality will be, of course, some specific implementation of the process outlined above...

请先登录，再进行评论。

Answer 3

Peter Perkins 2021-11-28

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1595369-how-to-convert-timeseries-class-mat-file-to-excel#answer_842179

In MATLAB R2021b, you can use timeseries2timetable, and then write the timetable to excel.

This timeseries has nothing complicated in it, so in earlier versions you can do something like what dpb suggests, but I'd recommend this slight variation that uses the SampleRate parameter:

tt = array2timetable(ts.Data,'StartTime',seconds(0),'SampleRate',20)

4 个评论
显示 2更早的评论隐藏 2更早的评论

dpb 2021-11-28

编辑：dpb 2021-11-28

That's interesting development, Peter. It does help those who picked up on using the timeseries and have stuff in it already to move to the more highly recommended timetable.

One Q? re: your suggestion above -- parenthetically, I don't use the timetable enough to have thought of the option, but since the tsInfo structure has in it for time data the following--

>> tsInfo.TimeInfo
  tsdata.timemetadata
  Package: tsdata
  Uniform Time:
    Length       12096001
    Increment    5.000000e-02 seconds
  Time Range:
    Start        0 seconds
    End          604800 seconds
  Common Properties:
          Units: 'seconds'
         Format: ''
      StartDate: ''

I'd have been more inclined to have used

tt=array2timetable(ts.Data,'StartTime',seconds(0),'TimeStep',tsInfo.TimeInfo.Increment);

making direct use of the information in the table to be generic instead of picking sticking in the constant.

Obviously,

tt = array2timetable(ts.Data,'StartTime',seconds(0),'SampleRate',1/tsInfo.TimeInfo.Increment);

is the same using the TS data and in this case, the sample frequency is an integer but that isn't necessarily always true. So, (finally!) to the Q? --

Is there a reason to use 'SampleRate' instead similar to that in the difference between colon and linspace in generating long series with floating point steps--namely the difference in rounding?

I looked for and was hoping there would have been a more direct path to have picked up the units for the time variable from the TS object without doing string comparisons in order to know which of the duration class functions to choose rather than hardcoding seconds as well, but didn't want to take the time to add that at the time.

I still do wish we only had one or the other just as with the dataset -- keeping all these other less-than-full-fledged members adds to bulk and confusion and seems counterproductive to both TMW and the user base.

Maybe it would take having had the Crystal Ball TB already implemented, I don't know...but to the outsider it seems as though things get rushed to release more frequently these days, sometimes before they're quite ready.

While the Statistics TB was sorely hampered without a heterogeneous collection and I understand therefore the development inside it of the dataset (which I gleefully used when it first arrived, as well), it seems given how soon the full-fledged table followed that it had to have been in development and the Statistics TB could have suffered through another release or two instead. Just observations that as user I'm more nonplussed by things coming and then being replaced or duplicated than I am in continuing to do without something don't know is coming anyway until "the real deal" is ready.

Peter Perkins 2021-11-28

SampleRate and TimeStep are, literally, flip sides of each other. I used SampleRate only because I could see it was an integer. I think signals people tend to think in terms of sample rate, while financial people think in terms of time steps, which are often calendar units for them. To some extent timetable is forgiving, it doesn't iteratively add up the time step and accumulate round-off, but still, best to pick the one for which there's an exact value. 44.1kHz, not 0.00002267573... sec, right?

timeseries2timetable doesd actually look at the TimeInfo.Units property:

>> ts = timeseries(1:5); ts.TimeInfo.Units = 'minutes';
>> tt = timeseries2timetable(ts)
tt =
  5×1 timetable
    Time     Data
    _____    ____
    0 min     1  
    1 min     2  
    2 min     3  
    3 min     4  
    4 min     5  

I think that's what you were asking about. TimeInfo.Units can be anything, so that conversion can throw an error.

We definitely hear what you are saying about replacing existing tools, and you can't imagine how hard we try to predict the future and cast a wide net and so on when we design something, and to preserve compatibility when we redesign or replace something. You are right, though, we don't have a crystal ball. timeseries2timetable is intended to ease the transition.

timeseries has been around since circa 2007, and I think what we learned was that it needed more interactivity. It was not really designed for the kind of data cleaning, reorganization, and exploration that timetable (like table and dataset before it) was aimed at and is good at.

dataset was originally aimed at statistics uses, but there were many requests to move it into core MATLAB. We felt like there were reasons to make some changes to dataset so that table was more approachable to non-statistical uses. For about 90% of uses, table is a drop-in replacement, though.

dpb 2021-11-29

..."To some extent timetable is forgiving, it doesn't iteratively add up the time step and accumulate round-off, but still, best to pick the one for which there's an exact value. 44.1kHz, not 0.00002267573... sec, right?"

That answers the Q? I was asking, yes, Peter, thanks.

I agree as for using wthich is the round/integer/exact value, but for a data set picked up at random without prior knowledge, unless one does tests for which is the round value, there's no way to know which might be that one. It's possible it's more likely to be frequency and as you say if the application is engineering or acoustics or the like, possibly quite highly probable, but not a foregone conclusion, so I took the value that was actually given in the data and used it.

Does timeseries2timetable make a test of that sort or just always use frequency on the assumption?

I did not recall (and obviously didn't go look first) that timeseries actually went back quite that far...I just never found a use for it "in anger" so never got on my radar.

Thanks for the feedback, Peter, it's always useful to know something of the development mindset -- I hope I don't come off as incessantly carping for no purpose, but remain focused on product improvement and future considerations... :)

Peter Perkins 2021-12-2