Adding new headers and splitting column
Question: I have a large `csv` with data that I have imported to python with pandas. The
first 3 rows of the `csv` look like the following.

    
    
    “PATIENT”,"MD",“REFMD”,“DIAGNOSIS_HISTORY”,“AVAILABLE_STUDIES”
    “patient1\nPID1\npAge1”,“MDname1\nMDname3”,” RefDoctorName1”,“Prostate cancer”,”No Path\n CT ClinicName (CAP)  - 11/30/2015\n Nuclear: ClinicName (Bone Scan)  - 11/30/2015"
    "patient2\nPID2\npAge2”,”MDname2\nSeen 10/12/2015”,“RefDoctorName2”,”Prostate cancer”,”Path: O/S - Prostate Bx 11/12/2014”
    

I want to

  * split the first `column` in 3 parts from “PATIENT” to “PATIENT_Name”, "PID", "pAGE" and 
  * in the second column remove the second MD if there one and add a new column of "MD2" to collect those times when a patient saw more than one MD at the same clinic. 
  * Also, I want to split out the incidences of \nSeen Date in the MD column and place that in a new column titled "Date_Seen".

I have all the columns split out, but having a hard time with the next step.

    
    
    import pandas as pd
    f = pd.read_csv("/path/file.csv")
    pat = f.iloc[0:,:1]
    refmd = f.iloc[0:,2:3]
    diag = f.iloc[0:,3:4]
    


Answer: You could start with the following:

    
    
    df.columns = [re.sub(r'[^A-Za-z0-9\\]+', '', c).strip() for c in df.columns]
    for i, col in df.items():
        df.loc[:, i] = col.str.replace(r'[^A-Za-z0-9\\ ]+', '').str.strip()
    

to get:

    
    
                     PATIENT                      MD           REFMD  \
    0  patient1\nPID1\npAge1        MDname1\nMDname3  RefDoctorName1   
    1  patient2\nPID2\npAge2  MDname2\nSeen 10122015  RefDoctorName2   
    
      DIAGNOSISHISTORY                                   AVAILABLESTUDIES  
    0  Prostate cancer  No Path\n CT ClinicName CAP   11302015\n Nucle...  
    1  Prostate cancer                      Path OS  Prostate Bx 11122014 
    

To `split` and `expand` into new `columns` on the `newline` characters:

    
    
    pat = df.iloc[:, 0].str.split(r'\\n', expand=True)
    pat.columns = ['PATIENT_name', 'PID', 'pAGE']
    
      PATIENT_name   PID   pAGE
    0     patient1  PID1  pAge1
    1     patient2  PID2  pAge2
    

and:

    
    
    md = df.iloc[:, 1].str.split(r'\\n', expand=True)
    md.columns = ['MD', 'MD2']
    
            MD            MD2
    0  MDname1        MDname3
    1  MDname2  Seen 10122015
    

