Combining Spreadsheets to Create a Table in Matlab
2 次查看(过去 30 天)
显示 更早的评论
Hello,
I have 4 spreadsheets, which have different data for zip codes. I want to create one spreadsheet for each zip code, where there the data from each spreadsheet is represented in the column for the zip code's own spreadsheet.
For instance, I have these spreadsheets, "WeeklyBreakZip.xlsx", "WeeklyManholeZip.xlsx", "WeeklyCatchZip.xlsx" and "WeeklyBackupZip.xlsx", "WeeklyStreetZip.xlsx" .
Each spreadsheet has zipcodes as the column headers, with the data listed. For example, one Zip Code is 10010.
For each Zip Code, I want to extract the columns from the four worksheets, and create its own worksheet.
So, for example, with zip code 10010, the columns of this new compiled worksheet would be:
Catch Back Break Manhole Street
Then, the data would be listed below.
How would I get Matlab to do this?Thank you. I very much appreciate any help.
2 个评论
采纳的回答
bharath pro
2020-6-29
I have written an approach which takes care of multiple excel files but requires that all of them have the same number of rows. I also assume that 'Weekly' and 'Zip.xlsx' appear in all the filenames.
Steps:
1) Put all the excel files into a folder
2) Explore the directory and get all excel files using fullfile
3) Store the data in the files to matlab using xlsread
4) Go through all the files
5) Go through all the column names in the files, add the first name in each column to a container ( the pin code ) and the remaining elements of the column are appended with the filename and stored as the value for the pin code in the container. If the pin code is already present in the container append the column to the already existing value for that pincode
6) Go through the container and write each value ( here a 2D matrix ) corresponding to the pincode in to a excel file whose filename is the pincode.
D = 'path_to_folder';
S = dir(fullfile(D,'*.xlsx'));
for k = 1:numel(S)
F = fullfile(D,S(k).name);
[S(k).num,S(k).txt,S(k).raw] = xlsread(F);
end
c = containers.Map;
for i=1:length(S)
k=S(i).name;
k=erase(k,'Zip.xlsx');
k=erase(k,'Weekly');
t1=S(i).num(1,:);
for i1=1:length(t1)
if i1==1
continue;
end
ty=S(i).num(:,i1);
ty=ty(2:end);
ty=ty.';
te=[k,num2cell(ty)];
te=te.';
size(te)
if isKey(c,num2str(t1(i1)))==0
c(num2str(t1(i1)))= te;
else
c(num2str(t1(i1)))=horzcat(c(num2str(t1(i1))),te);
end
end
end
o=c.keys;
v=c.values;
for i=1:length(o)
temp=char(strcat(o(i),'.xlsx'));
xlswrite(temp,v{i});
end
更多回答(1 个)
Cris LaPierre
2020-6-29
There are a couple things that make this problem challenging
- MATLAB does not like having variable names be numbers (the zip codes)
- Different amounts of data are recorded in your spreadsheets, meaning you need to have a plan for how to handle missing/extra zip codes between the data.
- You have an extra table in WeeklyStreetZip.xslx (two tables with 530 rows).
Forgive the approach here, but it's what I had to do to get something that worked. Feel free to modify. It does take a while to run.
BackupZip = readtable("WeeklyBackupZip.xlsx","Range",'1:1',"ReadRowNames",true,"ReadVariableNames",true,"PreserveVariableNames",true);
BackupData = readtable("WeeklyBackupZip.xlsx","NumHeaderLines",1,"ReadRowNames",true);
BackupData.Properties.VariableNames = BackupZip.Properties.VariableNames;
BreakZip = readtable("WeeklyBreakZip.xlsx","Range",'1:1',"ReadRowNames",true,"ReadVariableNames",true,"PreserveVariableNames",true);
BreakData = readtable("WeeklyBreakZip.xlsx","NumHeaderLines",1,"ReadRowNames",true);
BreakData.Properties.VariableNames = BreakZip.Properties.VariableNames;
CatchZip = readtable("WeeklyCatchZip.xlsx","Range",'1:1',"ReadRowNames",true,"ReadVariableNames",true,"PreserveVariableNames",true);
CatchData = readtable("WeeklyCatchZip.xlsx","NumHeaderLines",1,"ReadRowNames",true);
CatchData.Properties.VariableNames = CatchZip.Properties.VariableNames;
ManholeZip = readtable("WeeklyManholeZip.xlsx","Range",'1:1',"ReadRowNames",true,"ReadVariableNames",true,"PreserveVariableNames",true);
ManholeData = readtable("WeeklyManholeZip.xlsx","NumHeaderLines",1,"ReadRowNames",true);
ManholeData.Properties.VariableNames = ManholeZip.Properties.VariableNames;
StreetZip = readtable("WeeklyStreetZip.xlsx","Range",'1:1',"ReadRowNames",true,"ReadVariableNames",true,"PreserveVariableNames",true);
StreetData = readtable("WeeklyStreetZip.xlsx","NumHeaderLines",1,"ReadRowNames",true);
StreetData = StreetData(1:height(BackupData),:);
StreetData.Properties.RowNames = BackupData.Properties.RowNames;
StreetData.Properties.VariableNames = StreetZip.Properties.VariableNames;
zips = categorical([BackupZip{:,:},BreakZip{:,:},CatchZip{:,:},ManholeZip{:,:},StreetZip{:,:}]);
zips = categories(zips);
r = BackupData.Properties.RowNames;
for z = 1:length(zips)
try
Backup = BackupData{r,zips(z)};
catch
Backup = NaN([height(BackupData),1]);
end
try
Break = BreakData{r,zips(z)};
catch
Break = NaN([height(BreakData),1]);
end
try
Catch = CatchData{r,zips(z)};
catch
Catch = NaN([height(CatchData),1]);
end
try
Manhole = ManholeData{r,zips(z)};
catch
Manhole = NaN([height(ManholeData),1]);
end
try
Street = StreetData{r,zips(z)};
catch
Street = NaN([height(StreetData),1]);
end
zipTable = table(Backup,Break,Catch,Manhole,Street,'RowNames',r);
writetable(zipTable,"WeeklyZipData.xlsx","Sheet",string(BackupZip{1,z}),"WriteRowNames",true);
end
3 个评论
Cris LaPierre
2020-6-30
That is a newer setting, so if you are using an older versino of MATLAB, it is likely it's not available.
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Data Import from MATLAB 的更多信息
产品
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!