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 First for 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 = 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 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 Data
       for 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 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

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()
for rows in l:
    for row in rows:
        df = pd.concat([df, row])

In your case, it gives me the following output :

    0       1   2       3   4   5
0   A B 10  2   A B 10  3   1   AC
1   A B 104 3   A B 104 2   -1  AC
0   D B 126 3   D B 126 2   -1  EFG 1
1   D B 15  3   D B 15  2   -1  EFG 1
0   D B 544 2   D B 544 1   -1  EFG 11
1   D B 152 3   D B 152 2   -1  EFG 11
2   D B 682 3   D B 682 2   -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.