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"

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

Write a code in C# or Python so that:

It removes all unwanted columns along with its data...the columns that should be kept have the following header: "User" "Location" "Feature" "HoursUsed"

Remove all files with the "User" Billy

it outputs the data in the following order by column header:

"User" "Location" "Feature" "HoursUsed"

for "HoursUsed" make sure all the hours are added up

for "Location" remove "HOST:"

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 test2 Jill 1 1

Output:

"User" "Location" "Feature" "HoursUsed"

Bob USA test 1.5

Bob USA test2 2.5

Jill USA test2 1

Answer 1

Please give the thumbs up, if it is helpful for you!!. Let me know if you have any doubts.

# -*- coding: utf-8 -*-
"""
Created on Thu Sep 28 07:53:46 2017

@author:
"""

import pandas as pd

df=pd.read_csv('LabAssignment.csv')

new_df = df[['User','Location', 'Feature', 'HoursUsed']].copy()

df1= new_df[df["User"] != 'Billy']

#print(df1)

df2=df1.groupby(['User','Location','Feature']).sum().reset_index()

df2['Location'] = df2['Location'].str.replace('HOST: ',' ')

#print()
#print(df2)

df2.to_csv('labOutput.csv', sep=',', encoding='utf-8',index=False)

Output:

IPython console CConsole 1/A In [19]: runfile('E:/projects/work/HomeworkLib/python/csv operations/csv.py',wdir-'E:/projects/work/ HomeworkLib/python/csv operations') User Location Feature HoursUsed 0 Bob HOST: USA test 2 Bob HOST: USA test 3 Bob HOST: USA test2 4 Jill HOST: USA test2 6 Bob HOST: USA test2 2.5 0 Bot 1 Bob 2 Jill User Location Feature HoursUsed USA test USA test2 USA test2 3.5 In [20]:

PC csv. ру LabAssignment.c labOutput.csv labOutput.csv - Excel HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER Load Test Calibri General ▼|E€ Conditional Formatting ▼ ea. Blu. A. A 臣_ $.96, Format as Table ▼ Paste Cells Editing ←.0 .00 Cell Styles 00 →.0 Clipboard Font Alignment Number G Styles A1 1 User 2 Bob 3 Bob 4 Jill Location Feature HoursUsed USA USA USA test test2 test2 6 abOutput READY + 100%