Sort Data In A List To Columns For Xlsx Files
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?
Solution writing your List of Lists using
openpyxl
, for instancenewWorkbook = 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")
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"