I have a CSV file, here is a sample of what it looks like:
Year: Dec: Jan: 1 50 60 2 25 50 3 30 30 4 40 20 5 10 10 I know how to read the file in and print each column (for ex. - ['Year', '1', '2', '3', etc]). But what I actually want to do is read the rows, which would be like this ['Year', 'Dec', 'Jan'] and then ['1', '50', '60'] and so on.
And then I would like to store those numbers ['1', '50', '60'] into variables so I can total them later for ex.:
Year_1 = ['50', '60']. Then I can do sum(Year_1) = 110.
How would I go about doing that in Python 3?
10 Answers
Use the csv module:
import csv with open("test.csv", "r") as f: reader = csv.reader(f, delimiter="\t") for i, line in enumerate(reader): print 'line[{}] = {}'.format(i, line) Output:
line[0] = ['Year:', 'Dec:', 'Jan:'] line[1] = ['1', '50', '60'] line[2] = ['2', '25', '50'] line[3] = ['3', '30', '30'] line[4] = ['4', '40', '20'] line[5] = ['5', '10', '10'] You could do something like this:
with open("data1.txt") as f: lis = [line.split() for line in f] # create a list of lists for i, x in enumerate(lis): #print the list items print "line{0} = {1}".format(i, x) # output line0 = ['Year:', 'Dec:', 'Jan:'] line1 = ['1', '50', '60'] line2 = ['2', '25', '50'] line3 = ['3', '30', '30'] line4 = ['4', '40', '20'] line5 = ['5', '10', '10'] or :
with open("data1.txt") as f: for i, line in enumerate(f): print "line {0} = {1}".format(i, line.split()) # output line 0 = ['Year:', 'Dec:', 'Jan:'] line 1 = ['1', '50', '60'] line 2 = ['2', '25', '50'] line 3 = ['3', '30', '30'] line 4 = ['4', '40', '20'] line 5 = ['5', '10', '10'] Edit:
with open('data1.txt') as f: print "{0}".format(f.readline().split()) for x in f: x = x.split() print "{0} = {1}".format(x[0],sum(map(int, x[1:]))) # output ['Year:', 'Dec:', 'Jan:'] 1 = 110 2 = 75 3 = 60 4 = 60 5 = 20 Reading it columnwise is harder?
Anyway this reads the line and stores the values in a list:
for line in open("csvfile.csv"): csv_row = line.split() #returns a list ["1","50","60"] Modern solution:
# pip install pandas import pandas as pd df = pd.read_table("csvfile.csv", sep=" ") The Easiest way is this way :
from csv import reader # open file in read mode with open('file.csv', 'r') as read_obj: # pass the file object to reader() to get the reader object csv_reader = reader(read_obj) # Iterate over each row in the csv using reader object for row in csv_reader: # row variable is a list that represents a row in csv print(row) output: ['Year:', 'Dec:', 'Jan:'] ['1', '50', '60'] ['2', '25', '50'] ['3', '30', '30'] ['4', '40', '20'] ['5', '10', '10'] import csv with open('filepath/filename.csv', "rt", encoding='ascii') as infile: read = csv.reader(infile) for row in read : print (row) This will solve your problem. Don't forget to give the encoding.
1# This program reads columns in a csv file import csv ifile = open('years.csv', "r") reader = csv.reader(ifile) # initialization and declaration of variables rownum = 0 year = 0 dec = 0 jan = 0 total_years = 0` for row in reader: if rownum == 0: header = row #work with header row if you like else: colnum = 0 for col in row: if colnum == 0: year = float(col) if colnum == 1: dec = float(col) if colnum == 2: jan = float(col) colnum += 1 # end of if structure # now we can process results if rownum != 0: print(year, dec, jan) total_years = total_years + year print(total_years) # time to go after the next row/bar rownum += 1 ifile.close() A bit late but nonetheless... You need to create and identify the csv file named "years.csv":
Year Dec Jan 1 50 60 2 25 50 3 30 30 4 40 20 5 10 10
1Example:
import pandas as pd data = pd.read_csv('data.csv') # read row line by line for d in data.values: # read column by index print(d[2]) The csv module handles csv files by row. If you want to handle it by column, pandas is a good solution.
Besides, there are 2 ways to get all (or specific) columns with pure simple Python code.
1. csv.DictReader
with open('demo.csv') as file: data = {} for row in csv.DictReader(file): for key, value in row.items(): if key not in data: data[key] = [] data[key].append(value) It is easy to understand.
2. csv.reader with zip
with open('demo.csv') as file: data = {values[0]: values[1:] for values in zip(*csv.reader(file))} This is not very clear, but efficient.
zip(x, y, z) transpose (x, y, z), while x, y, z are lists. *csv.reader(file) make (x, y, z) for zip, with column names.
Demo Result
The content of demo.csv:
a,b,c 1,2,3 4,5,6 7,8,9 The result of 1:
>>> print(data) {'c': ['3', '6', '9'], 'b': ['2', '5', '8'], 'a': ['1', '4', '7']} The result of 2:
>>> print(data) {'c': ('3', '6', '9'), 'b': ('2', '5', '8'), 'a': ('1', '4', '7')} One can do it using pandas library.
Example:
import numpy as np import pandas as pd file = r"C:\Users\unknown\Documents\Example.csv" df1 = pd.read_csv(file) df1.head() I just leave my solution here.
import csv import numpy as np with open(name, newline='') as f: reader = csv.reader(f, delimiter=",") # skip header next(reader) # convert csv to list and then to np.array data = np.array(list(reader))[:, 1:] # skip the first column print(data.shape) # => (N, 2) # sum each row s = data.sum(axis=1) print(s.shape) # => (N,) 
