Formatting a CSV file, split a column into rows

Question

I have a CSV file, of the following type:

I need to reformat it to the following form:

Could you tell me please, how can column Column_B be divided into rows, but only so that column Column_A is filled with corresponding values according to column Column_B.

Thank you very much.

Answer 1

At first, you need to retrieve your CSV file content into raw text.

content = "..."
final_content = ""

# a readable solution
for line in content.split('\n'):
    key = line.split(';')[0]
    vals = line.split(';')[1].split(',')
    final_content += key+";"+vals[0]+"\n"
    final_content += key+";"+vals[1]+"\n"

The same solution, but looks shorter

final_content = "\n".join([line.split(';')[0]+":"line.split(';')[1].split(",")[0]+'\n'+line.split(';')[0]+":"line.split(';')[1].split(",")[1] for line in content.split('\n')])

Answer 2

Basically you need to split lines and create those two lines out of a single line. Here is a step by step solution: (I explained it with my variable names)

with open('old.csv') as f:
    # storing the header
    header = next(f)

    res = []
    for line in f:
        with_semicolon_part, without_semicolumn_part = line.rstrip().split(',')
        first_part, second_part = with_semicolon_part.split(';')
        lst = [first_part, second_part, without_semicolumn_part]

        res.append(lst)

# creating new csv file with our `res`.
with open('new.csv', mode='w') as f:
    f.write(header)
    for lst in res:
        f.write(lst[0] + ';' + lst[1] + '\n')
        f.write(lst[0] + ';' + lst[2] + '\n')