1 answer

Using python programming language write a program that will manipulate the below excel record to the...

Question:

using python programming language

write a program that will manipulate the below excel record to the output provided

Name Path ID Python-Excel C:\Users Desktop Python-Excell, C:Users IDesktop|Python-Excel2 PE12370, P009872,CD12890,U199912

This is what the output should look like

Name Path ID Python-Excel C:\Users Desktop Python-Excell, C:Users IDesktop|Python-Excel2 PE12370, P009872,CD12890,U199912

Answers

# I am assuming that the path column of excel contains all the comma-separated entries which are in the output and same for IDs.


# coding: utf-8

# In[1]:


import pandas as pd
import numpy as np
# requirement: intall xlrd using pip install xlrd
# install openpyxl using pip install openpyxl


# In[2]:


file = 'data.xlsx' # path to excel file
data = pd.read_excel(file) # reading excel file using pandas (remember excel file has a header)


# In[3]:


print(data) # printing data in excel file


# In[4]:


new_data = pd.DataFrame(columns=['Name','Path','ID']) # create empty data frame
for row in data.values: # for each row of data (excel file)
paths = row[1].split(',') # split the paths into list of path
ids = row[2].split(',') # split IDs into list of id
name = row[0] # name of row
for path, ID in zip(paths, ids): # adding each name, path, id into the new data frame
new_data = new_data.append(pd.DataFrame([{'Name': name, 'Path': path, 'ID': ID}]), ignore_index=True)
  


# In[7]:


new_data = new_data[['Name', 'Path', 'ID']] # rearranging the column names and corresponding columns
writer = pd.ExcelWriter('new_data.xlsx') # Saving the new data set into new excel file
new_data.to_excel(writer, index=False)
writer.save()


# In[8]:


print(new_data)

data.dsx - Excel Insert Page Layout FormulasData Review View Tell me what you want to do... Noorul Hasan s Cut AutoSum -A Colibri Fill 秏硅剽Merge & Center. $, % , 留昮 Conditional Format as Cell Formatting Table Styles Paste Insert Delete Format в 1 u. | Er·· ク.Δ, Clear Sort & Find & Filter Select Format Painter Clipboard Font Alignment Number Cells Editing A6 1 Name Path C:lUserslIDesktop\Python-Excel1, C\UsersDesktop\Python-Excel2, C:lUserslIDesktop\Python-Excel3, C:\UsersDesktop\Python-Excel4, C:lUserslIDesktop\Python-Excel2, C\UsersDesktop\Python-Excel3, ID PE12370, PO09872, CD12890, U199912, PE12370, PO09872, CD12890, U199912 2 Python-Excel C\UsersDesktoplPython-Excel4, C Users\\Desktop\Python-Excel5, 10 12 13 14 15 16 17 18 19 21 Sheet Ready 囲回凹- + 100%

.

Similar Solved Questions

