How can I count specific value in one column but based on another four columns

Hello,

How can I count specific value in one colum but based on another four columns

Example

Int_name     Country #       Milepost    lighting condition      Frequency 
'"A" ST'    '06223 15 D'       20.92	           'C'	              ?
'"A" ST'    '06223 15 D'       20.92	           'C'	
'"A" ST'    '06145 20 D'       10.09	           'A'	
'"A" ST'    '06145 20 D'       10.09	           'A'		

I need to count lighting condition: A or C frequency under same milepost under same country number under same Int_name

Like for "A" ST' , country number 06223 15 D and mile post 20.92 we have C ocuured 2 times. and so on for a large data.

Answer 1

[Andrei Bobrov]

Tout = varfun(@(x)numel(x),T,'g',1:4);

[Shaden AbedRabbo]

Thank you! its work.

I have one question, if I want it to be repeated not unique since "I have another colums with different values" but my main variables are light condition and location only

But to get full information it need to be repeated, How it will be done?

Example

Int_name country milepost light freq vehicle gender age

'"A" ST' '06223 15 D' 20.92 'C' 2 1 M 23

'"A" ST' '06223 15 D' 20.92 'C' 2 2 F 55

[Adam Danz]

+1 much simpler than my answer (since removed due to clear inferiority).

[Guillaume]

Note: from a didactic point of view, it would be much better to give answers that use the full names of Names/Values pair, so learners don't have to work out what 'g' stands for. In this instance:

Tout = varfun(@(x)numel(x),T,'GroupingVariables',1:4);

also the indirection with an anonymous function is not needed:

Tout = varfun(@numel, T, 'GroupingVariables', 1:4)

and of course, the actual names of the variables can be used instead of indices (more reliable in case the order is changed) as long as the variables are correctly spelled.

[Adam Danz]

I agree, Guillaume. I only use full names in all of my codes just in case some day someone will pick it up with less familiarity. Using the full names gives others the chance to look up the meaning of the property.

Answer 2

[Guillaume]

Assuming that your dara is in a table (it would be useful if the example used valid matlab syntax):

result = groupsummary(yourtable, {'int_Name', 'Country', 'Milepost'}, @numel, 'lighting_condition')

[Shaden AbedRabbo]

Thank you!

But I got an error. do you have any idea about it ?

[Andrei Bobrov]

may be

.. @numel,'LIGHT')

[Shaden AbedRabbo]

Thank you, its work!

I have one question, if I want it to be repeated not duplicate since "I have another colums with different values" but my main variables are light condition and location only

But to get full information it need to be repeated, How it will be done?

Example

Int_name country milepost light freq vehicle gender age

'"A" ST' '06223 15 D' 20.92 'C' 2 1 M 23

'"A" ST' '06223 15 D' 20.92 'C' 2 2 F 55

[Guillaume]

Variables need to be correctly spelled, including case.

result = groupsummary(updateddata1, {'int_desc', 'cnty_rte', 'milepost', 'LIGHT'}, @numel, 'LIGHT')

edit: I've just realised that what you want is an histogram, in that case 'LIGHT' is also a grouping variable, and the data variable that you choose to pass to numel is irrelevant.

[Shaden AbedRabbo]

Sorry, to be repeated not unique. Since I have another variables like gender, age ,etc. depending on them.

Ex:

The code you provided, gave me a result :

'"A" ST' '06223 15 D' 20.92 'C' 2 so any other line with same value removed.

But i need it to be repeated for each line with same values like:

Int_name country milepost light freq vehicle gender age

'"A" ST' '06223 15 D' 20.92 'C' 2 1 M 23

'"A" ST' '06223 15 D' 20.92 'C' 2 2 F 55

So I will know the total number of light C in mile post 20.92 one for male with age 23 and other for female with age 55.

Sorry, Iam trying to explain exactly.

[Guillaume]

I think I understand. In that case, the simplest is to go with findgroups, build the histogram of that, then redistribute the histogram in the table according to the table

group = findgroups(updateddata1(:, {'int_desc', 'cnty_rte', 'milepost', 'LIGHT'}));
count = accumarray(group, 1);   %one of the many ways to get an histogram in matlab. See also histcounts
updateddata1.Frequency = count(group);

[Shaden AbedRabbo]

It works!! Thank you! best thank