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

2 views (last 30 days)
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.

Answers (1)

Jonas
Jonas on 15 May 2021
Edited: Jonas on 15 May 2021
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 ;)

Community Treasure Hunt

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

Start Hunting!

Translated by