How to build a new dataframe with given data frame in python

Question

I was given a dataset like this:

index value
1     2.5
2     3.7
3     2.0
4     4.0
5     6.5
6     1.6
7     3.9
...   ...

I need to transfer the data into this form:

index value1 value2 value3 
1     2.5    3.7    2.0
2     3.7    2.0    4.0
3     2.0    4.0    6.5
4     4.0    6.5    1.6
5     6.5    1.6    3.9
...   ...    ...

Is there any way to do this efficiently? This is a sample of the data, I would have to move like a window of 20 rows of data, and do this process to fill the dataset sequentially.

Thanks!

Anton vBR · Accepted Answer · 2018-04-05 16:13:29Z

Another alternative would be to zip together two shifts and slice from 2 to remove nans:

Recreate dataframe:

import pandas as pd

data = '''\
index value
1     2.5
2     3.7
3     2.0
4     4.0
5     6.5
6     1.6
7     3.9'''

df = pd.read_csv(pd.compat.StringIO(data), sep='\s+').set_index('index')

Solution for 3 columns with zip:

newvalues = list(zip(df['value'].shift(2),df['value'].shift(1),df['value']))
df2 = pd.DataFrame(newvalues[2:],columns=['value1','value2','value3'])

print(df2)

Prints

   value1  value2  value3
0     2.5     3.7     2.0
1     3.7     2.0     4.0
2     2.0     4.0     6.5
3     4.0     6.5     1.6
4     6.5     1.6     3.9

Or a more general approach with n=3:

n = 3
newvalues = list(zip(*(df['value'].shift(i) for i in range(n-1,-1,-1))))
cols = ['value{}'.format(i) for i in range(1,n+1)]
df2 = pd.DataFrame(newvalues[n-1:],columns=cols)

Or using pd.concat(), however this seems to be slower with small dataset.

n = 3
df2 = pd.concat((df['value'].shift(i)[n-1:] for i in range(n-1,-1,-1)), axis=1)
df2.columns = ['value{}'.format(i) for i in range(1,n+1)]

pythonic833 · Accepted Answer · 2018-04-05 15:57:20Z

1

Use .shift

#rename column 'value' to 'value1'
df = df.rename(columns={'value':'value1'})
#perform loop
end_num=5
for i in range(2,end_num):
    df['value' + str(i)] = df['value' + str(i-1)].shift(-1)

output

value1  value2  value3  value4
0   2.5     3.7     2.0     4.0
1   3.7     2.0     4.0     6.5
2   2.0     4.0     6.5     1.6
3   4.0     6.5     1.6     3.9
4   6.5     1.6     3.9     NaN
5   1.6     3.9     NaN     NaN
6   3.9     NaN     NaN     NaN

answered Apr 5, 2018 at 15:57

pythonic833

3,2341 gold badge16 silver badges30 bronze badges

Collectives™ on Stack Overflow

How to build a new dataframe with given data frame in python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related