separate csv columns while a row of cell have different number of elements

user_15605043 0
Viewed 102

I have a csv file that look like this:

Original csv

I want to convert it to something like this:

Converted csv

The issue is that the first row of each column has 4 elements, while the rest of the rows have 7 elements. I want to keep them aligned. also I want to keep the original column separation.

Anyone have any ideas for how to achieve this?

pandas csv python
1 Answers

Assuming your df is something like this:

                          0                         1                         2
0    read pass 1000K times.    read pass 2000K times.    read pass 3000K times.
1  MAX BER WL layer 0 == 11  MAX BER WL layer 0 == 18  MAX BER WL layer 0 == 18
2   MAX BER WL layer 1 == 5  MAX BER WL layer 1 == 15  MAX BER WL layer 0 == 15

You can use .str.split(expand=True) on each column and concat() the results:

out = pd.concat([df[column].str.split(expand=True) for column in df.columns], axis=1)
0 1 2 3 4 5 6 0 1 2 ... 4 5 6 0 1 2 3 4 5 6
0 read pass 1000K times. None None None read pass 2000K ... None None None read pass 3000K times. None None None
1 MAX BER WL layer 0 == 11 MAX BER WL ... 0 == 18 MAX BER WL layer 0 == 18
2 MAX BER WL layer 1 == 5 MAX BER WL ... 1 == 15 MAX BER WL layer 0 == 15

Sample df for reference:

csv = '''
read pass 1000K times.,read pass 2000K times.,read pass 3000K times.
MAX BER WL layer 0 == 11,MAX BER WL layer 0 == 18,MAX BER WL layer 0 == 18
MAX BER WL layer 1 == 5,MAX BER WL layer 1 == 15,MAX BER WL layer 0 == 15
'''
df = pd.read_csv(StringIO(csv), header=None)
user_131383640
Related