CODEWITHSUNDEEP
pythoncsv
Daniel
Daniel

Reputation: 691

Rearrange data in csv with Python

I have a .csv file with the following format:

A   B   C   D   E   F
X1  X2  X3  X4  X5  X6
Y1  Y2  Y3  Y4  Y5  Y6
Z1  Z2  Z3  Z4  Z5  Z6

What I want:

A   X1
B   X2
C   X3
D   X4
E   X5
F   X6
A   Y1
B   Y2
C   Y3
D   Y4
E   Y5
F   Y6
A   Z1
B   Z2
C   Z3
D   Z4
E   Z5
F   Z6

I am unable to wrap my mind around the built-in transpose functions in order to achieve the final result. Any help would be appreciated.

Upvotes: 2

Views: 303

Answers (1)

Alexander
Alexander

Reputation: 109526

You can simply melt your dataframe using pandas:

import pandas as pd

df = pd.read_csv(csv_filename)
>>> pd.melt(df)
   variable value
0         A    X1
1         A    Y1
2         A    Z1
3         B    X2
4         B    Y2
5         B    Z2
6         C    X3
7         C    Y3
8         C    Z3
9         D    X4
10        D    Y4
11        D    Z4
12        E    X5
13        E    Y5
14        E    Z5
15        F    X6
16        F    Y6
17        F    Z6

A pure python solution would be as follows:

file_out_delimiter = ','  # Use '\t' for tab delimited.
with open(filename, 'r') as f, open(filename_out, 'w') as f_out:
    headers = f.readline().split()
    for row in f:
        for pair in zip(headers, row.split()):
            f_out.write(file_out_delimiter.join(pair) + '\n')

resulting in the following file contents:

A,X1
B,X2
C,X3
D,X4
E,X5
F,X6
A,Y1
B,Y2
C,Y3
D,Y4
E,Y5
F,Y6
A,Z1
B,Z2
C,Z3
D,Z4
E,Z5
F,Z6

Upvotes: 7

Related Questions