Show rows in columns and count occurrences

RodrigoAbadia

I am starting with Aracade in dashboard and I have a table with data in column 1 with the sectionals and another column with marital status, as shown below:

Dirección Seccional	Estado civil
MEDELLIN	CASADO
CAUCA	UNION_LIBRE
MAGDALENA MEDIO	CASADO
CUNDINAMARCA	SOLTERO
MEDELLIN	SEPARADO
MEDELLIN	CASADO
NARIÑO	CASADO
MEDELLIN	SOLTERO
ANTIOQUIA	SOLTERO
ANTIOQUIA	SOLTERO
MEDELLIN	CASADO
ANTIOQUIA	CASADO
MEDELLIN	CASADO
ANTIOQUIA	UNION_LIBRE
ANTIOQUIA	CASADO
MEDELLIN	SOLTERO
ANTIOQUIA	SOLTERO
MEDELLIN	SOLTERO
MEDELLIN	CASADO
ANTIOQUIA	SEPARADO
ANTIOQUIA	CASADO

I need to create a new data expression to display the sections by marital status as columns that count the amount for each marital status, and it should look like this:

SECCIONAL	CASADO	SEPARADO	SOLTERO	UNION_LIBRE	Total general
AMAZONAS	1		1	4	6
ANTIOQUIA	100	4	51	41
ARAUCA	11	2	11	6
ATLANTICO	54	7	29	23
BOGOTA	215	40	147	82
BOLIVAR	7	2	5	5
BOYACÁ	88	24	55	30
CALDAS
CALI
CAQUETA
CASANARE

 

Could someone help me with this need? Or has someone already done a similar development to guide me?

 

RodrigoAbadia

To complete the above request, I have the following script, but I cannot get the counts.
I ask someone can help me, thank you.

 

 

var fs = FeatureSetByPortalItem(Portal('https://www.arcgis.com'),'xyz',0,['*'],false);

var value_counts = {
    "AMAZONAS": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "ANTIOQUIA": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "ATLÁNTICO": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "ARAUCA": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "BOGOTÁ D.C.": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "BOLÍVAR": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "BOYACÁ": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "CALDAS": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "CAQUETA": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "CASANARE": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "CAUCA": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "CESAR": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "CUNDINAMARCA": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "CHOCÓ": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "CÓRDOBA": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "HUILA": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "GUAJIRA": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "MAGDALENA": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "META": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "NARIÑO": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "NORTE DE SANTANDER": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "PUTUMAYO": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "QUINDÍO": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "RISARALDA": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "SAN ANDRÉS": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "SANTANDER": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "SUCRE": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "TOLIMA": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "VALLE DEL CAUCA": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "VAUPÉS": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0},
    "VICHADA": {'CASADO': 0,'SEPARADO' : 0,'SOLTERO' : 0,'UNION_LIBRE' : 0}
};

for (var i in fs){
  var split_array = Split(i['est_civil'], ',');
  var choiceArray = [];
  for (var index in split_array) push(choiceArray, split_array[index])

  for(var i in choiceArray){
    var value = choiceArray[i]

    if(IsEmpty(value)) {continue}
    if(i == [0]) value_counts[value]['CASADO'] = value_counts[value]['CASADO'] + 1
    if(i == [1]) value_counts[value]['SEPARADO'] = value_counts[value]['SEPARADO'] + 1
    if(i == [2]) value_counts[value]['SOLTERO'] = value_counts[value]['SOLTERO'] + 1
    if(i == [3]) value_counts[value]['UNION_LIBRE'] = value_counts[value]['UNION_LIBRE'] + 1
  }
}

var Dict = {  
  'fields': [
   { 'name': 'SECCIONAL', 'type': 'esriFieldTypeString' },  
   //{ 'name': 'Average','type': 'esriFieldTypeDouble'},  
   { 'name': 'CASADO','type': 'esriFieldTypeInteger'},  
   { 'name': 'SEPARADO','type': 'esriFieldTypeInteger'},  
   { 'name': 'SOLTERO','type': 'esriFieldTypeInteger'},  
   { 'name': 'UNIÓN LIBRE','type': 'esriFieldTypeInteger'}],    
 'geometryType': '',  
 'features': []
 };  

var index = 0
for (var value in value_counts){
  Dict.features[index] = {
    'attributes': {
      'SECCIONAL': value,
      'CASADO': value_counts[value]['CASADO'],
      'SEPARADO': value_counts[value]['SEPARADO'],
      'SOLTERO': value_counts[value]['SOLTERO'],
      'UNIÓN LIBRE': value_counts[value]['UNION_LIBRE']
     // 'Average': value_counts[value]['Average']
    }
  }
  index++;
}

return FeatureSet(Dict);

 

 

I get the following output:

SECCIONAL CASADO SEPARADO SOLTERO UNIÓN LIBRE FID

"AMAZONAS"	0	0	0	0	0
"ANTIOQUIA"	0	0	0	0	1
"ARAUCA"	0	0	0	0	2
"ATLÁNTICO"	0	0	0	0	3
"BOGOTÁ D.C."	0	0	0	0	4
"BOLÍVAR"	0	0	0	0	5
"BOYACÁ"	0	0	0	0	6
"CALDAS"	0	0	0	0	7
"CAQUETA"	0	0	0	0	8
"CASANARE"	0	0	0	0	9
"CAUCA"	0	0	0	0	10
"CESAR"	0	0	0	0	11
"CHOCÓ"	0	0	0	0	12
"CUNDINAMARCA"	0	0	0	0	13
"CÓRDOBA"	0	0	0	0	14
"GUAJIRA"	0	0	0	0	15
"HUILA"	0	0	0	0	16
"MAGDALENA"	0	0	0	0	17
"META"	0	0	0	0	18
"NARIÑO"	0	0	0	0	19
"NORTE DE SANTANDER"	0	0	0	0	20
"PUTUMAYO"	0	0	0	0	21
"QUINDÍO"	0	0	0	0	22
"RISARALDA"	0	0	0	0	23
"SAN ANDRÉS"	0	0	0	0	24
"SANTANDER"	0	0	0	0	25
"SUCRE"	0	0	0	0	26
"TOLIMA"	0	0	0	0	27
"VALLE DEL CAUCA"	0	0	0	0	28
"VAUPÉS"	0	0	0	0	29
"VICHADA"	0	0	0	0	30