Community
Select to view content in your preferred language

Show rows in columns and count occurrences

590
1
04-08-2024 07:42 AM
RodrigoAbadia
by
New Contributor
‎04-08-2024 07:42 AM

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 SeccionalEstado civil
MEDELLINCASADO
CAUCAUNION_LIBRE
MAGDALENA MEDIOCASADO
CUNDINAMARCASOLTERO
MEDELLINSEPARADO
MEDELLINCASADO
NARIÑOCASADO
MEDELLINSOLTERO
ANTIOQUIASOLTERO
ANTIOQUIASOLTERO
MEDELLINCASADO
ANTIOQUIACASADO
MEDELLINCASADO
ANTIOQUIAUNION_LIBRE
ANTIOQUIACASADO
MEDELLINSOLTERO
ANTIOQUIASOLTERO
MEDELLINSOLTERO
MEDELLINCASADO
ANTIOQUIASEPARADO
ANTIOQUIACASADO

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:

SECCIONALCASADOSEPARADOSOLTEROUNION_LIBRETotal general
AMAZONAS1 146
ANTIOQUIA10045141 
ARAUCA112116 
ATLANTICO5472923 
BOGOTA2154014782 
BOLIVAR7255 
BOYACÁ88245530 
CALDAS     
CALI     
CAQUETA     
CASANARE     

 

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

 

0 Kudos
1 Reply
RodrigoAbadia
by
New Contributor
‎04-09-2024 08:37 AM

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'),'xxxx',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"00000
"ANTIOQUIA"00001
"ARAUCA"00002
"ATLÁNTICO"00003
"BOGOTÁ D.C."00004
"BOLÍVAR"00005
"BOYACÁ"00006
"CALDAS"00007
"CAQUETA"00008
"CASANARE"00009
"CAUCA"000010
"CESAR"000011
"CHOCÓ"000012
"CUNDINAMARCA"000013
"CÓRDOBA"000014
"GUAJIRA"000015
"HUILA"000016
"MAGDALENA"000017
"META"000018
"NARIÑO"000019
"NORTE DE SANTANDER"000020
"PUTUMAYO"000021
"QUINDÍO"000022
"RISARALDA"000023
"SAN ANDRÉS"000024
"SANTANDER"000025
"SUCRE"000026
"TOLIMA"000027
"VALLE DEL CAUCA"000028
"VAUPÉS"000029
"VICHADA"000030
0 Kudos