JExcel API - címke - it-tanfolyam.hu

Sankey-diagram készítése

2023. április 17. Szerző: Kaczur Sándor

A Sankey-diagram alkalmas kétféle adatsor közötti N:M fokszámú kapcsolat, összefüggés és a köztes átmenet ábrázolására. Hangsúlyozza a fő átvitelt vagy áramlatokat egy rendszeren belül. Az áramlás irányát nyíllal szemlélteti és az áramlatok szélessége arányos az áramlási mennyiségekkel.

Feladat

Jelenítsük meg HTML formátumú weboldalként a magyarországi régiókban a foglalkoztatottak számát nemzetgazdasági szektorok szerint a KSH 2018-as adatsora alapján! Automatizáljuk egy Java programmal úgy a feladatot, hogy az év paraméterként megadható legyen!

Tervezés

A KSH témastruktúrában a táblázat elérési útja:

5. Területi adatok,
5.1. A munkaerő-piaci tendenciák Magyarország régióiban,
5.1.3. A foglalkoztatottak száma nemzetgazdasági szektorok szerint, nemenként (2008–)

Online böngészhető táblázat:
https://www.ksh.hu/docs/hun/xstadat/xstadat_hosszu/h_qlf017.html

Letölthető táblázat (XLS formátumban):
https://www.ksh.hu/docs/hun/xstadat/xstadat_hosszu/xls/h5_1_3.xls

A táblázatban lévő adatforrás szükséges része látható az ábrán:

A táblázatban a régiók az A105:A112 cellatartományban találhatók. A hozzájuk tartozó 3 nemzetgazdasági szektor a B-C-D oszlopok azonos soraiból olvashatók ki. POJO-k létrehozása mindenképpen hasznos a megvalósításhoz, például new SankeyData("Közép-Dunántúl", "Szolgáltatás", 253.89). Ezekből generikus listát is célszerű építeni: List<SankeyData> sankeyDataList.

Többféleképpen is hozzájuthatunk az adatokhoz attól függően, hogy milyen előismeretekkel rendelkezünk a különböző tanfolyamainkon:

A Java SE szoftverfejlesztő tanfolyamon „kézzel” letölthetjük a projekt files mappájába az XLS fájlt. Ezután akár manuálisan is összeállítható a POJO lista, vagy a JExcel API-val is hatékonyan feldolgozható a XLS fájl aktuális munkalapja. Fájlkezelés előtt az összeállított HTML fájlt kiírathatjuk a konzolra, ahonnan „kézzel” vágólapozva létrehozhatjuk belőle a szükséges HTML fájlt. Fájlkezeléssel persze adott mappába, adott fájlnévvel, kivételkezeléssel a java.io vagy java.nio csomagot használva a HTML fájl generálása is automatizálható.
A Java EE szoftverfejlesztő tanfolyamon megvalósítható, hogy a program kivételkezeléssel hálózati kapcsolatot épít, majd letölti az XLS fájlt és ezzel a feladat visszavezethető az előző esetekre. Azt is megtehetjük, hogy az XLS fájlt nem töltjük le, hanem olvasunk belőle közvetlenül a webről. Ekkor is rendelkezésünkre áll a POJO lista. Itt már tudunk HTML fájlt is automatikusan generálni.

Tanulmányoznunk kell a Google Charts galériában a Sankey diagram dokumentációját! Meg kell ismernünk a paraméterezési lehetőségeit és JavaScript forráskódját!

Megvalósítás

