From nested structure to table in one line of code

52 views (last 30 days)
Giovanni Barbarossa
Giovanni Barbarossa on 30 Mar 2020
Edited: dpb on 31 Mar 2020
I have a structure S with n fields S_field_1, S_field_2,…S_field_n. Each field contains a structure with the same number m and name of fields field_1, field_2…field_m.
How do I get a nxm table with variables field_1, field_2…field_m with a single line of code?
Thank you!
  3 Comments
Show 1 older comment
Giovanni Barbarossa
Giovanni Barbarossa on 30 Mar 2020
The table resulting from the attached example should have variables 'date', 'filing_date', 'currency_symbol', etc.
Since the field names of the structure S are basically dates, it would be great if I could actually get a timetable, understanding it would require some manipulation of the field strings 'x2020_02_28', 'x2019_11_29',...etc.
Thank you

Sign in to comment.

Sign in to answer this question.

Accepted Answer

dpb
dpb on 31 Mar 2020
Edited: dpb on 31 Mar 2020
Will need some additional cleanup, but the basic tranformation is as
>> T=struct2table(struct2array(S));
>> T(1:10,:)
ans =
10×27 table
date filing_date currency_symbol researchDevelopment effectOfAccountingCharges incomeBeforeTax minorityInterest netIncome sellingGeneralAdministrative grossProfit ebit nonOperatingIncomeNetOther operatingIncome otherOperatingExpenses interestExpense extraordinaryItems nonRecurring otherItems incomeTaxExpense totalRevenue totalOperatingExpenses costOfRevenue totalOtherIncomeExpenseNet discontinuedOperations netIncomeFromContinuingOps netIncomeApplicableToCommonShares preferredStockAndOtherAdjustments
______________ ______________ _______________ ___________________ _________________________ ________________ ________________ ________________ ____________________________ _________________ ________________ __________________________ ________________ ______________________ ________________ __________________ ____________ ____________ ________________ _________________ ______________________ ________________ __________________________ ______________________ __________________________ _________________________________ _________________________________
{'2020-02-28'} {0×0 double } {'USD'} {'532000000.00'} {0×0 double} {'919000000.00'} {0×0 double} {'955000000.00'} {'1128000000.00'} {'2639000000.00'} {'937000000.00'} {0×0 double } {'937000000.00'} {0×0 double } {'-33000000.00'} {0×0 double} {0×0 double} {0×0 double} {'-36000000.00'} {'3091000000.00'} {'2154000000.00'} {'452000000.00'} {'-18000000.00'} {0×0 double} {'955000000.00'} {'955000000.00'} {0×0 double}
{'2019-11-29'} {0×0 double } {'USD'} {'499806000.00'} {0×0 double} {'957147000.00'} {0×0 double} {'851861000.00'} {'1027526000.00'} {'2539962000.00'} {'969932000.00'} {0×0 double } {'969932000.00'} {0×0 double } {'-36515000.00'} {0×0 double} {0×0 double} {0×0 double} {'105286000.00'} {'2991945000.00'} {'2022013000.00'} {'451983000.00'} {'-12785000.00'} {0×0 double} {'851861000.00'} {'851861000.00'} {0×0 double}
{'2019-08-30'} {0×0 double } {'USD'} {'489827000.00'} {0×0 double} {'834488000.00'} {0×0 double} {'792763000.00'} {'1031570000.00'} {'2418163000.00'} {'853812000.00'} {0×0 double } {'853812000.00'} {0×0 double } {'-39529000.00'} {0×0 double} {0×0 double} {0×0 double} {'41725000.00' } {'2834126000.00'} {'1980314000.00'} {'415963000.00'} {'-19324000.00'} {0×0 double} {'792763000.00'} {'792763000.00'} {0×0 double}
{'2019-05-31'} {'2019-06-26'} {'USD'} {'475958000.00'} {0×0 double} {'710772000.00'} {0×0 double} {'632593000.00'} {'1068261000.00'} {'2336792000.00'} {'749547000.00'} {'1802000.00' } {'749547000.00'} {'43026000.00'} {'-40577000.00'} {0×0 double} {0×0 double} {0×0 double} {'78179000.00' } {'2744280000.00'} {'1994733000.00'} {'407488000.00'} {'-38775000.00'} {0×0 double} {'632593000.00'} {'632593000.00'} {0×0 double}
{'2019-03-01'} {'2019-03-27'} {'USD'} {'464637000.00'} {0×0 double} {'702334000.00'} {0×0 double} {'674241000.00'} {'997627000.00' } {'2203660000.00'} {'694830000.00'} {'48097000.00'} {'694830000.00'} {'46566000.00'} {'-40593000.00'} {0×0 double} {0×0 double} {0×0 double} {'28093000.00' } {'2600946000.00'} {'1906116000.00'} {'397286000.00'} {'7504000.00' } {0×0 double} {'674241000.00'} {'674241000.00'} {0×0 double}
{'2018-11-30'} {'2019-01-25'} {'USD'} {'415958000.00'} {'0.00' } {'699217000.00'} {'0.00' } {'678240000.00'} {'935928000.00' } {'2105364000.00'} {'720546000.00'} {'6544000.00' } {'720546000.00'} {'32932000.00'} {'-27873000.00'} {'0.00' } {'0.00' } {'0.00' } {'20977000.00' } {'2464625000.00'} {'1744079000.00'} {'359261000.00'} {'-21329000.00'} {'0.00' } {'678240000.00'} {'678240000.00'} {0×0 double}
{'2018-08-31'} {'2018-09-26'} {'USD'} {'398957000.00'} {'0.00' } {'701358000.00'} {'0.00' } {'666291000.00'} {'854147000.00' } {'1995584000.00'} {'718606000.00'} {'3859000.00' } {'718606000.00'} {'23874000.00'} {'-21107000.00'} {'0.00' } {'0.00' } {'0.00' } {'35067000.00' } {'2291076000.00'} {'1276978000.00'} {'295492000.00'} {'-17248000.00'} {'0.00' } {'666291000.00'} {'666291000.00'} {0×0 double}
{'2018-06-01'} {'2018-06-27'} {'USD'} {'374128000.00'} {'0.00' } {'690799000.00'} {'0.00' } {'663167000.00'} {'824255000.00' } {'1914016000.00'} {'698484000.00'} {'12678000.00'} {'698484000.00'} {'17149000.00'} {'20363000.00' } {'0.00' } {'0.00' } {'0.00' } {'27632000.00' } {'2195360000.00'} {'1215532000.00'} {'281344000.00'} {'-7685000.00' } {'0.00' } {'663167000.00'} {'663167000.00'} {0×0 double}
{'2018-03-02'} {'2018-03-28'} {'USD'} {'348769000.00'} {'0.00' } {'702502000.00'} {'0.00' } {'583076000.00'} {'751397000.00' } {'1820045000.00'} {'702733000.00'} {'19668000.00'} {'702733000.00'} {'17146000.00'} {'19899000.00' } {'0.00' } {'0.00' } {'0.00' } {'119426000.00'} {'2078947000.00'} {'1117312000.00'} {'258902000.00'} {'-231000.00' } {'0.00' } {'583076000.00'} {'583076000.00'} {0×0 double}
{'2017-12-01'} {'2018-01-22'} {'USD'} {'324026000.00'} {'0.00' } {'643012000.00'} {'0.00' } {'501549000.00'} {'743671000.00' } {'1735723000.00'} {'662128000.00'} {'12788000.00'} {'649340000.00'} {'18686000.00'} {'19116000.00' } {'0.00' } {'0.00' } {'0.00' } {'141463000.00'} {'2006595000.00'} {'1086383000.00'} {'270872000.00'} {'-26159000.00'} {'0.00' } {'501549000.00'} {'501549000.00'} {0×0 double}
>>
To get to timetable must convert...did a few, rest as "exercise for Student"...
T.date=datetime(T.date); % convert to datetime so can convert to timetable
T.currency_symbol=categorical(T.currency_symbol); % better format for such a variable
T.researchDevelopment=str2double(T.researchDevelopment); % ditto for numeric
... % finish cleanup here...
which results in
>> format bank
>> TT=table2timetable(T);
>> TT(1:10,:)
ans =
10×26 timetable
date filing_date currency_symbol researchDevelopment effectOfAccountingCharges incomeBeforeTax minorityInterest netIncome sellingGeneralAdministrative grossProfit ebit nonOperatingIncomeNetOther operatingIncome otherOperatingExpenses interestExpense extraordinaryItems nonRecurring otherItems incomeTaxExpense totalRevenue totalOperatingExpenses costOfRevenue totalOtherIncomeExpenseNet discontinuedOperations netIncomeFromContinuingOps netIncomeApplicableToCommonShares preferredStockAndOtherAdjustments
___________ ______________ _______________ ___________________ _________________________ ________________ ________________ ________________ ____________________________ _________________ ________________ __________________________ ________________ ______________________ ________________ __________________ ____________ ____________ ________________ _________________ ______________________ ________________ __________________________ ______________________ __________________________ _________________________________ _________________________________
28-Feb-2020 {0×0 double } USD 532000000.00 {0×0 double} {'919000000.00'} {0×0 double} {'955000000.00'} {'1128000000.00'} {'2639000000.00'} {'937000000.00'} {0×0 double } {'937000000.00'} {0×0 double } {'-33000000.00'} {0×0 double} {0×0 double} {0×0 double} {'-36000000.00'} {'3091000000.00'} {'2154000000.00'} {'452000000.00'} {'-18000000.00'} {0×0 double} {'955000000.00'} {'955000000.00'} {0×0 double}
29-Nov-2019 {0×0 double } USD 499806000.00 {0×0 double} {'957147000.00'} {0×0 double} {'851861000.00'} {'1027526000.00'} {'2539962000.00'} {'969932000.00'} {0×0 double } {'969932000.00'} {0×0 double } {'-36515000.00'} {0×0 double} {0×0 double} {0×0 double} {'105286000.00'} {'2991945000.00'} {'2022013000.00'} {'451983000.00'} {'-12785000.00'} {0×0 double} {'851861000.00'} {'851861000.00'} {0×0 double}
30-Aug-2019 {0×0 double } USD 489827000.00 {0×0 double} {'834488000.00'} {0×0 double} {'792763000.00'} {'1031570000.00'} {'2418163000.00'} {'853812000.00'} {0×0 double } {'853812000.00'} {0×0 double } {'-39529000.00'} {0×0 double} {0×0 double} {0×0 double} {'41725000.00' } {'2834126000.00'} {'1980314000.00'} {'415963000.00'} {'-19324000.00'} {0×0 double} {'792763000.00'} {'792763000.00'} {0×0 double}
31-May-2019 {'2019-06-26'} USD 475958000.00 {0×0 double} {'710772000.00'} {0×0 double} {'632593000.00'} {'1068261000.00'} {'2336792000.00'} {'749547000.00'} {'1802000.00' } {'749547000.00'} {'43026000.00'} {'-40577000.00'} {0×0 double} {0×0 double} {0×0 double} {'78179000.00' } {'2744280000.00'} {'1994733000.00'} {'407488000.00'} {'-38775000.00'} {0×0 double} {'632593000.00'} {'632593000.00'} {0×0 double}
01-Mar-2019 {'2019-03-27'} USD 464637000.00 {0×0 double} {'702334000.00'} {0×0 double} {'674241000.00'} {'997627000.00' } {'2203660000.00'} {'694830000.00'} {'48097000.00'} {'694830000.00'} {'46566000.00'} {'-40593000.00'} {0×0 double} {0×0 double} {0×0 double} {'28093000.00' } {'2600946000.00'} {'1906116000.00'} {'397286000.00'} {'7504000.00' } {0×0 double} {'674241000.00'} {'674241000.00'} {0×0 double}
30-Nov-2018 {'2019-01-25'} USD 415958000.00 {'0.00' } {'699217000.00'} {'0.00' } {'678240000.00'} {'935928000.00' } {'2105364000.00'} {'720546000.00'} {'6544000.00' } {'720546000.00'} {'32932000.00'} {'-27873000.00'} {'0.00' } {'0.00' } {'0.00' } {'20977000.00' } {'2464625000.00'} {'1744079000.00'} {'359261000.00'} {'-21329000.00'} {'0.00' } {'678240000.00'} {'678240000.00'} {0×0 double}
31-Aug-2018 {'2018-09-26'} USD 398957000.00 {'0.00' } {'701358000.00'} {'0.00' } {'666291000.00'} {'854147000.00' } {'1995584000.00'} {'718606000.00'} {'3859000.00' } {'718606000.00'} {'23874000.00'} {'-21107000.00'} {'0.00' } {'0.00' } {'0.00' } {'35067000.00' } {'2291076000.00'} {'1276978000.00'} {'295492000.00'} {'-17248000.00'} {'0.00' } {'666291000.00'} {'666291000.00'} {0×0 double}
01-Jun-2018 {'2018-06-27'} USD 374128000.00 {'0.00' } {'690799000.00'} {'0.00' } {'663167000.00'} {'824255000.00' } {'1914016000.00'} {'698484000.00'} {'12678000.00'} {'698484000.00'} {'17149000.00'} {'20363000.00' } {'0.00' } {'0.00' } {'0.00' } {'27632000.00' } {'2195360000.00'} {'1215532000.00'} {'281344000.00'} {'-7685000.00' } {'0.00' } {'663167000.00'} {'663167000.00'} {0×0 double}
02-Mar-2018 {'2018-03-28'} USD 348769000.00 {'0.00' } {'702502000.00'} {'0.00' } {'583076000.00'} {'751397000.00' } {'1820045000.00'} {'702733000.00'} {'19668000.00'} {'702733000.00'} {'17146000.00'} {'19899000.00' } {'0.00' } {'0.00' } {'0.00' } {'119426000.00'} {'2078947000.00'} {'1117312000.00'} {'258902000.00'} {'-231000.00' } {'0.00' } {'583076000.00'} {'583076000.00'} {0×0 double}
01-Dec-2017 {'2018-01-22'} USD 324026000.00 {'0.00' } {'643012000.00'} {'0.00' } {'501549000.00'} {'743671000.00' } {'1735723000.00'} {'662128000.00'} {'12788000.00'} {'649340000.00'} {'18686000.00'} {'19116000.00' } {'0.00' } {'0.00' } {'0.00' } {'141463000.00'} {'2006595000.00'} {'1086383000.00'} {'270872000.00'} {'-26159000.00'} {'0.00' } {'501549000.00'} {'501549000.00'} {0×0 double}
>>
NB: will have to do logical indexing to fix up the non-string entries for filing_date as datetime can't convert the empty double entries.
There's no way can be done in just one line but isn't too bad once recognize should be a struct array instead of linear struct with metadata stored as field names.
Would also be simpler later if didn't have the null entry in the filing_date field when create the original data struct. An empty string instead would be converted to NaT would be the logical choice.
  2 Comments
Giovanni Barbarossa
Giovanni Barbarossa on 31 Mar 2020
Thank you, but struct2array is not available on Matlab R2020a. Is it an old function? Maybe from File Exchange?
dpb
dpb on 31 Mar 2020
That's bizarre...but it apparently was just some helper file
>> which struct2array
C:\ML_R2019b\toolbox\shared\measure\struct2array.m
Use
T=struct2cell(S);T=struct2table([T{:}]);
then. Requires another step--altho that's exactly what struct2array is w/o the input argument check.

Sign in to comment.

More Answers (0)

Categories

Find more on Structures in Help Center and File Exchange

Tags

Community Treasure Hunt

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

Start Hunting!