paul-d-ray/Polars_Xan_Sum_Filter.md

## Polars_Xan_Sum_Filter.md

      
    Raw
  

              Polars_Xan_Sum_Filter.md
            
          
    Determine how much was spent on Groceries

I exported the data from MS Money, for all years (2010 + 2025)
the details on Grocies.
I wanted to see how to sum the data by year for one grocey store.
I had done this in Visidata, and I knew both Nushell with the Polars plugin and Xan
could do it, so I decided how to get the syntax correct in both.
This is a very small dataset, only 1054 records.
Nushell and Polars Code

 open ../Visidata/groceries.csv
| polars into-df
| polars filter ((polars col Payee) == "Ingles")
| polars group-by Year
| polars agg {
    total: (polars col Amount | polars sum)
}
| polars sort-by Year
| polars collect
| polars into-nu
Nushell and Xan Code

^xan filter 'Payee eq "Ingles"' ../Visidata/groceries.csv 
| ^xan groupby Year  'sum(Amount) as Total'  
|  ^xan sort -N -s Year 
| from csv
Output for both

╭────┬──────┬─────────╮
│  # │ Year │  total  │
├────┼──────┼─────────┤
│  0 │ 2010 │ 2786.83 │
│  1 │ 2011 │ 1700.92 │
│  2 │ 2012 │ 2784.06 │
│  3 │ 2013 │ 3024.38 │
│  4 │ 2014 │ 5897.92 │
│  5 │ 2015 │ 5789.60 │
│  6 │ 2016 │ 6676.15 │
│  7 │ 2017 │ 5572.32 │
│  8 │ 2018 │ 3881.39 │
│  9 │ 2019 │ 4990.06 │
│ 10 │ 2020 │ 4569.46 │
│ 11 │ 2021 │ 5919.57 │
│ 12 │ 2022 │ 8704.19 │
│ 13 │ 2023 │ 7958.63 │
│ 14 │ 2024 │ 7457.54 │
│ 15 │ 2025 │ 6732.85 │
├────┼──────┼─────────┤
│  # │ Year │  total  │
╰────┴──────┴─────────╯

Get Year over Year change

Nushell and Polars Code

Help from Grok, minor syntax changes edits
 open ../Visidata/groceries.csv
| polars into-df
| polars filter ((polars col Payee) == "Ingles")
| polars group-by Year
| polars agg [(polars col Amount | polars sum | polars as total)]
| polars sort-by Year
| polars with-column [
    (polars col total | polars shift 1 | polars as prev_year),
    ((polars col total) - (polars col total | polars shift 1) | polars as change),
    (((polars col total) / (polars col total | polars shift 1) - 1) * 100
     | polars as "%_change")
]
| polars collect
| polars into-nu
| table -e
Output

╭────┬──────┬─────────┬───────────┬──────────┬──────────╮
│  # │ Year │  total  │ prev_year │  change  │ %_change │
├────┼──────┼─────────┼───────────┼──────────┼──────────┤
│  0 │ 2010 │ 2786.83 │           │          │          │
│  1 │ 2011 │ 1700.92 │   2786.83 │ -1085.91 │   -38.97 │
│  2 │ 2012 │ 2784.06 │   1700.92 │  1083.14 │    63.68 │
│  3 │ 2013 │ 3024.38 │   2784.06 │   240.32 │     8.63 │
│  4 │ 2014 │ 5897.92 │   3024.38 │  2873.54 │    95.01 │
│  5 │ 2015 │ 5789.60 │   5897.92 │  -108.32 │    -1.84 │
│  6 │ 2016 │ 6676.15 │   5789.60 │   886.55 │    15.31 │
│  7 │ 2017 │ 5572.32 │   6676.15 │ -1103.83 │   -16.53 │
│  8 │ 2018 │ 3881.39 │   5572.32 │ -1690.93 │   -30.35 │
│  9 │ 2019 │ 4990.06 │   3881.39 │  1108.67 │    28.56 │
│ 10 │ 2020 │ 4569.46 │   4990.06 │  -420.60 │    -8.43 │
│ 11 │ 2021 │ 5919.57 │   4569.46 │  1350.11 │    29.55 │
│ 12 │ 2022 │ 8704.19 │   5919.57 │  2784.62 │    47.04 │
│ 13 │ 2023 │ 7958.63 │   8704.19 │  -745.56 │    -8.57 │
│ 14 │ 2024 │ 7457.54 │   7958.63 │  -501.09 │    -6.30 │
│ 15 │ 2025 │ 6732.85 │   7457.54 │  -724.69 │    -9.72 │
├────┼──────┼─────────┼───────────┼──────────┼──────────┤
│  # │ Year │  total  │ prev_year │  change  │ %_change │
╰────┴──────┴─────────┴───────────┴──────────┴──────────╯
No results found