pandas: convert a CSV series into a data frame

Question

I'm new to pandas so apologies for what I think is a trivial question, but I can't quite find the relevant function for this:

I've got a file which consists of essentially 12 different data series, with the nth element of each series grouped together; i.e.

series_A_data0
series_B_data0
series_C_data0
...
series_L_data0
series_A_data1
series_B_data1
series_C_data1
...

I can import this into pandas as a single column data frame, but how can I get it into a 12-column data series?

For reference, currently I'm doing:

data = pd.read_csv(file)
data.head(14)

0   17655029760
1    1529585664
2    1598763008
3    4936196096
4    2192232448
5    2119827456
6    2143997952
7    1549099008
8    1593683968
9    1361498112
10   1514512384
11   1346588672
12  17939451904
13   1544957952

Answer 1

I don't know if there is a simpler method, but if you can construct a comparable series with the desired column names and index values, you can use pd.pivot:

Suppose you have 3 times the 12 values, creating a dummy example:

data = pd.Series(np.random.randn(12*3))

Now you can construct the desired columns and indices as follows:

col = pd.Series(np.tile(list('ABCDEFGHIJKL'),3))
idx = pd.Series(np.repeat(np.arange(3), 12))

And now:

In [18]: pd.pivot(index=idx, columns=col, values=data.values)
Out[18]:
          A         B         C         D         E         F         G  \
0  1.296702  0.270532 -0.645502  0.213300 -0.224421 -0.634656 -2.362567
1 -1.986403  1.006665 -1.167412 -0.697443 -1.394925 -0.365205 -1.468349
2  0.689492 -0.410681  0.378916  1.552068  0.144651 -0.419082 -0.433970

          H         I         J         K         L
0  2.102229  0.538711 -0.839540 -0.066535  1.154742
1 -1.090374 -1.344588  0.515923 -0.050190 -0.163259
2 -0.235364  0.296751  0.456884  0.237697  1.089476

PS: for some reason just using data instead of data.values does not work.

---

You can also do it with unstack as @TomAugspurger explained:

midx = pd.MultiIndex.from_tuples(zip(idx, col))
data.index = midx
data.unstack()

Answer 2

Do you know that the series will always be in the same order? If so, I'd create a MultiIndex, and the unstack from that. Just read in the Series like you've done. I'll work with this data frame:

In [31]: df = pd.DataFrame(np.random.randn(24))

In [32]: df
Out[32]: 
           0
0  -1.642765
1   1.369409
2  -0.732588
3   0.357242
4  -1.259126
5   0.851803
6  -1.582394
7  -0.508507
8   0.123032
9   0.421857
10 -0.524147
11  0.381085
12  1.286025
13 -0.983004
14  0.813764
15 -0.203370
16 -1.107230
17  1.855278
18 -2.041401
19  1.352107
20 -1.630252
21 -0.326678
22 -0.080991
23  0.438606

In [33]: import itertools as it

In [34]: series_id = it.cycle(list('abcdefghijkl'))  # first 12 letters.

In [60]: idx = pd.MultiIndex.from_tuples(zip(series_id, df.index.repeat(12)[:len(df)]))

We need to repeat the index so that the first observation for each Series is at index 0. Now set that as the index and unstack.

In [61]: df.index = idx

In [62]: df
Out[62]: 
            0
a 0 -1.642765
b 0  1.369409
c 0 -0.732588
d 0  0.357242
e 0 -1.259126
f 0  0.851803
g 0 -1.582394
h 0 -0.508507
i 0  0.123032
j 0  0.421857
k 0 -0.524147
l 0  0.381085
a 1  1.286025
b 1 -0.983004
c 1  0.813764
d 1 -0.203370
e 1 -1.107230
f 1  1.855278
g 1 -2.041401
h 1  1.352107
i 1 -1.630252
j 1 -0.326678
k 1 -0.080991
l 1  0.438606

[24 rows x 1 columns]

In [74]: df.unstack(0)[0]
Out[74]: 
          a         b         c         d         e         f         g  \
0 -1.642765  1.369409 -0.732588  0.357242 -1.259126  0.851803 -1.582394   
1  1.286025 -0.983004  0.813764 -0.203370 -1.107230  1.855278 -2.041401   

          h         i         j         k         l  
0 -0.508507  0.123032  0.421857 -0.524147  0.381085  
1  1.352107 -1.630252 -0.326678 -0.080991  0.438606  

[2 rows x 12 columns]

The unstack(0) say to move the outer index labels to the columns.