Python and pandas: Aggregate lines form Excel sheet

[Pedroski55]

Yes, I found that the sample excel file which you posted had an empty row at the bottom. The first sample XL had maxRow 31, but only 30 rows had entries, if I remember correctly.

I deleted row 31.

But now, that is not necessary. The code below will just ignore any rows which do not have either deposit, withdraw or order in column 2

for rowNum in range(2, smaxRow + 1):
    if not sourceFile[mysheet].cell(row=rowNum, column=2).value in type_dict.keys():
        continue

About inserting zeroes: None values can cause problems when you are trying to add. You can't add an integer and None.

In your example Excel, row 2 is a deposit, so there is no entry in column soldQuantity, column 6:

sourceFile[mysheet].cell(row=2, column=6).value == None
True

This will be true for all deposit rows. But I thought it is easier to pick up all the values of all the rows. regardless of type.

So, if we first give all numerical cells with None values the value zero, that will simply make the addition simpler! Adding zero changes nothing! Take a look at my bank account!

Just as an example, this below would set all None values in columns 4, 6 and 8 to zero, 0

# set the paths
path2file = '/home/pedro/myPython/openpyxl/xlsx_files/Beispieltabelle.xlsx'
# get the source XL
sourceFile = openpyxl.load_workbook(path2file)
sourceFilesheets = sourceFile.sheetnames
# show the available sheets
for sheet in sourceFilesheets:
    print('These are the sheets:', sheet)
# get the sheet you want to process
mysheet = input('What sheet do you want to process? Copy and paste the sheet name ... ')
# get the maximum row and column numbers for later loops
smaxRow = sourceFile[mysheet].max_row
smaxCol = sourceFile[mysheet].max_column
# if, just as an example columns 4, 6 and 8 can contain a number, set them all to zero first
for rowNum in range(2, smaxRow + 1):
    for colNum in range(4, 10, 2):
        if sourceFile[sheet].cell(row=rowNum, column=colNum).value == None:
            sourceFile[sheet].cell(row=rowNum, column=colNum, value=0)

Of course, you can avoid None values with an if-clause too!

Hab grad Besuch aus Deutschland, nicht so viel Zeit diese Woche, but if you are happy with the pattern of collecting the various patterns for deposit, withdrawal and order, I will "amalgamate" the rows to a very small XL.