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

Answer 1

# 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%$