0

I would like some advice on how to split a excel header cell using python, pandas, or openpyxl (what I'm currently trying to learn. This is something that I've done in excel/vba using text-to-columns. But I'm coming up with errors while trying to get this to work with Python... I have a dataframe and use pd.read_excel to the file. I can print(df.columns.tolist()) to see my columns.enter image description here I would like a piece of code to place each test# into it's own column.

These are all headers within a dataframe.

This is what I'd like to accomplish.
enter image description here

I had tried this method believing 5 is the column I need.

df.columns = df.columns[5].split(',')

I have also tried this method. Here I was thinking I had to list out what i wanted in the first statement and the name of the E1 cell currently. The delimiter is ',' a comma between each test#.

df[['test1','test2','test3','test4','test5',test6','test7','test8','test9']] = 
df.test1,test2,test3,test4,test5,test6,test7,test8,test9.str.split(",",expand=True,)

Any help would be appreciated. Thanks

This is an excel version of the dataframe version already posted above.

enter image description here

Boomer
  • 229
  • 1
  • 9

1 Answers1

0

The following code essentially grabs the name of the column of interest, removes it from the dataframe, and adds its sub-components back into the dataframe.

import pandas as pd

df = pd.read_excel("example.xlsx")
column_name_list = df.columns[-1].split(',')
df = df.drop(df.columns[-1], axis=1)
for column_name in column_name_list:
    df[column_name] = pd.Series()
Brian Ko
  • 163
  • 1
  • 8
  • Hey Brian, I spoke a bit too soon. the code works when I screen print(df.columns.tolist()). Everything looks great. But, when I perform a writer.save() to an example_out.xlsx the cell does not split out to individual columns. Kinda strange. Any thoughts? – Boomer Jun 25 '20 at 02:43
  • Why don't you update the original post for the new problem so that I can better help you? – Brian Ko Jun 25 '20 at 05:42