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
CodePudding user response:
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)
CodePudding user response:
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()
`