Bar chart from Excel with hidden columns

Question

Florian 2024-8-7，17:09

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2143769-bar-chart-from-excel-with-hidden-columns

移动：Stephen23 2024-8-9，5:07

Hi there

I want to create the attached chart (plus an average line per section) with Matlab. I can do it super quick with Excel, but unfortunately it didn´t work well with Matlab.

There are three complications for me. 1 - How can I can exclude the hidden columns? 2 - What is the best way to deal with German numbers with comma instead of point? 3 -And Is there any way to have an average line per section?

Seems the "readtable" doesn´t work well with commas.

Thanks

7 个评论
显示 5更早的评论隐藏 5更早的评论

Florian 2024-8-8，15:38

thanks

Stephen23 2024-8-8，17:18

移动：Stephen23 2024-8-9，5:07

在 MATLAB Online 中打开

Auswertung - Kopie.xlsx

"I raise it to Q once I know the error isn´t me haha"

In fact the data in column C is actually text. You can check this yourself using the TYPE function (in german TYP):

https://support.microsoft.com/en-us/office/type-function-45b4e688-4bc3-48b3-a105-ffa892995899

You can also check the type by viewing the Open Office XML file content. The Open Office standard specifies that the t="s" attribute&value defines that cell content as being text. Lets take a look at cell C2:

Yep, C2 is definitely marked as being text content. As are all other values in column C.

(As an aside, the value 23 refers to the sharedString where that text content is actually defined)

Here is a third approach to check the content of column C. Lets import the data using READCELL:

C = readcell('Auswertung - Kopie.xlsx')
C = 11x13 cell array
  Columns 1 through 9

    {'Cell'        }    {'Length'   }    {'Diameter' }    {'Can'      }    {'Anode total'}    {'Current Collec...'}    {'Coating Anode'}    {'Separator'}    {'Cathode total'}
    {'Blau'        }    {[       65]}    {'26'       }    {[   0.8000]}    {[     0.1100]}    {[           0.0700]}    {[       0.0200]}    {[   0.0200]}    {[       0.1100]}
    {'Grau'        }    {[       65]}    {'26'       }    {[   0.8000]}    {[     0.1100]}    {[           0.0700]}    {[       0.0200]}    {[   0.0200]}    {[       0.1100]}
    {'Orange'      }    {[       65]}    {'18'       }    {[   0.8000]}    {[     0.1100]}    {[           0.0700]}    {[       0.0200]}    {[   0.0200]}    {[       0.1100]}
    {'Rot'         }    {[       65]}    {'26'       }    {[   0.8000]}    {[     0.1100]}    {[           0.0700]}    {[       0.0200]}    {[   0.0200]}    {[       0.1100]}
    {'Grün'        }    {[       65]}    {'26'       }    {[   0.8000]}    {[     0.1100]}    {[           0.0700]}    {[       0.0200]}    {[   0.0200]}    {[       0.1100]}
    {'Literature 1'}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>  ]}    {[<missing>        ]}    {[<missing>    ]}    {[<missing>]}    {[<missing>    ]}
    {'Literature 2'}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>  ]}    {[<missing>        ]}    {[<missing>    ]}    {[<missing>]}    {[<missing>    ]}
    {'Literature 3'}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>  ]}    {[<missing>        ]}    {[<missing>    ]}    {[<missing>]}    {[<missing>    ]}
    {'Literature 4'}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>  ]}    {[<missing>        ]}    {[<missing>    ]}    {[<missing>]}    {[<missing>    ]}
    {'Literature 5'}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>  ]}    {[<missing>        ]}    {[<missing>    ]}    {[<missing>]}    {[<missing>    ]}

  Columns 10 through 13

    {'Current Collec...'}    {'Coating Cathode'}    {'Tab Copper'}    {'Tab Aluminium'}
    {[           0.0500]}    {[         0.0400]}    {[    0.2000]}    {[       0.1000]}
    {[           0.0500]}    {[         0.0400]}    {[    0.2000]}    {[       0.1000]}
    {[           0.0500]}    {[         0.0400]}    {[    0.2000]}    {[       0.1000]}
    {[           0.0500]}    {[         0.0400]}    {[    0.2000]}    {[       0.1000]}
    {[           0.0500]}    {[         0.0400]}    {[    0.2000]}    {[       0.1000]}
    {[<missing>        ]}    {[<missing>      ]}    {[<missing> ]}    {[<missing>    ]}
    {[<missing>        ]}    {[<missing>      ]}    {[<missing> ]}    {[<missing>    ]}
    {[<missing>        ]}    {[<missing>      ]}    {[<missing> ]}    {[<missing>    ]}
    {[<missing>        ]}    {[<missing>      ]}    {[<missing> ]}    {[<missing>    ]}
    {[<missing>        ]}    {[<missing>      ]}    {[<missing> ]}    {[<missing>    ]}

So I used three different independent methods to confirm the type of the data in column C. All of them told me that the content of column C is text. Which means that READTABLE is correctly importing text as text. Which is the expected, documented, desired behavior of READTABLE. Which means that the problem lies with the file data, not with READTABLE.

The best place to fix this import is to fix the incorrectly defined file data. First change the cell format to "general" and then use the approach given under "Convert a column of text to numbers" here:

https://support.microsoft.com/en-us/office/convert-numbers-stored-as-text-to-numbers-40105f2a-fe79-4477-a171-c5bad0f0a885

请先登录，再进行评论。

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

Answer 1

