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

Solution 1:

Question: ... only the last list was added to my xlsx file

The Firstfor loop is your for file in files:. I updated my Code below to this.

Question: how to do this without getting the error?

1. Solution writing your List of Lists using openpyxl, for instance

   newWorkbook = False
   newWorksheet = Falseif 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 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())
   
       # numbers is a List[Row Data] of List[Columns]# Iterate List of Row Datafor row_data in numbers:
           ws.append(row_data)
   
   wb.save("mySortData.xlsx")
   

   Output:

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

   # 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']],
   ]
   
   import csv
   # Write to Filewithopen('Output.csv', 'w') as csv_file:
       writer = csv.writer(csv_file)
       for _listin data:
           for row_data in _list:
               writer.writerow(row_data)
   

   Qutput:

   AB10,2,AB10,3,1,AC
   AB104,3,AB104,2,-1,AC
   D B126,3,D B126,2,-1,EFG 1
   D B15,3,D B15,2,-1,EFG 1
   D B544,2,D B544,1,-1,EFG 11
   D B152,3,D B152,2,-1,EFG 11
   D B682,3,D B682,2,-1,EFG 11

Tested with Python: 3.4.2 - openpyxl: 2.4.1

Solution 2:

I don't know if I understood well but if you have a list such as :

l = [rows, rows, rows, ...]

In order to create your dataframe, you could just iterate over each element of your list l such as :

df = pd.DataFrame()
forrowsin l:
    forrowinrows:
        df = pd.concat([df, row])

In your case, it gives me the following output :

0123450AB102AB1031   AC
1AB1043AB1042   -1  AC
0   D B1263   D B1262   -1  EFG 11   D B153   D B152   -1  EFG 10   D B5442   D B5441   -1  EFG 111   D B1523   D B1522   -1  EFG 112   D B6823   D B6822   -1  EFG 11

Solution 3:

Looks like you can get the data into your Excel file fine. So why don't use you use something like:

col1 = [cell.value for cell in ws['A']]

or

ws.iter_cols(min_col=x, max_col=y)

If this isn't what you want then please rephrase the question to point out where the problem is.