private String createSankeyDiagram(List<SankeyData> sankeyDataList) {

String sankeyData=

sankeyDataList.stream().map(SankeyData::toString).

collect(Collectors.joining(",\n ", " ", "\n"));

String html=

"<html>\n" +

" <head>\n" +

" <script type=\"text/javascript\" " +

" src=\"https://www.gstatic.com/charts/loader.js\"></script>\n" +

" <script type=\"text/javascript\">\n" +

" google.charts.load('current', {'packages':['sankey']});\n" +

" google.charts.setOnLoadCallback(drawChart);\n" +

"\n" +

" function drawChart() {\n" +

" var data = new google.visualization.DataTable();\n" +

" data.addColumn('string', 'Régió');\n" +

" data.addColumn('string', 'Nemzetgazdasági szektor');\n" +

" data.addColumn('number', 'Foglalkoztatottak száma (ezer fő)');\n" +

" data.addRows([\n" +

"#SankeyData#" +

" ]);\n" +

"\n" +

" var options = {\n" +

" width: 500,\n" +

" };\n" +

"\n" +

" var chart=new google.visualization.Sankey("+

" document.getElementById('sankey_basic'));\n" +

" chart.draw(data, options);\n" +

" }\n" +

" </script>\n" +

" </head>\n" +

" <body>\n" +

" <div id=\"sankey_basic\" style=\"width: 500px; height: 300px;\"></div>\n" +

" </body>\n" +

"</html>";

return html.replace("#SankeyData#", sankeyData);

}

A createSankeyDiagram() függvény létrehozza a HTML fájl szöveges tartalmát. Átveszi adatforrásként a sankeyDataList generikus POJO listát. A String típusú sankeyData objektum tartalmazza a Stream API-val hatékonyan összefűzött – POJO-któl elkért – toString() szövegeket. Ezek a diagramhoz szükséges adatok ( addRows …). Például: "['Közép-Dunántúl', 'Szolgáltatás', 253.89]". A String típusú html objektum kezdetben tartalmazza a diagramhoz nem szükséges fix részeket, a diagram alapbeállításait, valamint a diagram fejlécéhez szükséges metaadatokat ( addColumn… Régió, Nemzetgazdasági szektor, Foglalkoztatottak száma (ezer fő)). A függvény végül a html objektum #SankeyData# részét cseréli a sankeyData-val és az adatfüggő résszel frissített HTML tartalommal tér vissza.

Eredmény

Az egyik eredmény a generált HTML fájl (benne a grafikonhoz tartozó JavaScript) forráskódját tartalmazza:

<html>

<head>

src="https://www.gstatic.com/charts/loader.js"></script>

google.charts.load('current', {'packages':['sankey']});

google.charts.setOnLoadCallback(drawChart);

function drawChart() {

var data=new google.visualization.DataTable();

data.addColumn('string', 'Régió');

data.addColumn('string', 'Nemzetgazdasági szektor');

data.addColumn('number', 'Foglalkoztatottak száma (ezer fő)');

data.addRows([

['Budapest', 'Mezőgazdaság', 1.251],

['Budapest', 'Ipar', 148.004],

['Budapest', 'Szolgáltatás', 684.56],

['Pest', 'Mezőgazdaság', 11.845],

['Pest', 'Ipar', 182.934],

['Pest', 'Szolgáltatás', 400.425],

['Közép-Dunántúl', 'Mezőgazdaság', 22.684],

['Közép-Dunántúl', 'Ipar', 222.544],

['Közép-Dunántúl', 'Szolgáltatás', 253.89],

['Nyugat-Dunántúl', 'Mezőgazdaság', 25.64],

['Nyugat-Dunántúl', 'Ipar', 202.794],

['Nyugat-Dunántúl', 'Szolgáltatás', 253.44],

['Dél-Dunántúl', 'Mezőgazdaság', 26.865],

['Dél-Dunántúl', 'Ipar', 122.281],

['Dél-Dunántúl', 'Szolgáltatás', 224.863],

['Észak-Magyarország', 'Mezőgazdaság', 21.866],

['Észak-Magyarország', 'Ipar', 186.662],

['Észak-Magyarország', 'Szolgáltatás', 276.754],

['Észak-Alföld', 'Mezőgazdaság', 48.301],

['Észak-Alföld', 'Ipar', 206.593],

['Észak-Alföld', 'Szolgáltatás', 384.82],

['Dél-Alföld', 'Mezőgazdaság', 56.402],

['Dél-Alföld', 'Ipar', 174.068],

['Dél-Alföld', 'Szolgáltatás', 329.982]

]);

var options={

width: 500,

};

var chart=new google.visualization.Sankey(

document.getElementById('sankey_basic'));

chart.draw(data, options);

}

</script>

</head>

<body>

