How to arrange misplaced data elements

Question

Temitope 2020-1-21

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/501243-how-to-arrange-misplaced-data-elements

编辑： Guillaume 2020-1-22

Hi all.

I have a data in excel format that has values under the wrong headers in different ways like;

Name Age Sex Weight Address Height

John M 20 House 2, collins street 120 55

This is not the data because i am not permitted to share it.

Is there a way the data can be arranged to put the values under the right headers using matlab?

Thank you for your help.

2 个评论
显示无隐藏无

Guillaume 2020-1-21

编辑：Guillaume 2020-1-22

Really, the best thing is to go back to whatever wrote these incorrect files and fix the issue at the source.

Trying to fix the issue in matlab may be possible but there's certainly no tool built-in to do that. You could possibly come up with some rules that would allow you to identify which entry should go where but you'd have to write these.

With your example, it should be easy to identify sex, it's either 'M' or 'F', you could possibly differentiate name and Address if you say that address contains numbers and text whereas name never contains a number. As for age, weight and height, it becomes a lot more ambiguous (particularly since we don't know the unit). Is 120 the height in cm, or the weight in kg or a very old person? Is 20 the weight in stone or the age? Is 55 a height of 5'5'' or the age or the weight in kg?

If you can come up with rules for each variable, we can help you write the code but I suspect there would always be some manual clean up required.

Temitope 2020-1-22

Hi Guillaume,

Thank you for your comment. The file is from a backup source because the original system crashed.

Let me see what i can do about the rules and get back.

Thanks.

请先登录，再进行评论。

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

Answer 1

Bhaskar R 2020-1-21

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/501243-how-to-arrange-misplaced-data-elements#answer_411200

编辑：Bhaskar R 2020-1-21

在 MATLAB Online 中打开

Read your excel data as

T = readtable('< your excel file>'); % your file read in table data

Reinitialte the header to get what you what(shuffle Weight and Address header ) as

T.Properties.VariableNames = {'Name', 'Age','Sex', 'Address', 'Weight', 'Height'} ;

Write back your modified table to file

writetable(T, '<your file name>');

2 个评论
显示无隐藏无

Temitope 2020-1-21

Thank you Bhaskar, but the issue is there are about 1000 rows but the order of the misplacement is not the same. The value under the "Age" header for instance could be under "Address" for one row but under "Sex" for another row.