1 answer
Norwegian Cruise liners uses job order cost to determine the costs of the ships it produces....
Norwegian Cruise liners uses job order cost to determine the costs of the ships it produces. During the year 2014 it worked on 4 different types of ships, Family Fun, Romantic Travels, Executive Class, and Luxury Liners. The company has determined that machine hours are the best predictor of FOH cos...
1 answer
Aluminum produced in 1.00 h by the electrolysis of molten AICI if the current is 10,0...
aluminum produced in 1.00 h by the electrolysis of molten AICI if the current is 10,0 AN A It Belz ZAICI, 60 seconds 10 A 22600] =36,000 Aus=C 36,000 c. Zmolal E Imole- 27 = 20.15g 3.36 20.15 gal...
1 answer
The current population of a threatened animal species is 1.6 million, but it is declining with...
The current population of a threatened animal species is 1.6 million, but it is declining with a half-life of 20 years. How many animals will be left in 25 years? In 75 years?...
1 answer
What is the correct answer? Arizona Desert Homes (ADH) constructed a new subdivision during 2020 and...
What is the correct answer? Arizona Desert Homes (ADH) constructed a new subdivision during 2020 and 2021 under contract with Cactus Development Co. Relevant data are summarized below: Contract amount Cost: 2020 2021 Gross profit: $3,000,000 1,200,000 600,000 800,000 400,000 1,500,000 1,500,000 2020...
1 answer
A recent accounting graduate from Lethbridge University evaluated the operating performance of Fane Company's three divisions....
A recent accounting graduate from Lethbridge University evaluated the operating performance of Fane Company's three divisions. The following presentation was made to Fane’s Board of Directors. During the presentation, the accountant made the recommendation to eliminate the Southern Divisio...
1 answer
1.02 V; Zn2+(aq) and Ni2+(aq) QUESTION 8 Which of the following processes involves the use of...
1.02 V; Zn2+(aq) and Ni2+(aq) QUESTION 8 Which of the following processes involves the use of a voltaic as opposed to an electrolytic cell? Note this is a multiple answer question, you can select more the one answer Extracting gold from ore Silver plating cutlery Starting your car Plating chrome ont...
1 answer
#40, 41, 44, 45 please HANDEDNESS IN MOLECULES 18.40 What does the term chiral mean? Give...
#40, 41, 44, 45 please HANDEDNESS IN MOLECULES 18.40 What does the term chiral mean? Give two examples. 18.41 What does the term achiral mean? Give two examples. 18.42 Which of the following objects are chiral? (a) A mayonnaise jar (b) A rocking chair (c) A coin 18.43 Which of the following objec...
1 answer
***please be as accurate as possible this is extra credit because we did bad on test***...
***please be as accurate as possible this is extra credit because we did bad on test*** What is the ROTATION at B (to the nearest 0.0001 radians)? a = 8 ft b= 9 ft P = 183k E = 16 x 103 ksi | = 3,905 in 4 P A 7 b...
1 answer
Empt=475859&cmid=1507980 es EUN-1416 PTIT OF Macroeconomics Spring ZUZU / Money and the recerar Reserve (LH 1481...
empt=475859&cmid=1507980 es EUN-1416 PTIT OF Macroeconomics Spring ZUZU / Money and the recerar Reserve (LH 1481 When the government collects more in taxes than it pays out in government spending and transfer payments, the result is a: Select one: a. trade deficit. b. recession. c. budget defici...
1 answer
Harge of 2.06E-9C is placed at the origin, and a charge of 3.60E-9C is placed at...
harge of 2.06E-9C is placed at the origin, and a charge of 3.60E-9C is placed at x = 1.43m. Locate the x coordinate of the point between the two charges at which a charge of 3.04E-9C should be placed so that the net electric force on it is zero...
1 answer
Read the following selection, and answer the following question. Dear Mr. Dunlap: Subject: Your Request for...
Read the following selection, and answer the following question. Dear Mr. Dunlap: Subject: Your Request for Invoicing Process Information I am happ question regarding the company invoice processing. As you may know, once a contract has been signed, service providers may invoice the company at any ti...
1 answer
Below is an elimination reaction. Based on what you know regarding elimination reactions, predict the order...
Below is an elimination reaction. Based on what you know regarding elimination reactions, predict the order of the product formation from major to minor product. Abp 164-166°C B1100 Cbp = 102°C Dbp=1043 • D >C>B C>D>B • B > C>D . C>B>D .D>B>C B>D>...
1 answer
Consider how Rouse Valley Spring Park Lodge could use capital budgeting to decide whether the $13,500,000...
Consider how Rouse Valley Spring Park Lodge could use capital budgeting to decide whether the $13,500,000 Spring Park Lodge expansion would be a good investment. Assume Rouse Valley's managers developed the following estimates concening the expansion: EB (Click the icon to view the estimates.) A...
1 answer
In order to accurately calculate an employee's pay, it is important that payroll clearly understands the...
in order to accurately calculate an employee's pay, it is important that payroll clearly understands the different types of employee remuneration. In your own words, list and define the four categories of employment income, such as earnings, allowances, benefits, and expense reimbursements, prov...