Rearrange data in csv with Python

Question

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.

Alexander · Accepted Answer · 2017-08-15 01:13:47Z

7

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

edited Aug 15, 2017 at 1:13

answered Aug 14, 2017 at 20:15

Alexander

111k32 gold badges212 silver badges208 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Derek Over a year ago

Just to confirm, the pandas melt function takes the original columns and turns them into rows?

Alexander Over a year ago

Yes, but notice the default ordering is different in case that is important to you.

Anton vBR Over a year ago

Nice solutions +1, however I'd not put an extra blank space here: f_out.write(", ".join(pair) + '\n') and maybe join them with "\t" as it looks like that's what OP has?

Alexander Over a year ago

@AntonvBR Yes, but tabs are hard to see when displayed on SO. I've made it a little more clear.

Collectives™ on Stack Overflow

Rearrange data in csv with Python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related