Reading CSV file and statistics Python without using Pandas

user_7480356 0
Viewed 147

I am new to python and statistics and I am trying to get the following from a CSV file without using pandas:

Average Price

Get the average quantity of sales for the France across all products.

Get the total amount of money spent in the United Kingdom on product number 84879 on 1/12/2018.

Get the total amount of money spent on product number 84879 on 3/12/2018 in Belgium.

Graph the quantities of item number 84879 sold in the UK on 1/12/2018 against the same item sold in Belgium on 3/12/2018.

I can do these with the help of Pandas but finding it difficult to do it without pandas

I have tried

import statistics as st
import csv

df = []

file = open('CA2Dataset.csv')
type(file)

reader = csv.reader(file)

header = []
data = []

1

header = next(reader)

2

header

3

​

['TransactionNo',
 'Date',
 'ProductNo',
 'ProductName',
 'Price',
 'Quantity',
 'CustomerNo',
 'Country']

data = (reader)
data
for row in data:
    print(row)`

Here is the content of the csv file:

['536365', '01/12/2018', '85123A', 'Cream Hanging Heart T-Light Holder', '1.88', '6', '17850', 'United Kingdom']
['536365', '01/12/2018', '71053', 'White Moroccan Metal Lantern', '2.01', '6', '17850', 'United Kingdom']
['536365', '01/12/2018', '84406B', 'Cream Cupid Hearts Coat Hanger', '1.91', '8', '17850', 'United Kingdom']
['536365', '01/12/2018', '84029G', 'Knitted Union Flag Hot Water Bottle', '2.01', '6', '17850', 'United Kingdom']
['536365', '01/12/2018', '84029E', 'Red Woolly Hottie White Heart', '0.00', '6', '17850', 'United Kingdom']
['536365', '01/12/2018', '22752', 'Set 7 Babushka Nesting Boxes', '2.65', '2', '17850', 'United Kingdom']
['536365', '01/12/2018', '21730', 'Glass Star Frosted T-Light Holder', '2.14', '6', '17850', 'United Kingdom']
['536366', '01/12/2018', '22633', 'Hand Warmer Union Jack', '1.78', '6', '17850', 'United Kingdom']
['536366', '01/12/2018', '22632', 'Hand Warmer Red Retrospot', '1.78', '6', '17850', 'United Kingdom']
['536367', '01/12/2018', '84879', 'Assorted Colour Bird Ornament', '1.75', '32', '13047', 'United Kingdom']
['536367', '01/12/2018', '22745', "Poppy's Playhouse Bedroom", '0.00', '6', '13047', 'United Kingdom']
['536367', '01/12/2018', '22748', "Poppy's Playhouse Kitchen", '1.81', '6', '13047', 'United Kingdom']
['536367', '01/12/2018', '22749', 'Feltcraft Princess Charlotte Doll', '2.06', '8', '13047', 'United Kingdom']
['536367', '01/12/2018', '22310', 'Ivory Knitted Mug Cosy', '1.75', '6', '13047', 'United Kingdom']
['536367', '01/12/2018', '84969', 'Box Of 6 Assorted Colour Teaspoons', '2.14', '6', '13047', 'United Kingdom']
['536367', '01/12/2018', '22623', 'Box Of Vintage Jigsaw Blocks', '2.24', '3', '13047', 'United Kingdom']
['536367', '01/12/2018', '22622', 'Box Of Vintage Alphabet Blocks', '2.99', '2', '13047', 'United Kingdom']
['536367', '01/12/2018', '21754', 'Home Building Block Word', '2.39', '3', '13047', 'United Kingdom']
['536367', '01/12/2018', '21755', 'Love Building Block Word', '2.39', '3', '13047', 'United Kingdom']
['536367', '01/12/2018', '21777', 'Recipe Box With Metal Heart', '2.69', '4', '13047', 'United Kingdom']
['536367', '01/12/2018', '48187', 'Doormat New England', '2.69', '4', '13047', 'United Kingdom']
['536368', '01/12/2018', '22960', 'Jam Making Set With Jars', '2.14', '6', '13047', 'United Kingdom']
['536368', '01/12/2018', '22913', 'Red Coat Rack Paris Fashion', '2.24', '3', '13047', 'United Kingdom']
['536368', '01/12/2018', '22912', 'Yellow Coat Rack Paris Fashion', '2.24', '3', '13047', 'United Kingdom']
['536368', '01/12/2018', '22914', 'Blue Coat Rack Paris Fashion', '2.24', '3', '13047', 'United Kingdom']
['536369', '01/12/2018', '21756', 'Bath Building Block Word', '2.39', '3', '13047', 'United Kingdom']
['536370', '01/12/2018', '22728', 'Alarm Clock Bakelike Pink', '2.06', '24', '12583', 'France']
['536370', '01/12/2018', '22727', 'Alarm Clock Bakelike Red', '2.06', '24', '12583', 'France']
['536370', '01/12/2018', '22726', 'Alarm Clock Bakelike Green', '2.06', '12', '12583', 'France']
['536370', '01/12/2018', '21724', 'Panda And Bunnies Sticker Sheet', '1.63', '12', '12583', 'France']
['536370', '01/12/2018', '21883', 'Stars Gift Tape', '1.6', '24', '12583', 'France']
['536370', '01/12/2018', '10002', 'Inflatable Political Globe', '1.63', '48', '12583', 'France']
['536370', '01/12/2018', '21791', 'Vintage Heads And Tails Card Game', '1.69', '24', '12583', 'France']
['536370', '01/12/2018', '21035', 'Set/2 Red Retrospot Tea Towels', '1.94', '18', '12583', 'France']
['536370', '01/12/2018', '22326', 'Round Snack Boxes Set Of4 Woodland', '1.94', '24', '12583', 'France']
['536370', '01/12/2018', '22629', 'Spaceboy Lunch Box', '1.79', '24', '12583', 'France']
['536370', '01/12/2018', '22659', 'Lunch Box I Love London', '0.00', '24', '12583', 'France']
['536370', '01/12/2018', '22631', 'Circus Parade Lunch Box', '1.79', '24', '12583', 'France']
['536370', '01/12/2018', '22661', 'Charlotte Bag Dolly Girl Design', '1.63', '20', '12583', 'France']
['536370', '01/12/2018', '21731', 'Red Toadstool Led Night Light', '1.75', '24', '12583', 'France']
['536370', '01/12/2018', '22900', 'Set 2 Tea Towels I Love London', '1.94', '24', '12583', 'France']
['536370', '01/12/2018', '21913', 'Vintage Seaside Jigsaw Puzzles', '2.06', '12', '12583', 'France']
['536370', '01/12/2018', '22540', 'Mini Jigsaw Circus Parade', '1.56', '24', '12583', 'France']
['536370', '01/12/2018', '22544', 'Mini Jigsaw Spaceboy', '1.56', '24', '12583', 'France']
['536370', '01/12/2018', '22492', 'Mini Paint Set Vintage', '1.6', '36', '12583', 'France']
['536502', '01/12/2018', '84879', 'Assorted Colour Bird Ornament', '1.75', '16', '16552', 'United Kingdom']
['536536', '01/12/2018', '84879', 'Assorted Colour Bird Ornament', '1.75', '80', '18144', 'United Kingdom']
['536607', '02/12/2018', '84879', 'Assorted Colour Bird Ornament', '1.75', '8', '17732', 'United Kingdom']
['536681', '02/12/2018', '84879', 'Assorted Colour Bird Ornament', '1.75', '8', '17287', 'United Kingdom']
['536876', '03/12/2018', '84879', 'Assorted Colour Bird Ornament', '1.98', '8', '12876', 'Belgium']
['536876', '03/12/2018', '84879', 'Assorted Colour Bird Ornament', '1.98', '10', '12876', 'Belgium']
['536876', '03/12/2018', '84879', 'Assorted Colour Bird Ornament', '1.98', '15', '12876', 'Belgium']
['C536383', '01/12/2018', '35004C', 'Set Of 3 Coloured Flying Ducks', '2.2', '-1', '15311', 'United Kingdom']
['C536391', '01/12/2018', '22556', 'Plasters In Tin Circus Parade', '1.75', '-12', '17548', 'United Kingdom']
['C536391', '01/12/2018', '21984', 'Pack Of 12 Pink Paisley Tissues', '1.54', '-24', '17548', 'United Kingdom']
['C536391', '01/12/2018', '21983', 'Pack Of 12 Blue Paisley Tissues', '1.54', '-24', '17548', 'United Kingdom']
['C536391', '01/12/2018', '21980', 'Pack Of 12 Red Retrospot Tissues', '1.54', '-24', '17548', 'United Kingdom']
['C536391', '01/12/2018', '21484', 'Chick Grey Hot Water Bottle', '2.02', '-12', '17548', 'United Kingdom']
['C536391', '01/12/2018', '22557', 'Plasters In Tin Vintage Paisley', '1.75', '-12', '17548', 'United Kingdom']
['C536391', '01/12/2018', '22553', 'Plasters In Tin Skulls', '1.75', '-24', '17548', 'United Kingdom']
['C536506', '01/12/2018', '22960', 'Jam Making Set With Jars', '2.14', '-6', '17897', 'United Kingdom']
['C536543', '01/12/2018', '22632', 'Hand Warmer Red Retrospot', '1.81', '-1', '17841', 'United Kingdom']
['C536543', '01/12/2018', '22355', 'Charlotte Bag Suki Design', '1.63', '-2', '17841', 'United Kingdom']
['C536548', '01/12/2018', '22244', '3 Hook Hanger Magic Garden', '1.79', '-4', '12472', 'Germany']
['C536548', '01/12/2018', '22242', '5 Hook Hanger Magic Toadstool', '1.75', '-5', '12472', 'Germany']
['C536548', '01/12/2018', '20914', 'Set/5 Red Retrospot Lid Glass Bowls', '1.94', '-1', '12472', 'Germany']
['C536548', '01/12/2018', '22892', 'Set Of Salt And Pepper Toadstools', '1.69', '-7', '12472', 'Germany']
['C536548', '01/12/2018', '22654', 'Deluxe Sewing Kit', '2.39', '-1', '12472', 'Germany']
['C536548', '01/12/2018', '22767', 'Triple Photo Frame Cornice', '2.99', '-2', '12472', 'Germany']
['C536548', '01/12/2018', '22333', 'Retrospot Party Bag + Sticker Set', '1.75', '-1', '12472', 'Germany']
['C536548', '01/12/2018', '22245', 'Hook 1 Hanger Magic Garden', '1.63', '-2', '12472', 'Germany']
['C536548', '01/12/2018', '22077', '6 Ribbons Rustic Charm', '1.75', '-6', '12472', 'Germany']
['C536548', '01/12/2018', '22631', 'Circus Parade Lunch Box', '1.79', '-1', '12472', 'Germany']
['C536548', '01/12/2018', '22168', 'Organiser Wood Antique White', '2.78', '-2', '12472', 'Germany']
['C536548', '01/12/2018', '21218', 'Red Spotty Biscuit Tin', '2.06', '-3', '12472', 'Germany']
['C536548', '01/12/2018', '20957', 'Porcelain Hanging Bell Small', '1.72', '-1', '12472', 'Germany']
['C536548', '01/12/2018', '22580', 'Advent Calendar Gingham Sack', '2.39', '-4', '12472', 'Germany']

import statistics as st

with open("CA2Dataset.csv", "r", encoding = "utf8") as file:
    reader = csv.reader(file)
    
    for row in reader:
        TransactionNo = row[0]
        Date = row[1]
        ProductNo = row[2]
        ProductName = row[3]
        Price = row[4]
        Quantity = row[5]
        CustomerNo = row[6]
        Country = row[7]
        


for item in row:
    average = st.mean(row[4])
    print(average)

but getting error

TypeError: can't convert type 'str' to numerator/denominator

Please, I need help on how to get these values. Thanks

pandas csv statistics python
2 Answers

The immediate error you are receiving but getting error TypeError: can't convert type 'str' to numerator/denominator Is because when it is being read from the csv it is being interpreted as a string.

Instead of Price = row[4] you will want to type cast what is coming out of the csv reader, making that line

price = float(row[4])

There are additional problems here and I would suggest you take a look at the documentation https://docs.python.org/3/library/statistics.html#statistics.mean . It says the mean function requires an iterable. So you will most likely want to make your price a list, and append to that list as you iterate through the rows of your csv. At the moment your code is attempting to give you the mean of a literal, which will give you an error as it is not iterable (list/tuple/etc)

user_43710170

To find average price

` import statistics as st

 with open("CA2Dataset.csv", "r", encoding = "utf8") as file:
    reader = list(csv.reader(file))

 Price = []
 AvgPrice = []

 for instance in reader:
     if instance[1] == '01/12/2018':
        SumPrice.append(float(instance[4]))
 totalPrice = (sum(Price))
 print(round(totalPrice, 2))
 AvgPrice = totalPrice/len(instance[4])
 print(round(AvgPrice, 2))
 

 Get the average quantity of sales for the France across all products.

 QuantityFrance = []
 for instance in reader:
    if instance[1] == '01/12/2018' and instance[6] == '12583' and    instance[7] == 'France':
        QuantityFrance.append(int(instance[5]))
 print(sum(QuantityFrance))`

 Get the total amount of money spent in the United Kingdom on product number 84879 on 1/12/2018.


TotalAmtUK = []
for instance in reader:
    if instance[1] == '01/12/2018' and instance[2] == '84879' and instance[7] == 'United Kingdom':
        TotalAmtUK.append(float(instance[4]))
print(TotalAmtUK)
print(sum(TotalAmtUK))


Get the total amount of money spent on product number 84879 on 3/12/2018 in Belgium.


TotalAmtBelgium = []
QuantityBG=[]
for instance in reader:
    if instance[1] == '3/12/2018' and instance[2] == '84879' and instance[7] == 'Belgium':
        TotalAmtBelgium.append(float(instance[4]))
        QuantityBG.append(int(instance[5]))
print(TotalAmtBelgium)
print(sum(TotalAmtBelgium))
print(QuantityBG)


Graph the quantities of item number 84879 sold in the UK on 1/12/2018 against the same item sold in Belgium on 3/12/2018.



 x = [1.63, 1.88, 2.69 ]
 uky = [1.75, 1.75, 1.75]
 belgiumy =[1.98, 1.98, 1.98]


 plt.plot(x,uky)
 plt.plot(x,belgiumy)
 plt.xlabel('Product')
 plt.ylabel('Amount Spent')
 plt.title('Amount Spent on Product 84879 in Belgium & UK')

 plt.show()

`

user_160679000
Related