[FIXED] How to read comma separated string in one cell using Python

Issue

I have a project wherein you need to read data from an excel file. I use openpyxl to read the said file. I tried reading the data as string first before converting it to an integer; however, error is occurring because of, I think, numbers in one cell separated by comma. I am trying to do a nested list but I still new in Python.

My code looks like this:

# storing S
S_follow = []
for row in range(2, max_row+1):
  if (sheet.cell(row,3).value is not None):
  S_follow.append(sheet.cell(row, 3).value);
# to convert the list from string to int, nested list
for i in range(0, len(S_follow)):
S_follow[i] = int(S_follow[i])
print(S_follow)

The data I a trying to read is: ['2,3', 4, '5,6', 8, 7, 9, 8, 9, 3, 11, 0]

hoping for your help

Solution

When you're about to convert the values to integers in the loop on the second-last line of your script, you can check if each value is an integer or string and if it is a string, just split it, convert the split values to integers and push them to a temporary list called say, strVal and then append that temp list to a new list called, say S_follow_int. But if the value is not a string, then just append them to S_follow_int without doing anything.

data= ['2,3', 4, '5,6', 8, 7, 9, 8, 9, 3, 11, 0]
S_follow = []
S_follow_int = []

for row in range(0, len(data)):
  if (sheet.cell(row,3).value is not None):
      S_follow.append(sheet.cell(row, 3).value);
      
# to convert the list from string to int, nested list
for i in range(0, len(S_follow)):
    
    #if the current value is a string, split it, convert the values to integers, put them on a temp list called strVal and then append it to S_follow_int
    if type(S_follow[i]) is str:
        x = S_follow[i].split(',')
        strVal = []
        for y in x:
            strVal.append(int(y))
        S_follow_int.append(strVal)
        
    #else if it is already an integer, just append it to S_follow_int without doing anything

    else:
        S_follow_int.append(S_follow[i])
    
print(S_follow_int)

However, I would recommend that you check the datatype(str/int) of each value in the initial loop that you used to retrieved data from the excel file itself rather than pushing all values to S_follow and then convert the type afterwards like this:

#simplified representation of the logic you can use for your script

data = ['2,3', 4, '5,6', 8, 7, 9, 8, 9, 3, 11, 0]
x = []

for dat in data:
    if dat is not None:
        if type(dat) is str:
            y = dat.split(',')
            strVal = []
            for z in y:
                strVal.append(int(z))
            x.append(strVal)
        else:
            x.append(dat)
print(x)

Answered By - AndrewL64

Answer Checked By - Cary Denson (PHPFixing Admin)