how to flatten certain range of columns into one in python?

user_11834687 0
Viewed 208

I have a data table with a dimension of 214 rows and 972 columns.

I want to pick up every 108 columns and then flatten into one column.

Then the output table would have a dimension of 23112 (=214*108) rows and 9 columns.

I have tried pd.concat and pd.flatten something etc.,,,, but I can only flatten whole columns of table into one column.

So to be simple, below is the toy table. Pick up every two columns and flatten them into one column. so I want to reshape; [ 5*6 ] to [ 10*3 ]

a  b  c  d  e  f 
g  h  i  j  k  l
m  n  o  p  q  r 
s  t  u  v  w  x
y  z  1  2  3  4

This would be converted to

a  c  e
g  i  k
m  o  q
s  u  w
y  1  3
b  d  f
h  j  l
n  p  r
t  v  x
z  2  4

I am really new to python,,,, too hard to figure it out,, Thank you for your help!

pandas flatten datatable python
4 Answers

IIUC:

print (pd.concat([pd.DataFrame(df.iloc[:,::2].values),
                  pd.DataFrame(df.iloc[:,1::2].values)])
       .reset_index(drop=True))

Or generalize with a list comprehension with number n:

n=2
print (pd.concat([pd.DataFrame(df.iloc[:,i::n].values) for i in range(n)])
       .reset_index(drop=True))

   0  1  2
0  a  c  e
1  g  i  k
2  m  o  q
3  s  u  w
4  y  1  3
5  b  d  f
6  h  j  l
7  n  p  r
8  t  v  x
9  z  2  4
user_92844230

You can also use , np.reshape:

n = 2 #replace n with your factor
pd.DataFrame(np.reshape(df.to_numpy().T,(df.shape[1]//n,df.shape[0]*n)).T)

Or even without reshape using Fortran-like index order

pd.DataFrame(np.reshape(df.to_numpy(),(df.shape[0]*n,df.shape[1]//n),order='F'))

   0  1  2
0  a  c  e
1  g  i  k
2  m  o  q
3  s  u  w
4  y  1  3
5  b  d  f
6  h  j  l
7  n  p  r
8  t  v  x
9  z  2  4
user_98406370

Using list comprehension, can be done in one line:

cols_to_flatten = 2 #change this as per your requirement
pd.concat([pd.concat([df[col] for col in df.columns[i:i+cols_to_flatten]], ignore_index=True) for i in range(0, df.shape[1], cols_to_flatten)], axis=1)
user_73212010

Here is how I would have done it before having read other folks' answers to your question:

import pandas as pd


def concat_every(frame, every):
    frame = frame.copy()
    new_df = pd.DataFrame(
        columns=range(len(frame.columns)//every)
    )
    for c in new_df.columns:
        col = frame.columns
        new_df[c] = pd.concat(
            [frame.pop(col[i]) for i in range(every)],
            ignore_index=True
        )
    return new_df


df = pd.DataFrame({
    0:['a','g','m','s','y'],
    1:['b','h','n','t','z'],
    2:['c','i','o','u',1],
    3:['d','j','p','v',2],
    4:['e','k','q','w',3],
    5:['f','l','r','x',4],
})

df1 = concat_every(df, 2)

print(df)
print(df1)

Python Tutor Link To Code

user_112285410
Related