File parsing using commas Python
Question: I am trying to write a program in python in which we have to add the numbers
from different categories and sub-categories. The program is about a farmer's
annual sale of produce from his farm. The text file from where we have to read
from has 4 categories. The first category is the type of product for example
Vegetables, Fruits, condiments. The second category tells us about the type of
product we have, for example Potatoes, Apples, Hot Sauce. The third category
tells us about the sales in 2014 and the fourth category tells us about the
sales in 2015. In this program, we only have to calculate the totals from the
2015 numbers. The 2014 numbers are present in the text file but are
irrelevant.

Here is how the text file looks like

    
    
    PRODUCT,CATEGORY,2014 Sales,2015 Sales
    Vegetables,Potatoes,4455,5644
    Vegetables,Tomatoes,5544,6547
    Vegetables,Peas,987,1236
    Vegetables,Carrots,7877,8766
    Vegetables,Broccoli,5564,3498
    Fruits,Apples,398,4233
    Fruits,Grapes,1099,1234
    Fruits,Pear,2342,3219
    Fruits,Bananas,998,1235
    Fruits,Peaches,1678,1875
    Condiments,Peanut Butter,3500,3902
    Condiments,Hot Sauce,1234,1560
    Condiments,Jelly,346,544
    Condiments,Spread,2334,5644
    Condiments,Ketchup,3321,3655
    Condiments,Olive Oil,3211,2344
    

What we are looking to do is to add the sales for 2015 by products and then
the total sales for everything in 2015.

The output should look something like this in the written text file:

> Total sales for Vegetables in 2015 : {Insert total number here}
>
> Total sales for Fruits in 2015 : {Insert total number here}
>
> Total sales for Condiments in 2015 : {Insert total number here}
>
> * * *
>
> Total sales for the farmer in 2015: {Insert total for all the products sold
> in 2015}

Along with that, it should also print the grand total on the Python run screen
in the IDE along with the text file:

> Total sales for the farmer in 2015: {Insert total for all the products sold
> in 2015}

Here is my code. It works but prints a weird first line in the output. Also, I
would rather not use lists. Is there any other way? Please no CSV as we are
directed to use the data as a text file.

    
    
    readFile = open("Products.txt", "r")
    reportfile = open("report.txt", "w")
    line = readFile.readline()
    
    totalSum = 0
    container = []
    product = ()
    sum=0
    for line in readFile:
        line=line.strip()
        line=line.split(",")
    
        if line[0] not in container:
            print(product,sum, file=reportfile)
    
            product = line[0]
            totalSum += int(line[3])
            sum = 0
            sum += int(line[3])
            container.append(product)
        elif product == line[0]:
            totalSum += int(line[3])
            sum += int(line[3])
    
    print(totalSum, file=reportfile)
    


Answer: These kinds of tasks are perfect for Pandas:

    
    
    import pandas
    
    df = pandas.read_csv('Products.txt')
    df = df.groupby('PRODUCT').sum()
    df.ix['Total'] = df.sum()
    df
    

[![groupby
result](http://i.stack.imgur.com/93ajJ.png)](http://i.stack.imgur.com/93ajJ.png)

