CODEWITHSUNDEEP
pythonpython-2.7numpy
Ryflex
Ryflex

Reputation: 5779

Appending conditional formatting like hex colors to a list using numpy

In my original code the list that is modified isn't generated by a csv, but instead it comes from another function however for problems sake we'll generate the list from a csv file.

I'm sure someone must known how to edit the python script lower down to fix the problem

The problem/error/change in question is that I'm looking for a way to modify the python code so that any cells that are empty or contain NA are given a completely different hex color (not one that is in the gradient scale), however to explain the problem from the start.

Data stored in csv format for visual and problem purposes.

UniqueData1,UniqueTest1,13,13.39%,42.7%,Data
UniqueData1,UniqueTest1,14,82%,41.9%,Data
UniqueData1,UniqueTest1,18,29.39%,41%,Data
UniqueData2,UniqueTest2,22.3,44.4%,42.3%,Data
UniqueData2,UniqueTest2,4,44.2%,43.4%,Data
UniqueData2,UniqueTest2,4.1,49%,42.7%,Data
UniqueData3,UniqueTest1,4.45,49.5%,42.42%,Data
UniqueData3,UniqueTest1,4.29,20%,42.3%,Data
UniqueData3,UniqueTest1,4.22,25%,41.2%,Data

The data that needs to be modified is stored in list form as seen below

[['UniqueData1', 'UniqueTest1', '13', '13.39%', '42.7%', 'Data'], ['UniqueData1', 'UniqueTest1', '14', '82%', '41.9%', 'Data'], ['UniqueData1', 'UniqueTest1', '18', '29.39%', '41%', 'Data'], ['UniqueData2', 'UniqueTest2', '22.3', '44.4%', '42.3%', 'Data'], ['UniqueData2', 'UniqueTest2', '4', '44.2%', '43.4%', 'Data'], ['UniqueData2', 'UniqueTest2', '4.1', '49%', '42.7%', 'Data'], ['UniqueData3', 'UniqueTest1', '4.45', '49.5%', '42.42%', 'Data'], ['UniqueData3', 'UniqueTest1', '4.29', '20%', '42.3%', 'Data'], ['UniqueData3', 'UniqueTest1', '4.22', '25%', '41.2%', 'Data']]

The expected output is as seen below:

[['UniqueData1', 'UniqueTest1', '13', '13.39%', '42.70%', 'Data', '#F8696B', '#F8696B', '#63BE7B'], ['UniqueData1', 'UniqueTest1', '14', '82%', '41.90%', 'Data', '#FFEB84', '#63BE7B', '#FFEB84'], ['UniqueData1', 'UniqueTest1', '18', '29.39%', '41%', 'Data', '#63BE7B', '#FFEB84', '#F8696B'], ['UniqueData2', 'UniqueTest2', '22.3', '44.40%', '42.30%', 'Data', '#63BE7B', '#FFEB84', '#F8696B'], ['UniqueData2', 'UniqueTest2', '4', '44.20%', '43.40%', 'Data', '#F8696B', '#F8696B', '#63BE7B'], ['UniqueData2', 'UniqueTest2', '4.1', '49%', '42.70%', 'Data', '#FFEB84', '#63BE7B', '#FFEB84'], ['UniqueData3', 'UniqueTest1', '4.45', '49.50%', '42.42%', 'Data', '#63BE7B', '#63BE7B', '#63BE7B'], ['UniqueData3', 'UniqueTest1', '4.29', '20%', '42.30%', 'Data', '#FFEB84', '#F8696B', '#FFEB84'], ['UniqueData3', 'UniqueTest1', '4.22', '25%', '41.20%', 'Data', '#F8696B', '#FFEB84', '#F8696B']]

For visual purposes, if it was exported into a csv file it would look like:

