Pexe Pexe - 1 year ago 89
Python Question

Sort data in a list to columns for xlsx files

I have a list that looks something like this. (The data is from several xlsx files):

[['A B 10', 2, 'A B 10', 3, 1, AC], ['A B 104', 3, 'A B 104', 2, -1, 'AC']]
[['D B 126', 3, 'D B 126', 2, -1, 'EFG 1'], ['D B 15', 3, 'D B 15', 2, -1, 'EFG 1']
[]
[]
[['D B 544', 2, 'D B 544', 1, -1, 'EFG 11'], ['D B 152', 3, 'D B 152', 2, -1, 'EFG 11'], ['D B 682', 3, 'D B 682', 2, -1, 'EFG 11']


I want to put this information into a new xlsx file, but first I need to sort the data into rows and columns. I want all of the first strings in each sub-lists to be added to the first column, the numbers in the second column etc. So the columns is to be where the comma signs are. I also do not want the list to have sub lists, so everything should be in the same list. Something like this:

['A B 10', 2, 'A B 10', 3, 1, AC,
'A B 104', 3, 'A B 104', 2, -1, 'AC'
'D B 126', 3, 'D B 126', 2, -1, 'EFG 1'
'D B 15', 3, 'D B 15', 2, -1, 'EFG 1'
'D B 544', 2, 'D B 544', 1, -1, 'EFG 11'
'D B 152', 3, 'D B 152', 2, -1, 'EFG 11'
'D B 682', 3, 'D B 682', 2, -1, 'EFG 11']


This is how far I have come on my code:

import pandas as pd
from openpyxl import Workbook, load_workbook
import glob
from openpyxl.utils.dataframe import dataframe_to_rows

numbers = []
os.chdir(r'C:Myfolder')
files = glob.glob('*.xlsx')
print(files)
for file in files: #Getting the data from xlsx files and to the numbers-list
df = pd.read_excel(file)
m = (df.iloc[:,4] - df.iloc[:,1]) != 0
pos = [0,1,3,4,6,7]
numbers = (df.loc[m, df.columns[pos]].values.tolist())
print(numbers)
excel_input = load_workbook(rapp) #Going from using pandas and dataframes to working with openpyxl (Just because I am not that familiar with pandas).
ws = excel_input.active
for r in dataframe_to_rows(df, index=True, header=True):
ws.append(r)
else:
pass

col1 = [] #Creating open lists to put the data into columns
col2 = []
col4 = []
col5 = []
col7 = []
col8 = []

mainlist = []
try:
for row in numbers: #Putting the data into the columns lists
col1.append(ws.cell(row=row, column=1).value) #This is wrong, and is throwing the error
col2.append(ws.cell(row=row, column=2).value) #Wrong
col4.append(ws.cell(row=row, column=4).value) #Wrong
col5.append(ws.cell(row=row, column=5).value) #Wrong
col7.append(ws.cell(row=row, column=7).value) #Wrong
col8.append(ws.cell(row=row, column=8).value) #Wrong
except AttributeError:
logging.error('Something is wrong')
finally:
columns = zip(col1, col2, col4, col5, col7, col8) #Zipping the lists
for column in columns:
mainlist.append(column)



Traceback:
col1.append(ws.cell(row=row, column=1).value)
File "C:\Python3\lib\site-packages\openpyxl\worksheet\worksheet.py", line
306, in cell
if row < 1 or column < 1:
TypeError: unorderable types: list() < int()


Does anyone know how to do this without getting the error? I have commented in the code where the mistake is.

EDIT:

By using @stovfl's method I was able to put some of the data into the xlsx file, but as my list is a list of list, only the last list was added to my xlsx file.
I used this code:

from openpyxl import load_workbook

report = load_workbook(r"C:\Myworkbook.xlsx")
ws = report.create_sheet('My sheet')
for _list in numbers:
for row_data in _list:
ws.append([row_data])
print(row_data)
report.save(r"C\Myworkbook.xlsx")


The print:

A B 10
2
A B 10
3
1
AC


The output in the xlsx file:

Output Myworkbook.xlsx

The problem is only the last list is added, not the entire list of lists and I wanted the output to look like this:

How I need it to be Myworkbook.xlsx

Answer Source

Question: how to do this without getting the error?

# Data == List of List
data = [[['A B 10', 2, 'A B 10', 3, 1, 'AC'], ['A B 104', 3, 'A B 104', 2, -1, 'AC']],
[['D B 126', 3, 'D B 126', 2, -1, 'EFG 1'], ['D B 15', 3, 'D B 15', 2, -1, 'EFG 1']],
[],
[],
[['D B 544', 2, 'D B 544', 1, -1, 'EFG 11'], ['D B 152', 3, 'D B 152', 2, -1, 'EFG 11'], ['D B 682', 3, 'D B 682', 2, -1, 'EFG 11']],
]
  1. Solution writing your List of Lists using openpyxl, for instance

    newWorkbook = False
    newWorksheet = False
    
    if newWorkbook:
        from openpyxl import Workbook
        wb = Workbook()
        # Select First Worksheet
        ws = wb.worksheets[0]
    else:
        from openpyxl import load_workbook
        wb = load_workbook("mySortData.xlsx")
        if newWorksheet:
            # Create a New Worksheet in this Workbook
            ws = wb.create_chartsheet('Sheet 2')
        else:
            # Select a Worksheet by Name in this Workbook
            ws = wb['Sheet']
    
    for _list in data:
        # Append Row Values to Worksheet
        for row_data in _list:
            ws.append(row_data)
    
    wb.save("mySortData.xlsx")
    

    Output:
    enter image description here

  2. Solution writing your List of Lists direct to CSV, for instance:

    import csv
    # Write to File
    with open('Output.csv', 'w') as csv_file:
        writer = csv.writer(csv_file)
        for _list in data:
            for row_data in _list:
                writer.writerow(row_data)
    

    Qutput:

    A B 10,2,A B 10,3,1,AC
    A B 104,3,A B 104,2,-1,AC
    D B 126,3,D B 126,2,-1,EFG 1
    D B 15,3,D B 15,2,-1,EFG 1
    D B 544,2,D B 544,1,-1,EFG 11
    D B 152,3,D B 152,2,-1,EFG 11
    D B 682,3,D B 682,2,-1,EFG 11  
    

Tested with Python: 3.4.2 - openpyxl: 2.4.1

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download