Voss 2024-8-8，17:37

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2143769-bar-chart-from-excel-with-hidden-columns#answer_1496744

编辑：Voss 2024-8-8，22:18

在 MATLAB Online 中打开

Auswertung - Kopie.xlsx

filename = 'Auswertung - Kopie.xlsx';

opts = detectImportOptions(filename);

opts = setvartype(opts,2:numel(opts.VariableTypes),'double');

opts.VariableNamingRule = 'preserve';

T = readtable(filename,opts);

cats = T{:,1};

% data = T{:,2:end};

data = T{:,[4 6:8 10:end]};

idx = all(isnan(data),2);

cats(idx) = [];

data(idx,:) = [];

hb = bar(data.','FaceColor','flat');

colors = [0 0.4 0.7; 0.5 0.5 0.5; 1 0.5 0; 0.6 0 0; 0 0.6 0];

set(hb,{'CData'},num2cell(colors,2));

mean_data = mean(data,1,'omitnan');

N = size(data,2);

yd = [mean_data([1 1],:); NaN(1,N)];

xd = 0.5*[-1; 1; NaN]+(1:N);

hl = line(xd(:),yd(:),'Color','k','LineStyle',':','LineWidth',2);

legend([hb hl],[cats; {'Averages'}],'Location','best')

set(gca(), ...

'YScale','log', ...

'XLim',[0 N+1], ...

'XTick',1:N, ...

...'XTickLabels',T.Properties.VariableNames(2:end))

'XTickLabels',T.Properties.VariableNames([4 6:8 10:end]))

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

Answer 2

dpb 2024-8-8，17:37

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2143769-bar-chart-from-excel-with-hidden-columns#answer_1496739

编辑：dpb 2024-8-8，18:48

在 MATLAB Online 中打开

warning('off','MATLAB:table:ModifiedAndSavedVarNames'); % turn off annoying nag message

tA=readtable('Auswertung - Kopie.xlsx','MissingRule','omitrow'); % read, don't bring missing rows

tA.Cell=categorical(tA.Cell); % convert types

tA.Diameter=str2double(tA.Diameter); % ditto, w/o the opt object

head(tA) % look at what's we gots...

Cell Length Diameter Can AnodeTotal CurrentCollectorAnode CoatingAnode Separator CathodeTotal CurrentCollectorCathode CoatingCathode TabCopper TabAluminium ______ ______ ________ ___ __________ _____________________ ____________ _________ ____________ _______________________ ______________ _________ ____________ Blau 65 26 0.8 0.11 0.07 0.02 0.02 0.11 0.05 0.04 0.2 0.1 Grau 65 26 0.8 0.11 0.07 0.02 0.02 0.11 0.05 0.04 0.2 0.1 Orange 65 18 0.8 0.11 0.07 0.02 0.02 0.11 0.05 0.04 0.2 0.1 Rot 65 26 0.8 0.11 0.07 0.02 0.02 0.11 0.05 0.04 0.2 0.1 Grün 65 26 0.8 0.11 0.07 0.02 0.02 0.11 0.05 0.04 0.2 0.1

ixBar=[4 6 8:width(tA)]; % variables wanted in bar()

x=(tA.Properties.VariableNames(ixBar)); % create an x vector for labelling

hB=bar(x,tA{:,ixBar}.','Grouped'); % pull the columns; transpose for grouping

hL=legend(tA.Cell,'location','northeast'); % label 'em...

ylim([0 1])

Not terribly interesting given all the data are the same by color (whatever that represents), but the idea should be clear.

Left as "exercise for Student" to add a row to the table that is the mean for each... :)

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

Answer 3

dpb 2024-8-7，18:13

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2143769-bar-chart-from-excel-with-hidden-columns#answer_1496074

编辑：dpb 2024-8-7，19:30

在 MATLAB Online 中打开

opt=detectImportOptions('yourfile.xlsx');                           % base import options object
opt.SelectedVariableNames=opt.SelectedVariableNames([1 3:end]);     % don't read the second column
opt=setvaropts(opt,'DecimalSeparator',',');                         % set comma as decimal
tT=readtable('yourfile.xlsx',opt);

MATLAB may need a little additional help given it doesn't have all of Excel built in...thank goodnes!!! :)

Above would be a start; you may want to add some additional details and it's certainly easy enough to then compute means or whatever statistics desired...the extra lines may be something else to deal with; not sure what, if anything, might be behind the graphic; if there are just extra labels in the first column for "in case", then the 'MissingRule' may be useful to not read those in...

See detectImportOptions and friends for all the details, examples, etc., ...

7 个评论
显示 5更早的评论隐藏 5更早的评论

dpb 2024-8-8，16:10

编辑：dpb 2024-8-8，16:16

在 MATLAB Online 中打开

Auswertung.m

"assuming I need a loop for creating the groups?"

Not likely, no...

type Auswertung.m
% Daten aus der Excel-Datei einlesen
data = readtable("Auswertung.xlsx");

% Kategorien und Werte zuweisen
category = data.Cell;
values = [data.Can; data.CurrentCollectorAnode; data.CoatingAnode; data.Separator; data.CurrentCollectorCathode; data.CoatingCathode; data.TabCopper; data.TabAluminium];

% Balkendiagramm erstellen
bar(values);

% Achsenbeschriftungen setzen
xlabel('categories');
ylabel('values');

% Y-Achse logarithmisch skalieren
set(gca, 'YScale', 'log');

% Titel des Diagramms
% title('Balkendiagramm');

But, it's the Excel file we need...

Florian 2024-8-8，16:56