UniqueData1,UniqueTest1,13,13.39%,42.70%,Data,#F8696B,#F8696B,#63BE7B
UniqueData1,UniqueTest1,14,82%,41.90%,Data,#FFEB84,#63BE7B,#FFEB84
UniqueData1,UniqueTest1,18,29.39%,41%,Data,#63BE7B,#FFEB84,#F8696B
UniqueData2,UniqueTest2,22.3,44.40%,42.30%,Data,#63BE7B,#FFEB84,#F8696B
UniqueData2,UniqueTest2,4,44.20%,43.40%,Data,#F8696B,#F8696B,#63BE7B
UniqueData2,UniqueTest2,4.1,49%,42.70%,Data,#FFEB84,#63BE7B,#FFEB84
UniqueData3,UniqueTest1,4.45,49.50%,42.42%,Data,#63BE7B,#63BE7B,#63BE7B
UniqueData3,UniqueTest1,4.29,20%,42.30%,Data,#FFEB84,#F8696B,#FFEB84
UniqueData3,UniqueTest1,4.22,25%,41.20%,Data,#F8696B,#FFEB84,#F8696B

As you can see they've all been given 3 hex colors, which in excel if you were to apply the colors to each cell would translate to:

https://i.sstatic.net/xQii1.png

The code that M4tini created to "solve" the above problem is:

import numpy as np

with open("testing.csv") as infile:
    lines = [line.rstrip().split(",") for line in infile]

def mapValues(values):
    values = [float(i.split('%')[0]) for i in values]
    colorMap = np.array(['#F8696B', '#F86E6C', '#F8736D', '#F8786E', '#F97E6F', '#F98370', '#F98871', '#FA8E72', '#FA9373', '#FA9874', '#FA9E75', '#FBA376', '#FBA877', '#FBAD78', '#FCB379', '#FCB87A', '#FCBD7B', '#FCC37C', '#FDC87D', '#FDCD7E', '#FDD37F', '#FED880', '#FEDD81', '#FEE382', '#FEE883', '#FCEB84', '#F6E984', '#F0E784', '#E9E583', '#E3E383', '#DCE182', '#D6E082', '#D0DE82', '#C9DC81', '#C3DA81', '#BDD881', '#B6D680', '#B0D580', '#AAD380', '#A3D17F', '#9DCF7F', '#96CD7E', '#90CB7E', '#8ACA7E', '#83C87D', '#7DC67D', '#77C47D', '#70C27C', '#6AC07C', '#63BE7B'])
    #colorMap = np.array(["low", "mid", "high"])
    values = np.asarray(values)
    _, bins = np.histogram(values, 49)
    mapped = np.digitize(values, bins)
    return list(colorMap[mapped - 1])

def mapAndAdd(finalList, tempList, v1,v2,v3):
    v1 = mapValues(v1)
    v2 = mapValues(v2)
    v3 = mapValues(v3)
    for i,j in enumerate(newList):
        finalList.append(j + [v1[i] , v2[i] , v3[i]])

uniqueDataSet = set()

finalList = []
for index, DataSet in enumerate(lines):
    if (DataSet[0] + DataSet[1]) in uniqueDataSet:
        v1.append(DataSet[2])
        v2.append(DataSet[3])
        v3.append(DataSet[4])

        newList.append(DataSet)
    else:
        if (index != 0):
            mapAndAdd(finalList, newList, v1,v2,v3)

        uniqueDataSet.add(DataSet[0] + DataSet[1])
        newList = [DataSet]
        v1 = [DataSet[2]]
        v2 = [DataSet[3]]
        v3 = [DataSet[4]]

mapAndAdd(finalList, newList, v1,v2,v3)

with open('testing_new.csv', 'wb') as new_csv:
    for line in finalList:
        new_csv.write(",".join(line) + "\r\n")

print finalList

Now if we use the csv data:

UniqueData1,UniqueTest1,1,1,1,Data
UniqueData1,UniqueTest1,2,2,2,Data
UniqueData1,UniqueTest1,3,3,3,Data
UniqueData1,UniqueTest1,4,NA,4,Data
UniqueData1,UniqueTest1,5,5,5,Data
UniqueData1,UniqueTest1,,6,6,Data
UniqueData1,UniqueTest1,7,7,7,Data
UniqueData1,UniqueTest1,8,8,8,Data
UniqueData1,UniqueTest1,9,9,9,Data

We get the following error, which is because of the blank cell on Row 6, Column 2 and the same goes for Row 4, Column 3 where we have a cell containing NA

