Convert column vector into multi-column matrix

user_4796942 0
Viewed 75

I have a column vector with say 30 values (1-30) I would like to try to manipulate this vector so that it becomes a matrix with 5 values in the first column, 10 values in the second and 15 values in the third column. How would I implement this using Pandas or NumPy?

import pandas as pd

#Create data
df = pd.DataFrame(np.linspace(1,20,20))

print(df)

1
2
:
28
29
30

In order to get something like this:

# Manipulate the column vector to make columns where the first column has 5
# the second column has 10 and the last column has 15 values
'T1' 'T2' 'T3'
1    6    16 
2    7    17
3    8    18
4    9    19
5    10   20
NA   11   21
NA   12   22
NA   13   23 
NA   14   24
NA   15   25
NA   NA   26
NA   NA   27
NA   NA   28
NA   NA   29
NA   NA   30
pandas numpy arrays python
2 Answers

Try this by slicing with reindexing:

df['T1'] = df[0][0:5]
df['T2'] = df[0][5:15].reset_index(drop=True)
df['T3'] = df[0][15:].reset_index(drop=True)

Original data before operation:

df = pd.DataFrame(np.linspace(1,30,30))
print(df)

    0
0   1.0
1   2.0
2   3.0
3   4.0
4   5.0
5   6.0
6   7.0
7   8.0
8   9.0
9   10.0
10  11.0
11  12.0
12  13.0
13  14.0
14  15.0
15  16.0
16  17.0
17  18.0
18  19.0
19  20.0
20  21.0
21  22.0
22  23.0
23  24.0
24  25.0
25  26.0
26  27.0
27  28.0
28  29.0
29  30.0

Running new codes:

df['T1'] = df[0][0:5]
df['T2'] = df[0][5:15].reset_index(drop=True)
df['T3'] = df[0][15:].reset_index(drop=True)

print(df)

       0   T1    T2    T3
0    1.0  1.0   6.0  16.0
1    2.0  2.0   7.0  17.0
2    3.0  3.0   8.0  18.0
3    4.0  4.0   9.0  19.0
4    5.0  5.0  10.0  20.0
5    6.0  NaN  11.0  21.0
6    7.0  NaN  12.0  22.0
7    8.0  NaN  13.0  23.0
8    9.0  NaN  14.0  24.0
9   10.0  NaN  15.0  25.0
10  11.0  NaN   NaN  26.0
11  12.0  NaN   NaN  27.0
12  13.0  NaN   NaN  28.0
13  14.0  NaN   NaN  29.0
14  15.0  NaN   NaN  30.0
15  16.0  NaN   NaN   NaN
16  17.0  NaN   NaN   NaN
17  18.0  NaN   NaN   NaN
18  19.0  NaN   NaN   NaN
19  20.0  NaN   NaN   NaN
20  21.0  NaN   NaN   NaN
21  22.0  NaN   NaN   NaN
22  23.0  NaN   NaN   NaN
23  24.0  NaN   NaN   NaN
24  25.0  NaN   NaN   NaN
25  26.0  NaN   NaN   NaN
26  27.0  NaN   NaN   NaN
27  28.0  NaN   NaN   NaN
28  29.0  NaN   NaN   NaN
29  30.0  NaN   NaN   NaN
user_150706970

It took a little time to find out what series is this, and I found that its a triangular series , just a modified one.

tri = lambda x:int((0.25+2*x)**0.5-0.5)

This would give results like:

0 1 1 2 2 2 3 3 3 3 4 4 4 4 4 5 5 5 5 5 5 ...

And after the modification:

modtri = lambda x:int((0.25+2*(x//5))**0.5-0.5)

0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 ...

So each occurrence in normal triangular series repeats 5 times.

The above modtri function would directly map the index starting from 0, to appropriate group ids.

and so after that, this would do the job:

df[0].groupby(modtri).apply(lambda x: pd.Series(x.values)).unstack().T

Full execution:

import pandas as pd
import numpy as np

df = pd.DataFrame(np.linspace(1,30,30))

N = 5 #the increment value

modtri = lambda x:int((0.25+2*(x//N))**0.5-0.5)

df2 = df[0].groupby(modtri).apply(lambda x: pd.Series(x.values)).unstack().T

df2.rename(columns={0: "T1", 1: "T2",2:"T3"},inplace=True)

print(df2)

Output:

     T1    T2    T3
0   1.0   6.0  16.0
1   2.0   7.0  17.0
2   3.0   8.0  18.0
3   4.0   9.0  19.0
4   5.0  10.0  20.0
5   NaN  11.0  21.0
6   NaN  12.0  22.0
7   NaN  13.0  23.0
8   NaN  14.0  24.0
9   NaN  15.0  25.0
10  NaN   NaN  26.0
11  NaN   NaN  27.0
12  NaN   NaN  28.0
13  NaN   NaN  29.0
14  NaN   NaN  30.0
user_147849120
Related