How to exclude specific rows form multiple separate tables in the workspace and than merge them?

Hello everyone,

Unfortunatly I am again struggling with a MatLab task. I have 15 sheets in an excel spreadsheet each containing 3 different measures of social performance (Columns) for 400 companies (rows) for 15 years respectively (each sheet is a specific year). I have importet those sheets into MatLab and I have now got 15 seperate tables in my workspace. In a first stop I would like to delete rows that contain only NaN, so I would like to check for each year what are the companies that do no provide data for the respective. I need to perform this command then for each of the 15 tables in my workspace. Once this is completed I would like to merge the 15 tables and create a table that should look like this:

Year Companies Social Score 1 Social Score 2 Social Score 3

1999 (List of the companies that provide data for this year)

...

...

2000

The aim is that in the I can rank the companies according to the respective Social score for each year.

I know this a quite extensive question but as I am a beginner I would be grateful for your help.

Thank you very much in advance.

Answer 1

[Jonas]

assuming you have 15 tables, each is a 400x3 table with company names as row names and score type as column names you can remove rows with NaN only by using

tbl(all(isnan(tbl{:,:}),2),:)=[];

you could the just put the 15 tables in a table again and use the year as row names. you will then have a 15x1 table in which each entry will be a table again

alternatively you add to each year table a column with the year as entry (which eill be the same for all entries per table) and then just merge the 15 tables to a about (400x15)x4 big table. the first x means a multiplication here ;)