Traceback (most recent call last):
  File "C:\testing.py", line 47, in <module>
    mapAndAdd(finalList, newList, v1,v2,v3)
  File "C:\testing.py", line 20, in mapAndAdd
    v1 = mapValues(v1)
  File "C:\testing.py", line 11, in mapValues
    values = [float(i.split('%')[0]) for i in values]
ValueError: could not convert string to float:

I'm looking for a way to modify the python code so that any cells that are empty or contain NA are given a completely different hex color not one that is in the gradient

For example the output should look like:

[['UniqueData1', 'UniqueTest1', '1', '1', '1', 'Data', '#F8696B', '#F8696B', '#F8696B'], ['UniqueData1', 'UniqueTest1', '2', '2', '2', 'Data', '#F98871', '#F98871', '#F98871'], ['UniqueData1', 'UniqueTest1', '3', '3', '3', 'Data', '#FBA877', '#FBA877', '#FBA877'], ['UniqueData1', 'UniqueTest1', '4', 'NA', '4', 'Data', '#FDC87D', '#808080', '#FDC87D'], ['UniqueData1', 'UniqueTest1', '5', '5', '5', 'Data', '#FEE883', '#FEE883', '#FEE883'], ['UniqueData1', 'UniqueTest1', '', '6', '6', 'Data', '#808080', '#DCE182', '#DCE182'], ['UniqueData1', 'UniqueTest1', '7', '7', '7', 'Data', '#B6D680', '#B6D680', '#B6D680'], ['UniqueData1', 'UniqueTest1', '8', '8', '8', 'Data', '#90CB7E', '#90CB7E', '#90CB7E'], ['UniqueData1', 'UniqueTest1', '9', '9', '9', 'Data', '#63BE7B', '#63BE7B', '#63BE7B']]

Which for visual purposes should look like:

UniqueData1,UniqueTest1,1,1,1,Data,#F8696B,#F8696B,#F8696B
UniqueData1,UniqueTest1,2,2,2,Data,#F98871,#F98871,#F98871
UniqueData1,UniqueTest1,3,3,3,Data,#FBA877,#FBA877,#FBA877
UniqueData1,UniqueTest1,4,NA,4,Data,#FDC87D,#808080,#FDC87D
UniqueData1,UniqueTest1,5,5,5,Data,#FEE883,#FEE883,#FEE883
UniqueData1,UniqueTest1,,6,6,Data,#808080,#DCE182,#DCE182
UniqueData1,UniqueTest1,7,7,7,Data,#B6D680,#B6D680,#B6D680
UniqueData1,UniqueTest1,8,8,8,Data,#90CB7E,#90CB7E,#90CB7E
UniqueData1,UniqueTest1,9,9,9,Data,#63BE7B,#63BE7B,#63BE7B

Upvotes: 0

Views: 237

Answers (1)

M4rtini
M4rtini

Reputation: 13549

Here you go, another attempt to "solve" it.

def mapValues(values):
    nValues = np.asarray(values, dtype="|S8")
    mask = nValues != 'NA'
    maskedValues = [float(i.split('%')[0]) for i in nValues[mask]]
    colorMap = np.array(['#F8696B', '#F86E6C', '#F8736D', '#F8786E', '#F97E6F', '#F98370', '#F98871', '#FA8E72', '#FA9373', '#FA9874', '#FA9E75', '#FBA376', '#FBA877', '#FBAD78', '#FCB379', '#FCB87A', '#FCBD7B', '#FCC37C', '#FDC87D', '#FDCD7E', '#FDD37F', '#FED880', '#FEDD81', '#FEE382', '#FEE883', '#FCEB84', '#F6E984', '#F0E784', '#E9E583', '#E3E383', '#DCE182', '#D6E082', '#D0DE82', '#C9DC81', '#C3DA81', '#BDD881', '#B6D680', '#B0D580', '#AAD380', '#A3D17F', '#9DCF7F', '#96CD7E', '#90CB7E', '#8ACA7E', '#83C87D', '#7DC67D', '#77C47D', '#70C27C', '#6AC07C', '#63BE7B'])
    _, bins = np.histogram(maskedValues, 49)
    mapped = np.digitize(maskedValues, bins)
    nValues[mask] = colorMap[mapped - 1]
    nValues[~mask] = "NA color"
    return nValues.tolist()

Upvotes: 1

Related Questions