Nushell create a Pivot (Transpose) table

Nushell_Pivot_Trnaspose_by_Year.md

Pivot table of Auto Expenses

Show in a Pivot table how much has been spend by Auto and Year The data has to be cleaned and have fields added

• Remove commas from the Amount column
• Add Year and Yearmon columns
• Spilt the Category column into Auto and Work Columns

This data comes from MS Money, using UE to remove "total" lines, and blank lines.

Nushell Code without Total Row

open 'D:\work\Visidata\ko_auto.csv'
| update Amount  {|row| $row.Amount | into string  | str replace ',' '' | into float | $in  * -1 }
| insert Year    { $in.Date | into datetime | format date '%Y' | into int }
| insert Yearmon { $in.Date | into datetime | format date '%Y%m' | into int }
| insert Auto    { $in.Category | split row ':' | get 0 | str trim }
| insert Work    { $in.Category | split row ':' | get 1? | str trim }
| where Auto not-in ['Taxes']
| group-by Auto Year --to-table
| sort-by Auto
| each {|it|
    {
        Auto:  $it.Auto,
        Year:  $it.Year
        Total: ($it.items.Amount | math sum | math abs | math round -p 0)
    }
}
| sort-by Year --reverse
| group-by Auto
| items {|auto, rows|
    $rows
    | reduce -f { Auto: $auto } {|it, acc|
        $acc | insert ($it.Year | into string) ($it.Total | into int )
    }
}

Output

Auto	2026	2025	2024	2023	2022	2021	2020	2019	2018	2017	2016	2015
2014 Nissan Altima	105	8731	1655	990	60	28	3231
2012 Lexus RX 350	127	3235	2525	1469	72	440
2011 Honda Civic	84		422	22			1777
2004 Chevrolet Silverado		4336		659	72		166	5141	5145
2003 Jeep Liberty									1469	1488	2579	723
1999 Dodge Intrepid												1185

Nushell Code with Total Row

# with total row
 let pivot = (
     open 'D:\work\Visidata\ko_auto.csv'
     | update Amount  {|row| $row.Amount | into string | str replace ',' '' | into float | $in * -1 }
     | insert Year    { $in.Date | into datetime | format date '%Y' | into int }
     | insert Yearmon { $in.Date | into datetime | format date '%Y%m' | into int }
     | insert Auto    { $in.Category | split row ':' | get 0 | str trim }
     | insert Work    { $in.Category | split row ':' | get 1? | str trim }
     | where Auto not-in ['Taxes']
     | group-by Auto Year --to-table
     | sort-by Auto
     | each {|it|
         {
             Auto:  $it.Auto,
             Year:  $it.Year,
             Total: ($it.items.Amount | math sum | math abs | math round -p 0)
         }
     }
     | sort-by Year --reverse
     | group-by Auto
     | items {|auto, rows|
         $rows
         | reduce -f { Auto: $auto } {|it, acc|
             $acc | insert ($it.Year | into string) ($it.Total | into int)
         }
     }
 )

 let year_cols = (
     $pivot
     | columns
     | where {|c| $c != 'Auto' }
 )

# Total Row
 let total_row = (
     $year_cols
     | reduce -f { Auto: 'TOTAL' } {|col, acc|
         let sum = (
             $pivot
             | get -o $col
             | where {|v| $v != null }
             | math sum
         )
         $acc | insert $col $sum
     }
 )

 $pivot
 | append $total_row

Output

Auto	2026	2025	2024	2023	2022	2021	2020	2019	2018	2017	2016	2015
2014 Nissan Altima	105	8731	1655	990	60	28	3231
2012 Lexus RX 350	127	3235	2525	1469	72	440
2011 Honda Civic	84		422	22			1777
2004 Chevrolet Silverado		4336		659	72		166	5141	5145
2003 Jeep Liberty									1469	1488	2579	723
1999 Dodge Intrepid												1185
TOTAL	316	16302	4602	3140	204	468	5174	5141	6614	1488	2579	1908

Nushell code with Total Row and Total Column

# with total row and total column
let pivot = (
    open 'D:\work\Visidata\ko_auto.csv'
    | update Amount {|row|
        $row.Amount
        | into string
        | str replace ',' ''
        | into float
        | $in * -1
    }
    | insert Year    { $in.Date | into datetime | format date '%Y' | into int }
    | insert Yearmon { $in.Date | into datetime | format date '%Y%m' | into int }
    | insert Auto    { $in.Category | split row ':' | get 0 | str trim }
    | insert Work    { $in.Category | split row ':' | get 1? | str trim }
    | where Auto not-in ['Taxes']

    # yearly totals per Auto
    | group-by Auto Year --to-table
    | sort-by Auto
    | each {|it|
        {
            Auto:  $it.Auto
            Year:  $it.Year
            Total: ($it.items.Amount | math sum | math abs | math round -p 0)
        }
    }
    | sort-by Year --reverse

    # pivot years into columns + per-Auto Total
    | group-by Auto
    | items {|auto, rows|
        let row_total = (
            $rows
            | get Total
            | math sum
            | into int
        )

        $rows
        | reduce -f { Auto: $auto, Total: $row_total } {|it, acc|
            $acc | insert ($it.Year | into string) ($it.Total | into int)
        }
    }
)

# ---- TOTAL row (includes Total + all year columns) ----

let cols = (
    $pivot
    | columns
    | where {|c| $c != 'Auto' }
)

let total_row = (
    $cols
    | reduce -f { Auto: 'TOTAL' } {|col, acc|
        let sum = (
            $pivot
            | get -o $col
            | where {|v| $v != null }
            | math sum
        )
        $acc | insert $col $sum
    }
)

$pivot
| append $total_row

Output

Auto	Total	2026	2025	2024	2023	2022	2021	2020	2019	2018	2017	2016	2015
2014 Nissan Altima	14800	105	8731	1655	990	60	28	3231
2012 Lexus RX 350	7868	127	3235	2525	1469	72	440
2011 Honda Civic	2305	84		422	22			1777
2004 Chevrolet Silverado	15519		4336		659	72		166	5141	5145
2003 Jeep Liberty	6259									1469	1488	2579	723
1999 Dodge Intrepid	1185												1185
TOTAL	47936	316	16302	4602	3140	204	468	5174	5141	6614	1488	2579	1908