Combine csv values and output to csv

user_14665401 0
Viewed 56

I'm trying to read a csv file and combine the duplicate values then output the values into a csv again.

  • Iterate through each line in the text file. The first line contains headers, so should be skipped.
  • Separate the three values found in each line. Each line contains the product name, quantity sold, and unit price (the price of a single product), separated by a tab character.
  • Keep a running total for the quantity sold of each product; for example, the total quantity sold for ‘product b’ is 12.
  • Keep a record of the unit price of each product.
  • Write the result to the sales-report.csv; the summary should include the name of each product, the sales volume (total quantity sold), and the sales revenue (total quantity sold * by the product price).

What I intend.

Input Data:

product name,quantity,unit price
product c,2,22.5
product a,1,10
product b,5,19.7
product a,3,10
product f,1,45.9
product d,4,34.5
product e,1,9.99
product c,3,22.5
product d,2,34.5
product e,4,9.99
product f,5,45.9
product b,7,19.7

Output Data:

product name,sales volume,sales revenue
product c,5,112.5
product a,4,40
product b,12,236.4
product f,6,275.4
product d,6,207
product e,5,49.95

This is what I have so far, I've looked around and it isn't entirely clear how I'm supposed to perform list comprehension and combine values. When I looked for an answer, it was mostly more complicated than it probably needs to be, it is relatively simple...

    record = []
    with open("items.csv", "r") as f:
        next(f)
        for values in f:
            split = values.rstrip().split(',')
            record.append(split)
        print(record)
slice csv list python
2 Answers

You can use pandas for this:

import pandas as pd

df = pd.read_csv('path/to/file')

Then calculate sales revenue, groupby and sum

df = df.assign(sales_revenue=lambda x: x['quantity'] * x['unit price']).groupby('product name').sum().reset_index()


  product name  quantity  sales_revenue
0    product a         4       20.00
1    product b        12       39.40
2    product c         5       45.00
3    product d         6       69.00
4    product e         5       19.98
5    product f         6       91.80

You can save the result to a csv file

df.to_csv('new_file_name.csv', index=False)

user_60319950

pandas is the way to go with the problem. If you don't already use it, it aggregates operations across entire tables so you don't have to iterate yourself. Notice that entire columns can be multiplied in a single step. groupby will group the dataframe by each product and then its easy to sum.

import pandas as pd

df = pd.read_csv("f.csv")
df["sales revenue"] = df["quantity"] * df["unit price"]
del df["unit price"]
outdf = df.groupby("product name").sum()
outdf.rename(columns={"quantity": "sales volume"})
outdf.to_csv("f-out.csv")
user_6420700
Related