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

1 view (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 ;)

Categories

Find more on Environment and Settings in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!