Conversion of the "PRO Prisundersökning" from XLSX to CSV, JSON,NDJSON

pro-prisundersokning.go

package main

import (
	"encoding/csv"
	"encoding/json"
	"flag"
	"fmt"
	"io"
	"net/http"
	"os"
	"strconv"
	"strings"
	"time"

	"github.com/xuri/excelize/v2"
)

const (
	defaultRawurl = "https://pro.se/download/18.8d3d3b4187083b364113f70/1679655215923/PRO%20prisunders%C3%B6kning%202022%20Butikslista%20hela%20landet_20230324.xlsx"
	defaultFormat = "json"
)

func main() {
	if err := run(os.Args, os.Stdout); err != nil {
		fmt.Fprintf(os.Stderr, "%s\n", err)
		os.Exit(1)
	}
}

func run(args []string, stdout io.Writer) error {
	rawurl, format, err := parseArgs(args)
	if err != nil {
		return err
	}

	client := &http.Client{Timeout: 10 * time.Second}

	f, err := getRemoteFile(rawurl, client)
	if err != nil {
		return err
	}

	rows, err := f.Rows("Alla")
	if err != nil {
		return err
	}

	var records [][]string

	for rows.Next() {
		row, err := rows.Columns()
		if err != nil {
			return err
		}

		records = append(records, row)
	}

	enc := json.NewEncoder(stdout)

	switch format {
	case "csv":
		csv.NewWriter(stdout).WriteAll(records)
	case "json":
		enc.SetIndent("", " ")
		enc.Encode(convertToButiker(records))
	case "ndjson":
		for _, butik := range convertToButiker(records) {
			enc.Encode(butik)
		}
	default:
		return fmt.Errorf("unknown format")
	}

	return nil
}

type butik struct {
	Butiksnamn string  `json:"butiksnamn"`
	Distrikt   string  `json:"distrikt"`
	Typ        string  `json:"typ"`
	AntalVaror int     `json:"antal_varor"`
	Total      float64 `json:"total"`
	Varor      []vara  `json:"varor"`
}

func convertToButiker(records [][]string) []butik {
	var butiker []butik

	if len(records) == 0 {
		return nil
	}

	for _, row := range records[1:] {
		if len(row) < 5 {
			continue
		}

		antalVaror, err := strconv.Atoi(row[3])
		if err != nil {
			continue
		}

		total, err := strconv.ParseFloat(row[4], 64)
		if err != nil {
			continue
		}

		varor, err := parseVaror(records[0][5:], row[5:])
		if err != nil {
			continue
		}

		butiker = append(butiker, butik{
			Butiksnamn: row[0],
			Distrikt:   row[1],
			Typ:        row[2],
			AntalVaror: antalVaror,
			Total:      total,
			Varor:      varor,
		})
	}

	return butiker
}

type vara struct {
	Namn string  `json:"namn"`
	Pris float64 `json:"pris"`
}

func parseVaror(headings, columns []string) ([]vara, error) {
	var varor []vara

	for i, v := range columns {
		_, namn, found := strings.Cut(headings[i], ". ")
		if !found {
			return nil, fmt.Errorf("unexpected name")
		}

		pris, err := strconv.ParseFloat(v, 64)
		if err != nil {
			return nil, err
		}

		varor = append(varor, vara{
			Namn: namn,
			Pris: pris,
		})
	}

	return varor, nil
}

func getRemoteFile(rawurl string, client *http.Client) (*excelize.File, error) {
	resp, err := client.Get(rawurl)
	if err != nil {
		return nil, err
	}
	defer resp.Body.Close()

	return excelize.OpenReader(resp.Body)
}

func parseArgs(args []string) (rawurl, format string, err error) {
	flags := flag.NewFlagSet(args[0], flag.ExitOnError)

	flags.StringVar(&rawurl, "rawurl", defaultRawurl, "The URL to the PRO Prisundersökning .xlsx to be parsed")
	flags.StringVar(&format, "format", defaultFormat, "The format to use for the output (json/ndjson/csv)")

	if err := flags.Parse(args[1:]); err != nil {
		return "", "", err
	}

	return rawurl, format, nil
}

package main

import (
	"encoding/json"
	"fmt"
	"os"
	"strings"

	_ "embed"

	"github.com/wcharczuk/go-chart/v2"
	"github.com/wcharczuk/go-chart/v2/drawing"
)

func main() {
	var butiker []butik

	if err := json.Unmarshal(data, &butiker); err != nil {
		panic(err)
	}

	totals := make([]float64, len(butiker))
	priser := make([]float64, len(butiker))

	for n := 0; n < 36; n++ {
		var varorNamn string

		for i, butik := range butiker {
			totals[i] = butik.Total
			priser[i] = butik.Varor[n].Pris
			varorNamn = butik.Varor[n].Namn
		}

		mainSeries := chart.ContinuousSeries{
			Style: chart.Style{
				StrokeColor: chart.ColorRed,
				StrokeWidth: 11,
				DotColor:    chart.ColorBlack,
				DotWidth:    2,
			},
			XValues: chart.LinearRange(1, float64(len(totals))),
			YValues: chart.ValueSequence(totals...).Values(),
		}

		priserSeries := chart.ContinuousSeries{
			YAxis: chart.YAxisSecondary,
			Style: chart.Style{
				StrokeColor: drawing.ColorFromHex("AAA"),
				StrokeWidth: 3,
				DotColor:    chart.ColorBlack,
				DotWidth:    2,
			},
			XValues: chart.LinearRange(1, float64(len(totals))),
			YValues: chart.ValueSequence(priser...).Values(),
		}

		graph := chart.Chart{
			Title: "PRO Prisundersökning 2022 | Totalbelopp vs " + varorNamn,
			TitleStyle: chart.Style{
				FontSize: 12,
			},
			DPI:    200,
			Width:  3072,
			Height: 1024,
			XAxis: chart.XAxis{
				Style: chart.Style{
					Hidden: true,
				},
			},
			YAxis: chart.YAxis{
				ValueFormatter: func(v interface{}) string {
					return chart.FloatValueFormatterWithFormat(v, "%.0f kr")
				},
			},
			YAxisSecondary: chart.YAxis{
				ValueFormatter: func(v interface{}) string {
					return chart.FloatValueFormatterWithFormat(v, "%.0f kr")
				},
			},
			Series: []chart.Series{
				priserSeries,
				mainSeries,
			},
		}

		fn := strings.ToLower(
			fmt.Sprintf("pro-prisundersokning-total-vs-%s.png",
				strings.ReplaceAll(varorNamn, " ", "-"),
			),
		)

		f, err := os.Create(fn)
		if err != nil {
			panic(err)
		}
		defer f.Close()

		if err := graph.Render(chart.PNG, f); err != nil {
			panic(err)
		}
	}
}

//go:embed data.json
var data []byte

type butik struct {
	Butiksnamn string  `json:"butiksnamn"`
	Distrikt   string  `json:"distrikt"`
	Typ        string  `json:"typ"`
	AntalVaror int     `json:"antal_varor"`
	Total      float64 `json:"total"`
	Varor      []vara  `json:"varor"`
}

type vara struct {
	Namn string  `json:"namn"`
	Pris float64 `json:"pris"`
}

Data from the 2023 report

	defaultRawurl = "https://pro.se/download/18.4d17fc3b18d2a6eee111b331/1706085711958/Butikslista_hela%20landet_2023.xlsx"