CODEWITHSUNDEEP
pythonpython-3.x
Alex Rebell
Alex Rebell

Reputation: 374

Formatting a CSV file, split a column into rows

I have a CSV file, of the following type:

enter image description here

I need to reformat it to the following form:

enter image description here

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.

Upvotes: 0

Views: 219

Answers (3)

rahlf23
rahlf23

Reputation: 9019

I would recommend leveraging df.explode() after modifying Column_B to a list-type:

df = pd.read_csv(text, sep=';')

df['Column_B'] = df['Column_B'].str.split(',')
df = df.explode('Column_B')

df.to_csv('test.csv', sep=';', index=False)

Upvotes: 6

S.B
S.B

Reputation: 16564

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')

Upvotes: 0

ext
ext

Reputation: 301

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')])

Upvotes: 0

Related Questions