How to split a row into two rows in python based on delimiter in Python

user_1546610 0
Viewed 83

I have the following input file in csv

A,B,C,D
1,2,|3|4|5|6|7|8,9
11,12,|13|14|15|16|17|18,19

How do I split column C right in the middle into two new rows with additional column E where the first half of the split get "0" in Column E and the second half get "1" in Column E?

A,B,C,D,E
1,2,|3|4|5,9,0
1,2,|6|7|8,9,1
11,12,|13|14|15,19,0
11,12,|16|17|18,19,1

Thank you

pandas dataframe csv python
4 Answers

Here's how to do it without Pandas:

import csv

with open("input.csv", newline="") as f_in, open("output.csv", "w", newline="") as f_out:
    reader = csv.reader(f_in)

    header = next(reader)  # read header
    header += ["E"]  # modify header

    writer = csv.writer(f_out)
    writer.writerow(header)

    for row in reader:
        a, b, c, d = row  # assign 4 items for each row

        c_items = [x.strip() for x in c.split("|") if x.strip()]

        n_2 = len(c_items) // 2  # halfway index
        c1 = "|" + "|".join(c_items[:n_2])
        c2 = "|" + "|".join(c_items[n_2:])

        writer.writerow([a, b, c1, d, 0])  # 0 & 1 will be converted to str on write
        writer.writerow([a, b, c2, d, 1])
user_2468010

If I understand you correctly, you can use str.split on column 'C', then .explode() the column and join it again:

df["C"] = df["C"].apply(
    lambda x: [
        (vals := x.strip(" |").split("|"))[: len(vals) // 2],
        vals[len(vals) // 2 :],
    ]
)
df["E"] = df["C"].apply(lambda x: range(len(x)))
df = df.explode(["C", "E"])
df["C"] = "|" + df["C"].apply("|".join)

print(df.to_csv(index=False))

Prints:

A,B,C,D,E
1,2,|3|4|5,9,0
1,2,|6|7|8,9,1
11,12,|13|14|15,19,0
11,12,|16|17|18,19,1
user_100359850

Using a regex and str.findall to break the string, then explode and Groupby.cumcount:

(df.assign(C=df['C'].str.findall('(?:\|[^|]*){3}'))
   .explode('C')
   .assign(E=lambda d: d.groupby(level=0).cumcount())
   #.to_csv('out.csv', index=False)
 )

Output (before CSV export):

    A   B          C   D  E
0   1   2     |3|4|5   9  0
0   1   2     |6|7|8   9  1
1  11  12  |13|14|15  19  0
1  11  12  |16|17|18  19  1

Output CSV:

A,B,C,D,E
1,2,|3|4|5,9,0
1,2,|6|7|8,9,1
11,12,|13|14|15,19,0
11,12,|16|17|18,19,1
user_163434640

Another way

df=(df.assign(C=df['C'].str.replace('^\|','',regex=True)#remove leading | to allow split by the character
              .str.split('\|')#Split to create list
              .apply(lambda x:np.array_split(x, 2)))#split list into lists of sublists
             .explode('C')#explode into rows
               )
df = df.assign(C= "|" + df["C"].apply("|".join)#clean c
              ,E=df.groupby('A').cumcount('B'))
user_89869750
Related