</body>

</html>

A másik eredmény a Sankey-diagram képernyőképe, amelyről kiválóan leolvashatók az értékek:

A böngészőben megjelenő HTML oldalon a Sankey-diagram dinamikusan – az egérkurzor pozíciójától függően – képes az aktuális adatok megjelenítésére, mintegy lebegő jelmagyarázatként.

A bejegyzéshez tartozó teljes forráskódot ILIAS e-learning tananyagban tesszük elérhetővé tanfolyamaink résztvevői számára.

KSH táblázatból dolgozunk

2023. január 3.2020. január 17. Szerző: Kaczur Sándor

A Központi Statisztikai Hivatal honlapján elérhető STADAT táblákból könnyen kinyerhetjük a nekünk szükséges adatokat. A témastruktúrába sorolt online és XLS exportként is böngészhető táblázatokban megtalálhatjuk logikusan csoportosítva összesítve az adatokat régiónként (megyénként), évenként, százalékosan. Az XLS fájlformátum Java nyelven a JExcel API-val hatékonyan feldolgozható. Lássunk erre egy példát!

Feladat

A KSH 2.1.2.35. táblázatából gyűjtsük ki a 19 magyar megyére + Budapestre vonatkozóan a gazdaságilag aktívak létszámát és az első évet alapnak tekintve adjuk meg évenként a változást százalékosan!

Tervezés

A KSH témastruktúrában a táblázat elérési útja:

2. Társadalom,
2.1. Munkaerőpiac,
2.1.2. A munkaerőpiac alakulása Magyarországon (1998–2018) -> Területi adatok,
2.1.2.35. A 15–64 éves népesség gazdasági aktivitása megyénként és régiónként (1998–2018)

Online böngészhető táblázat:
https://www.ksh.hu/docs/hun/xstadat/xstadat_hosszu/mpal2_01_02_35.html.

Letölthető táblázat (XLS formátumban): https://www.ksh.hu/docs/hun/xstadat/xstadat_hosszu/xls/h2_1_2_35.xls.

A táblázat A oszlopában szerepelnek a régiók, megyék, időszakok (vegyesen, szövegként) és a D oszlopában a gazdaságilag aktívak (ezer fő, valós számként). A fejlécet nem szabad feldolgozni. 1998-tól 2018-ig 546 sorból áll az adatsor. A csoportosítás 26 régiót és megyét tartalmaz, amiből a 6 régiót (például: Közép-Dunántúl) ki kell hagyni.

A megyékre vonatkozóan 440 sort kell feldolgozni. Ebből az első sor a megye (vagy Budapest) neve, a többi (2019-ben 21 db) sorban találhatók az adatok (időszak). Olyan algoritmusban érdemes gondolkodni, ami a jövőben is működik. Ha csoportváltást alkalmazunk, akkor nem számít, hogy megyénként minden évben egy sornyival több adat lesz majd. A KSH táblázatok szerkezete nagyon ritkán változik, így bátran írható rájuk testre szabott forráskód (ezeket nem kell évente frissíteni).

Az évenkénti változást százalékosan nem tartalmazza a táblázat, ezt nekünk kell kiszámítani. A valós számok formázását érdemes egységesíteni, például a gazdaságilag aktívak létszámát 3 tizedesre, a változást 2 tizedesre kerekítve.

A belső adatábrázolást érdemes átgondolni. Hasznos, ha az időszakhoz tartozó három összetartozó adatot egyetlen Data POJO-ba fogjuk össze ( String period, double active és double change). Ezeket generikus listába szervezve ( ArrayList<Data> list) könnyen hozzájuk rendelhető a megye ( String county) és ezek együtt alkotják a Region POJO-t. A Region és Data kapcsolati fokszáma: 1:N. 2019-ben N=21 .

Részlet a megoldásból

