How to flat every n rows in pandas datafrmae

user_6659095 0
Viewed 399

I would like to flat every n rows in a pandas dataframe, e.g., if n=2 then an example would be like

df = pd.DataFrame([[1,2], [3,4], [5,6], [7,8], [9,10], [11,12]])
df.columns = ['a', 'b']
target_df =  pd.DataFrame([[1,2, 3, 4], [5,6, 7, 8], [9,10, 11, 12]])
target_df.columns = ['a1', 'b1', 'a2', 'b2']
print(df, '\n\n', target_df)

    a   b
0   1   2
1   3   4
2   5   6
3   7   8
4   9  10
5  11  12 

    a1  b1  a2  b2
0   1   2   3   4
1   5   6   7   8
2   9  10  11  12

Is there any fast way to do so? Note that the length of the dataframe and n could be arbitrarily large, so hardcoding n is not a good option.

pandas dataframe python
4 Answers

First we get all even and uneven index numbers. Then we select these with loc and concat them over axis=1:

grp1 = df.index%2 == 0 # uneven index
grp2  = df.index%2 == 1 # even index

df = pd.concat([
    df.loc[grp1].reset_index(drop=True), df.loc[grp2].reset_index(drop=True)
], axis=1)

   a   b   a   b
0  1   2   3   4
1  5   6   7   8
2  9  10  11  12
user_90812670

Just use the underlying numpy array directly:

import pandas as pd

df = pd.DataFrame(
    [[1,2], [3,4], [5,6], [7,8], [9,10], [11,12]],
    columns=["a", "b"]
)
df_2 = pd.DataFrame(
    df.values.reshape([-1, 4]),
    columns = ["a1", "b1", "a2", "b2"]
)

df looks like:

    a   b
0   1   2
1   3   4
2   5   6
3   7   8
4   9  10
5  11  12

df_2 looks like: 

   a1  b1  a2  b2
0   1   2   3   4
1   5   6   7   8
2   9  10  11  12

For a generic solution:

def concat_rows(df, n):
    new_cols = [
        f"{col}{i}"
        for i in range(1, n+1)
        for col in df.columns
    ]
    n_cols = len(df.columns)
    new_df = pd.DataFrame(
        df.values.reshape([-1, n_cols*n]),
        columns=new_cols
    )
    return new_df

df_2 = concat_rows(df, 2)
df_3 = concat_rows(df, 3)

df_2 looks as before. df_3 looks like: 

   a1  b1  a2  b2  a3  b3
0   1   2   3   4   5   6
1   7   8   9  10  11  12
user_45857130

You can use numpy hstack,

simple solution:

n = 2
np.hstack((df.values[::n],df.values[1::n]))

array([[ 1,  2,  3,  4],
       [ 5,  6,  7,  8],
       [ 9, 10, 11, 12]])

Convert the above to dataframe using,

n = 2
pd.DataFrame(np.hstack((df.values[::n],df.values[1::n])))

    0   1   2   3
0   1   2   3   4
1   5   6   7   8
2   9   10  11  12

If you want to handle variable n, try

n = 3
l = []
for i in range(n):
    l.append(df.values[i::n])

pd.DataFrame(np.hstack((l)))

Note: This still requires n to be a factor of len(df)

user_62873080

Construct multiindex and assign to index and unstack

n = 2
iix = pd.MultiIndex.from_arrays([np.arange(df.shape[0]) // n, 
                                (np.arange(df.shape[0]) % n)+1])

df1 = df.set_index(iix).unstack().sort_index(level=1, axis=1)

Out[211]:
   a   b   a   b
   1   1   2   2
0  1   2   3   4
1  5   6   7   8
2  9  10  11  12

If you don't want multiindex columns, you may flatten it

df1.columns = df1.columns.map('{0[0]}{0[1]}'.format)

Out[213]:
   a1  b1  a2  b2
0   1   2   3   4
1   5   6   7   8
2   9  10  11  12

If you want other n, change value of n

n = 3
iix = pd.MultiIndex.from_arrays([np.arange(df.shape[0]) // n,
                               (np.arange(df.shape[0]) % n)+1])
df1 = df.set_index(iix).unstack().sort_index(level=1, axis=1)
df1.columns = df1.columns.map('{0[0]}{0[1]}'.format)

Out[215]:
   a1  b1  a2  b2  a3  b3
0   1   2   3   4   5   6
1   7   8   9  10  11  12
user_101892140
Related