Gegevens ontpivoteren

Over ontpivoteren

Rapporten komen breed binnen, want zo lezen mensen ze: één rij per product, één kolom per maand, totalen onderaan. Dat is de juiste vorm voor een gedrukte pagina en de verkeerde voor vrijwel al het andere.

Elk hulpmiddel dat die gegevens wil plotten, draaien, filteren of importeren wil ze juist lang: één rij per product per maand, met de maand als waarde in een kolom in plaats van als kop erboven.

• Breed — Product | jan | feb | mrt
• Lang — Product | Maand | Omzet, drie keer zoveel rijen

Excel kan dit via Power Query, wat neerkomt op meerdere dialoogvensters, een laadstap en een vernieuwing. Hier zijn het twee vinkjes en een naam.

Zo gebruik je het

• Plak je rijen of upload een spreadsheet
• Vink de kolommen aan die blijven zoals ze zijn — die de rij benoemen, meestal de eerste of de eerste twee
• Alles wat je niet aanvinkt wordt rijen. Geef de twee nieuwe kolommen meteen een naam
• Download als CSV of Excel, of kopieer het terug

De eerste kolom staat alvast aangevinkt, want in vrijwel elk breed rapport is dat de identificatie.

Lege cellen vallen standaard weg

Een breed raster is meestal dun bezet. Niet elk product verkocht in elke maand, dus een flink deel van de cellen is leeg — en die meenemen levert een lange tabel op die vooral uit niets bestaat.

Lege cellen worden daarom overgeslagen tenzij je erom vraagt. Lege cellen als rijen behouden is er voor het geval een leegte betekenisvol is in plaats van ontbrekend: een enquête waarin "geen antwoord" een echt antwoord is, of een rooster waarin een vrij blok zichtbaar moet blijven.

Als het rapport twee koprijen heeft

Gedrukte rapporten dragen vaak een titelrij boven de echte koppen — een samengevoegde cel met Omzet per maand boven jan, feb, mrt. Als gegevens gelezen wordt die bovenste rij de koprij, worden de echte koppen de eerste rij waarden, en is het resultaat onzin met een kolom die Omzet per maand heet en een andere zonder naam.

Ontpivoteren kan niet raden welke rij welke is, want beide zijn tekst en beide staan boven de getallen. De oplossing is de sierrij te verwijderen vóór je plakt — één klik in de spreadsheet, en alles daarna klopt.

Hetzelfde geldt voor een totalenrij onderaan. Dat is geen waarneming en hoort dus niet in een lange tabel thuis; laat hem buiten de selectie, of verwijder hem uit het resultaat. Hem stilletjes meenemen verdubbelt elk cijfer zodra iemand de waardekolom optelt.

Waar mensen het voor gebruiken

• Een breed rapport bruikbaar maken voor een draaitabel, die één rij per waarneming wil
• Maand- of kwartaalkolommen in een vorm krijgen die een grafiek over de tijd kan uitzetten
• Een spreadsheet klaarmaken voor import in een database, die één waarde per rij wil
• Een begrotingsraster omzetten in een lijst met posten
• Enquêteresultaten hervormen waarin elke vraag een eigen kolom werd
• Tidy data produceren voor R, pandas of elk analysehulpmiddel dat dat verwacht

Goed om te weten

• Werkt op Windows, macOS, Linux, ChromeOS en op telefoons en tablets — het neemt geplakte tekst of een bestand aan, geen map
• Er wordt niets geüpload. Lezen en hervormen gebeuren allebei in de pagina
• De eerste rij is altijd de koprij, want de kolomkoppen worden de waarden in de nieuwe kenmerkkolom — zonder hen valt een tabel niet te ontpivoteren
• Alle kolommen aanvinken zou niets overlaten om te ontpivoteren, dus valt het hulpmiddel terug op alleen de eerste behouden
• De rijvolgorde volgt het origineel: eerst alle waarden van de eerste rij, dan alle van de tweede
• De voorbeeldweergave toont de eerste 200 rijen; de download bevat er altijd alle

Gerelateerde hulpmiddelen

• Groeperen en optellen — de andere richting — rijen samenvouwen tot een overzicht
• Rijen en kolommen omwisselen — in plaats daarvan de hele tabel kantelen
• Kolommen splitsen & samenvoegen — de kolommen zelf hervormen
• Excel naar JSON — de lange tabel ergens anders mee naartoe nemen