Skip to content Skip to sidebar Skip to footer

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:
    enter image description here

  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.


Post a Comment for "Sort Data In A List To Columns For Xlsx Files"