Using C# or Python, write a code that reads a Microsoft Excel CSV file named \"L

Question

Using C# or Python, write a code that reads a Microsoft Excel CSV file named "LabAssignment" then outputs all the collected and formated data as another Microsoft Excel CSV file named "labOutput" Do NOT use pandas

The CSV file contains 25 colums as well close to 200 rows of data.

Write a code in C# or Python so that:

Removes the first row (the column headers)

sorts the data such as in the example below

output new data into microsoft excel CSV file titles "labOutput"

EXAMPLE

CSV file contains:

"Location" "Feature" "User" "NumUser" "HoursUsed" (and 20 more column headers...)

HOST: USA test Bob 1 0.5

HOST::USA test Billy 1 1

HOST: USA test Bob 1 1

HOST: USA test2 Bob 1 2.5

HOST: USA test Jill 1 1

HOST:USA test3 Bob 1 1  

HOST:USA test2 Billy 1 1

HOST:USA test2 Jill 1 1

HOST:USA test3 Jill 1 1

HOST: USA test3 Billy 1 1

Output:

HOST:USA Bob test 1.5

HOST:USA Bob test2 2.5

HOST:USA Bob test3 1

HOST::USA test Billy 1 1

HOST::USA test2 Billy 1 1

HOST::USA test3 Billy 1 1

HOST:USA test Jill 1 1

HOST:USA test2 Jill 1 1

HOST:USA test3 Jill 1 1

Explanation / Answer

import sys
import csv
from sys import argv
from operator import itemgetter

num_arguments = len(argv)

# Check usage and provide help
if num_arguments == 2 and argv[1] in ('-h', '-help'):
   print "Usage: %s input_file.csv 1st_sort_col ... nth_sort_col" % argv[0]
   print "Example: %s foo.csv 1 2 -9" % argv[0]
   print " Sorts foo.csv on 1st and 2nd columns (ascending) then 9th descending."
   sys.exit()
elif num_arguments < 3: # Guidance on arguments to pass
   usage = "Usage: %s input_file.csv 1st_sort_col ... nth_sort_col" % argv[0]
   error = "You passed only %d arguments" % num_arguments
   sys.exit("%s -- %s" % (usage, error))
if '.csv' not in argv[1]: # Ensure using a CSV file
   usage = "Usage: %s input_file.csv 1st_sort_col ... nth_sort_col" % argv[0]
   error = "You passed %r for input_file.csv" % argv[1]
   sys.exit("%s -- %s" % (usage, error))

# Create the output file as input with _sorted before .csv extension
input_file = argv[1]
output_file = input_file.replace('.csv', '_sorted.csv')

# Ensure you can open the source and target files
try:
   source = open(input_file, 'r')
except:
   e = sys.exc_info()[0]
   sys.exit("Error - Could not open input file %r: %s" % (input_file, e))
try:
   target = open(output_file, 'w')
except:
   e = sys.exc_info()[0]
   sys.exit("Error - Could not open output file %r: %s" % (output_file, e))
print " Sorting data from %r into %r, inside out" % (input_file, output_file)
sorts = []
for i in range (2, num_arguments): # Skip script name and input filename
   # Ensure you are passed Excel-like column numbers
   try:
       sort_arg = int(argv[i])
   except:
       e = sys.exc_info()[0]
       sys.exit("Error - Sort column %r not an integer: %s." % (argv[i], e))
   if sort_arg == 0:
       sys.exit("Error - Use Excel-like column numbers from 1 to N")
   # Create a tuple for each as described above
   if sort_arg > 0:
       sorts.append((sort_arg - 1, False)) # Convert column num to index num
   else:
       sorts.append(((-1 * sort_arg) - 1, True))

# Read in the data creating a label list and list of one tuple per row
reader = csv.reader(source)
row_count = 0
data=[]
for row in reader:
   row_count += 1
   # Place the first row into the header
   if row_count == 1:
       header = row
       continue
   # Append all non-header rows into a list of data as a tuple of cells
   data.append(tuple(row))
for sort_step in reversed(sorts):
   print 'Sorting Column %d ("%s") Descending=%s' %
   (sort_step[0] + 1, header[sort_step[0]], sort_step[1]) # +1 for Excel col num
   data = sorted(data, key=itemgetter(sort_step[0]), reverse=sort_step[1])
print 'Done sorting %d data rows (excluding header row) from %r' %
((row_count - 1), input_file)
writer = csv.writer(target)
writer.writerow(header) # Write the header in CSV format
for sorted_row in data: # Wrtie the sorted data, converting to CSV format
   writer.writerow(sorted_row)
print 'Done writing %d rows (sorted data plus header) to %r ' %
(row_count, output_file)
source.closed
target.closed

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.