Myujth

I am building out an ETL process in Python with the help of Pandas. I am trying to split some of the flat files by the delimiter "_" in which, within the column I want to split, there are some rows that contain 3 delimiters and some that contain 4 delimiters (as an additional details part).

In the example a file that contains 3 delimiters within the column, if I add column 5 and use n=4, it gives me a Columns must be same length as key error which makes sense as there are only 3 delimiters (if i use only 4 columns and n=3 it works but not what I want).

How can I get around this and when it spots the extra delimiter still splits it into a column and if not just leaves that column as null or blank. I also want to specify a n value as I do not want it to keep splitting at every delimiter. Any help would be amazing!

df[['column1','column2','column3','column4',
'column5 may or may not exisit']] = df['Column_to_split'].str.split('_',n=4,expand=True)

Example Data

0 Column_to_split nextcolumn nextcolumn nextcolumn nextcolumn
0 text_text_text text2 text3 text4 23
1 text_text_text text2 text3 text4 8

Desired Result

0 Column_to_split Column_to_split1 Column_to_split2 Column_to_split3 Column_to_split4 nextcolumn nextcolumn nextcolumn nextcolumn
0 text_text_text text text text null text2 text3 text4 23
1 text_text_text text text text null text2 text3 text4 8

Maybe I'm missing something; does this approach work for you?

import pandas as pd

df = pd.DataFrame(["text1, text2, text3, text4", "text1, text2, text3, text4, text5"], columns=["column_name"])
print(df)

Output:

 column_name
0 text1, text2, text3, text4
1 text1, text2, text3, text4, text5

Split the single column into many columns:

df_split = df["column_name"].str.split(",", expand=True)
print(df_split)

Output:

 0 1 2 3 4
0 text1 text2 text3 text4 None
1 text1 text2 text3 text4 text5

You can rename the columns after this operation.

df_split.rename(columns=0:"column1", inplace=True)
print(df_split)

Output:

 column1 1 2 3 4
0 text1 text2 text3 text4 None
1 text1 text2 text3 text4 text5

Alt approach, after your comment:

df = pd.DataFrame([["text1, text2, text3, text4",
 "text1, text2, text3, text4, text5"], 
 ["text1, text2, text3, text4",
 "text1, text2, text3, text4, text5"]],
 columns=["column1", "column2"])
print(df)

list_of_dfs = 

for col in df.columns:
 temp_df = df[col].str.split(",", expand=True)
 print(temp_df)
 list_of_dfs.append(temp_df)

split_df = pd.concat(list_of_dfs)
print(split_df)

Sample output:

 0 1 2 3
0 text1 text2 text3 text4
1 text1 text2 text3 text4
 0 1 2 3 4
0 text1 text2 text3 text4 text5
1 text1 text2 text3 text4 text5
 0 1 2 3 4
0 text1 text2 text3 text4 NaN
1 text1 text2 text3 text4 NaN
0 text1 text2 text3 text4 text5
1 text1 text2 text3 text4 text5