2

how to divide a column in to two columns in an excel using a delimiter ', 'and name the header's using python

here is my code

import openpyxl


w=openpyxl.load_workbook('DDdata.xlsx')
active=w.active


a=active.columns[1]
for cellobj in a:
    s=cellobj.value
    fila=s.split(',')

    print(fila)

[Input file link][1]

[outputfile][3]

ak_1
  • 173
  • 1
  • 2
  • 12
  • Questions seeking debugging help (**"why isn't this code working?"**) must include the desired behavior, *a specific problem or error* and *the shortest code necessary* to reproduce it **in the question itself**. Questions without **a clear problem statement** are not useful to other readers. See: [How to create a Minimal, Complete, and Verifiable Example](http://stackoverflow.com/help/mcve). Your question is completely unclear. What are you expecting `firstname[]` and `lastname[]` to do? What are the contents of your Excel file? What does this have to do with `pandas`? – MattDMo Sep 18 '16 at 03:14
  • 1
    You need to include data for debugging – tfv Sep 18 '16 at 06:10
  • @MattDMo The contents of my excel file are it has a column with first name and Last name separated by comma, What i would like to achieve in my output excel file is to separate first name and last name and add them in the output excel file as two columns. I am not sure if this can be achieved using openpyxl module or pandas module that is the reason i added both of them in the code. – ak_1 Sep 18 '16 at 16:23
  • @tfv i added input and required output file thanks for taking time to look in to my question i am new to stack overflow and python thanks for guiding me. – ak_1 Sep 18 '16 at 16:43
  • You have included a screenshot of your excel file, this does not help a lot. The python program only works on xls input, so you will have to provide a linkt to the xls file (or parts of it for demonstration). – tfv Sep 18 '16 at 16:57
  • @akhil Is there a particular reason you're using Python for this? You can do this relatively easily directly in Excel. – MattDMo Sep 18 '16 at 17:18
  • @MattDMo yes i want to automate certain things that i do repeatedly in excel and also get hand on experience in python. – ak_1 Sep 18 '16 at 17:31
  • @tfv I included the link thanks. – ak_1 Sep 18 '16 at 17:39

1 Answers1

3

This looks to be a duplicate of this post, but here's a solution for you to consider:

import pandas as pd

df = pd.read_excel('input.xlsx')

df['First Name'], df['Last Name'] = df['Applicant Name'].str.split(',', 1).str
del df['Applicant Name']

df.to_excel('output.xlsx')

Output (in python):

First Name  Last Name
0   -   Mohammed Abdul Naser Aziz
1   -   Gottipati Harshavardhan
2   -   Sandeep Kaur
3   .   Rounak
4   abbas   Syed
5   Abbas   Mohd Manzar
6   Abbasi  Fatema
7   Abdelkader  BEKHTIE
8   abdollahzadehkan    mina
Community
  • 1
  • 1
shawnheide
  • 797
  • 4
  • 11