0

I am sourcing chart data from an excel spreadsheet, by using Openpyxl.

I need to be able to sort this data largest to smallest without it getting jumbled. Sometimes there are multiple series in the charts.

Is there a method by which this can be accomplished?

If it cannot be sorted before being dropped into the plot, is there a means by which it can be sorted afterward? I imagine it would need to be located, then sorted. This would need to happen for every chart in the document.

emmartin
  • 105
  • 12
  • 1
    Can you provide an example? – Charlie Clark Jul 02 '19 at 13:17
  • In python-pptx, ChartData() accepts a list of categories and any number of lists of series' values. So the Category List might be (Lions, Seals, Dolphins), and the series lists might be (3, 4, 1) and (2, 6, 5). I need to be able to sort by the first series, largest to smallest, but since ChartData() takes everything in separate lists, just sorting one would jumble the data. I wonder if there's a way to zip it into a larger list or a dictionary, sort it, then unzip it and feed the sorted lists in ChartData(), but any other method would be great too. – emmartin Jul 08 '19 at 13:02
  • python-pptx manipulates the XML directly. openpyxl provides a different API. – Charlie Clark Jul 08 '19 at 14:17
  • I'm aware. I'm using both. Openpyxl to read the data from a spreadsheet and drop it into a list which is used by Python-pptx to create charts. I'm looking for a method, be it using a feature of either of those or some other code, to sort the data after it's been read by Openpyxl. – emmartin Jul 09 '19 at 15:06
  • Well, then that is a discussion for the openpyxl mailing list. – Charlie Clark Jul 10 '19 at 08:46
  • I don't believe that it is. I don't want to alter the spreadsheet in any way, so ideally the sorting would be done after the data is collected and dropped into lists. I hypothesize that if I pull the lists apart, and make an ordered dictionary with the first value as a key, I can then re-create the lists. I will try this and report back. – emmartin Jul 10 '19 at 14:25

1 Answers1

0

Here's what I did to solve this problem, and it seems to be working currently. If anyone has suggestions to make this process more streamlined, I'm all ears!

        for cat in data_collect['categories']: #creates new lists by category
            new_list = []
            for key in data_collect:
                if key != 'categories':
                    new_list.append(data_collect[key][indexcounter])  
                else:
                    pass
            new_list.insert(1, cat)
            indexcounter += 1
            data_sort.append(new_list)

        data_sort.sort() #sorts list by first column
        cat_list_sorted = []

        for lst in data_sort: #removes sorted category list and drops it into the chart
            cat_list_sorted.append(lst[1])
            del lst[1]
        chart_data.categories = cat_list_sorted

        indexcounter = 0 #pulls data back apart and creates new series lists. Drops each series into ChartData()
        while indexcounter < len(series_name_list):
            series_sorted = []
            for lst in data_sort:
                series_sorted.append(lst[indexcounter])
            series_name = series_name_list[indexcounter]
            chart_data.add_series(series_name, series_sorted, '0%')
            indexcounter += 1```
emmartin
  • 105
  • 12