By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,480 Members | 771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,480 IT Pros & Developers. It's quick & easy.

csv into multiple columns using split function using python

P: 1
How do I use Python to split a csv into multiple columns?

(using split function using python)
Expand|Select|Wrap|Line Numbers
  1. "CUSTOMERID","DESCRIPTION","FARE","GUESTS","SEATCLASS","SUCCESS"
  2. "1","Braund, Mr. Owen Harris;22","7.25","1","3","0"
  3. "2","Cumings, Mrs. John Bradley (Florence Briggs Thayer);38","71.2833","1","1","1"
  4. "3","Heikkinen, Miss. Laina;26","7.925","0","3","1"
  5. "4","Futrelle, Mrs. Jacques Heath (Lily May Peel);35","53.1","1","1","1"
  6. "5","Allen, Mr. William Henry;35","8.05","0","3","0"
  7. "6","Moran, Mr. James;","8.4583","0","3","0"
  8. "7","McCarthy, Mr. Timothy J;54","51.8625","0","1","0"
  9. "8","Palsson, Master. Gosta Leonard;2","21.075","3","3","0"
  10. "9","Johnson, Mrs. Oscar W (Elisabeth Vilhelmina Berg);27","11.1333","0","3","1"
  11. "10","Nasser, Mrs. Nicholas (Adele Achem);14","30.0708","1","2","1"
  12. "11","Sandstrom, Miss. Marguerite Rut;4","16.7","1","3","1"
  13. "12","Bonnell, Miss. Elizabeth;58","26.55","0","1","1"
  14. "13","Saundercock, Mr. William Henry;20","8.05","0","3","0"
  15. "14","Andersson, Mr. Anders Johan;39","31.275","1","3","0"
  16. "15","Vestrom, Miss. Hulda Amanda Adolfina;14","7.8542","0","3","0"

Expected Output
Expand|Select|Wrap|Line Numbers
  1. _CUSTOMERID     _DESCRIPTION    Prefix    Name    Age     _FARE     _GUESTS     _SEATCLASS     _SUCCESS
  2. 1    Braund     Mr     Owen Harris    22    7.25    1    3    0
  3. 2    Cumings     Mrs     John Bradley (Florence Briggs Thayer)    38    71.2833    1    1    1
  4. 3    Heikkinen     Miss     Laina    26    7.925    0    3    1
  5. 4    Futrelle     Mrs     Jacques Heath (Lily May Peel)    35    53.1    1    1    1
  6. 5    Allen     Mr     William Henry    35    8.05    0    3    0
  7. 7    McCarthy     Mr     Timothy J    54    51.8625    0    1    0
  8. 8    Palsson     Master     Gosta Leonard    2    21.075    3    3    0
  9. 9    Johnson     Mrs     Oscar W (Elisabeth Vilhelmina Berg)    27    11.1333    0    3    1
  10. 10    Nasser     Mrs     Nicholas (Adele Achem)    14    30.0708    1    2    1
  11. 11    Sandstrom     Miss     Marguerite Rut    4    16.7    1    3    1
  12. 12    Bonnell     Miss     Elizabeth    58    26.55    0    1    1
  13. 13    Saundercock     Mr     William Henry    20    8.05    0    3    0
  14. 14    Andersson     Mr     Anders Johan    39    31.275    1    3    0
  15. 15    Vestrom     Miss     Hulda Amanda Adolfina    14    7.8542    0    3    0
Run code snippetCopy snippet to answerExpand snippet

I am trying to split a specific column of csv into multiple column and then appending the split values at the end of each row.

I wanted to split second column on ',' and '.' and ';' and then append to each row respective values.


Expand|Select|Wrap|Line Numbers
  1. `enter code here`
  2. import csv
  3. fOpen1=open('Meta_D1.txt')
  4.  
  5. reader=csv.reader(fOpen1)
  6. mylist=[elem[1].split(',') for elem in reader]
  7. mylist1=[]
  8.  
  9. for elem in mylist:
  10.     mylist1.append(elem)
  11.  
  12.  
  13. #writing to a csv file
  14. with open('out1.csv', 'wb') as fp:
  15.     myf = csv.writer(fp, delimiter=',')
  16.     myf.writerows(mylist1)
  17.  Run code 
Here is the link to input file(Meta_D1.txt) and expected output file (Meta_D1.xlsx) I wanted to do it using python, I did it using excel.
Can someone guide me further?

python
Nov 29 '16 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.