private List<Region> readXLS() throws IOException, BiffException {

WorkbookSettings workbookSettings=new WorkbookSettings();

workbookSettings.setEncoding("Cp1252");

Workbook workbook=Workbook.getWorkbook(

new File("./files/h2_1_2_35.xls"), workbookSettings); //munkafüzet

Sheet sheet=workbook.getSheet("2.1.2.35."); //munkalap

List<String> skipRegionList=Arrays.asList(new String[] {

"Közép-Dunántúl", "Nyugat-Dunántúl", "Dél-Dunántúl",

"Észak-Magyarország", "Észak-Alföld", "Dél-Alföld"});

List<Region> list=new ArrayList<>();

int i=3; //kihagyjuk a fejlécet

while(i<sheet.getRows()) {

Cell cellA=sheet.getCell(0, i); //megyék

if(cellA.getType()==CellType.LABEL) {

String county=((LabelCell)cellA).getString().trim();

Region region=new Region(county);

i++;

Cell cellD=sheet.getCell(3, i); //gazdaságilag aktívak

while(i<sheet.getRows() && cellD.getType()!=CellType.EMPTY) {

cellA=sheet.getCell(0, i); //időszakok

String period="";

if(cellA.getType()==CellType.LABEL)

period=((LabelCell)cellA).getString().trim();

double active=0;

if(cellD.getType()==CellType.NUMBER)

active=((NumberCell)cellD).getValue();

region.addList(period, active);

i++;

if(i<sheet.getRows())

cellD=sheet.getCell(3, i);

}

if(!skipRegionList.contains(county))

list.add(region);

}

workbook.close();

return list;

}

A JExcel API használatához a Java projekthez hozzá kell adni a jxl.jar fájlt. A XLS fájl olvasható közvetlenül a webről is, de egyszerűbb helyi fájlrendszerbe mentett változatból dolgozni ( ./files/h2_1_2_35.xls). A megyék nevében található ékezetes karakterek miatt ügyelni kell a megfelelő karakterkódolásra ( Cp1252). A munkafüzet azonosítását követően hivatkozni kell a feldolgozandó munkalapra ( 2.1.2.35.). Az adatfeldolgozás során kihagyott régiókat (kivételeket) érdemes listába gyűjteni ( skipRegionList). A csoportváltást a két egymásba ágyazott ciklus valósítja meg. Ügyelni kell az adatok formátumának ellenőrzésére.

Eredmények

Például Somogy megyére az alábbi adatokat kapjuk eredményként (XLS formátumban, Excel-be betöltve, tipikus háttérszín kiemeléssel: szélsőértékek a C oszlopban, negatív értékek a D oszlopban):

További programozható feladatok

Hogyan alakult a magyar autóbuszgyártás 1960-tól évtizedenként csoportosítva (átlag, min, max, szórás)? – 4.2. Ipar (1960–)
Milyen a lakossági építkezés aránya az ezredforduló után a teljes építőipari termelés értékéhez viszonyítva évenkénti bontásban? – 4.3.1. Az országos építőipari termelés értéke kivitelezők szerint (1990–)
Milyen kapcsolat figyelhető meg a szállodák száma és a kiadható férőhelyek száma között a 2008-ban kirobbant gazdasági világválság előtt és után? – 4.5.11. A kereskedelmi szálláshelyek kapacitása, július 31. (2001–)
Melyik években volt a legmagasabb (TOP 3) a saját mobilhálózatba irányuló beszélgetések hossza az összes mobilhálózatba irányuló beszélgetések hosszához viszonyítva? – 4.7.5. A mobilhálózatokból kiinduló beszélgetések (2001–)
Hogyan alakultak az üzemanyagok éves fogyasztói átlagárai (átlag, min, max, szórás)? – 3.6.3. Egyes termékek és szolgáltatások éves fogyasztói átlagára (1996–)
Ábrázoljuk tematikus térképen: hogyan alakult régiónként a munkanélküliségi ráta a mindenkori utolsóként megadott negyedévben az előző év azonos negyedévéhez viszonyítva! – 6.2.1.11. Munkanélküliségi ráta

A bejegyzéshez tartozó teljes forráskódot ILIAS e-learning tananyagban tesszük elérhetővé tanfolyamaink résztvevői számára.

A feladat a Java SE szoftverfejlesztő tanfolyam tematikájához kötődik (ha az XLS fájlt a helyi fájlrendszerből érjük el), és a Java EE szoftverfejlesztő tanfolyam tematikájához kapcsolódik (ha az XLS fájl tartalmát közvetlenül a webről olvassuk).

Doktoranduszok programoznak

2023. január 3.2018. november 16. Szerző: Kaczur Sándor

Saját doktorandusz csoporttársaimmal többször beszélgettünk már arról, hogyan tudnák/tudják használni a programozás eszköztárát, módszereit, lehetőségeit saját kutatási munkájukban, beépítve a kutatási folyamat egyes lépéseibe, illetve disszertációjuk elkészítésébe.

Mivel a 10 fős csoportban mindenkinek más az alapvégzettsége, így szoftverfejlesztéshez, programozáshoz közös szókincs és terminológia haladó szinten természetesen nincs, viszont közös bennünk, hogy mindannyian alkotunk különféle modelleket és elemzünk adatokat.

Különféle modelleket alkotunk

a mérnökök, fizikusok, geográfusok, biológusok többféle kísérletet végeznek el, szimulációkat terveznek és futtatnak, mérőeszközöket és műszereket használnak,
az informatikusok különböző matematikai eszközöket alkalmazva objektumorientált – vagy másféle – modellezést végeznek, szoftvereket terveznek, javítanak, újraírnak.

Adatokat is elemzünk, ki-ki előképzettségének megfelelően

kérdőívező szoftverekből exportálva valamit,
Excel munkalapokon, függvényekkel, adatbázis-kezelő funkciókkal, kimutatásokkal (Pivot táblák),
különböző fájlformátumokkal (CSV, XML, JSON, egyedi) dolgozunk és konvertálunk A-ból B-be,
távoli adatbázisokhoz, felhőbeli adattárházakhoz csatlakozunk, lekérdezünk és kapunk valamilyen – többnyire szabványos – adathalmazt,
matematikai, statisztikai szoftvereket használunk, például: MATLAB, Derive, Maple, SPSS.

Önszerveződően összeállítottunk egy olyan két részből álló tematikát, ami mindannyiunk számára hasznos. A 64 óra két 32 órás modulból áll: Java programozás és SPSS programrendszer.

Java programozás modul

1-8. óra: Objektumorientált modellezés, MVC rétegek, algoritmus- és eseményvezérelt programozás
9-16. óra: Fájlkezelés és szövegfeldolgozás (XLS, CSV, XML, JSON formátumú adatok írása, olvasása, feldolgozása)
17-24. óra: Adatbázis-kezelés JDBC alapon (SQL parancsok, CRUD műveletek, hierarchikus lekérdezések)
25-32. óra: Komplex adatfeldolgozási feladatok megoldása programozási tételek használatával

SPSS programrendszer modul

1-8. óra: Bevezetés az SPSS-be, interakciós eszközök, adatmátrix, menük: Transform, Analyze, szkriptek futtatása
9-16. óra: Alapstatisztikák kérése, kereszttáblázatok készítése, hipotéziselmélethez kötődő funkciók, normalitásvizsgálat, minták összehasonlítása t-próbával
17-24. óra: Regresszió-analízis: lineáris, nemlineáris, többváltozós; Idősorok elemzése: szűrés, periodogram, trendelemzés
25-32. óra: Mesterséges neuronhálózatok: matematikai modell és működése

Mivel mindenki doktorandusz a csoportban, így a különböző MSc-s alapvégzettsége ellenére mindannyiunknak vannak strukturális programozáshoz kötődő alapismeretei, valamint adatok elemzéséhez szükséges elméleti matematikai/statisztikai alapjai. Az én részem a 32 órás Java programozás modul, ami 2018.10.28-án kezdődött és 2018.12.09-ig fog tartani hétvégi napokon. Ez nagyban lefedi a Java SE szoftverfejlesztő tanfolyamunk tematikáját és kapcsolódik a Java EE szoftverfejlesztő tanfolyamunk és a Java adatbázis-kezelő tanfolyamunk tematikájához is.

A koncepciót once-in-a-lifetime jelleggel dolgoztuk ki azzal a fő szándékkal, hogy hatékonyabban működjünk együtt a jövőben.