How do I concatenate groups of 3 columns into one in a large pandas DataFrame?

user_7177790 0
Viewed 118

For example, I have:

    0 1 2   0 1 2     0     1      2        ...
0 | a c e | r t v | alpha gamma   epsilon   ...
1 | b d f | s u w | beta  delta   zeta      ...

And I want to retrieve:

    0   1     2     ...
0 | a | r | alpha   ...
1 | b | s | beta    ...
2 | c | t | gamma   ...
3 | d | u | delta   ...
4 | e | v | epsilon ...
5 | f | w | zeta    ...

My DataFrames are way larger than this, so a general solution would be much appreciated. For example, if I have 300 columns with 10000 rows each, I'd like to retrieve 100 columns with 30000 rows each. The files are generated through Fortran code, which by default groups them in sets of 3 columns. I'm currently reading them with: 

    pd.concat((pd.read_csv(file, sep='\s+', header=None, skiprows=0, 
               engine='c', dtype=np.float64, low_memory=False) 
               for file in list_glob), axis='columns')

Thanks!

pandas python
1 Answers

Let's try numpy's reshape and transpose:

pd.DataFrame(df.values.reshape(len(df),3,-1)
               .transpose(2,0,1)
               .reshape(3*len(df),-1)
            )

Output:

   0  1        2
0  a  r    alpha
1  b  s     beta
2  c  t    gamma
3  d  u    delta
4  e  v  epsilon
5  f  w     zeta

You can manipulate the data read from read_csv before concatenation:

pd.concat((pd.read_csv(file, sep='\s+', header=None, skiprows=0, 
                       engine='c', dtype=np.float64, low_memory=False)
             .melt()[['value']]                    # turn each dataframe into single-column one
           for file in list_glob), axis='columns')
user_42384080
Related