How to rearrange and append data contained in a column of a df in the rows of another dataframe-CodePudding

I have big difficulties in moving data from a dataframe to a new empty one.

These are my dfs

df1:

df2:

I would like to rearrange the values of df1['Close'] in df2['Close','Close 2','Close 3',...,'Close n'] putting in one row let's say 20 'Close' values of df1 (df1 20 first rows), in one row of df2, and proceed with the second row of df2 putting next 20 rows of df1(from 21 to 40). This will happend while ignoring the target columns of both dfs, so I have tried with these parameters as example. I try to explain better.. 5 (inside the range of the for loop) is the numbers of rows in which I would like to dispose the first 100 values from df1['Close'] in df2:

for j in range (0, 5):

    count = 20;
    amazon_df_copy = 
amazon_df_copy.iloc[j:j 1,1:len(amazon_df_copy.columns)].append(normalized_amazon_df.iloc[:count,1:2]);
    count  = 20;

The result is this:

I have tried even in different ways but I could achieve to fill only one column, and in the result the order of my columns is changing and even some of them are missing. When I am done I will delete the NaN remaining values, while the targets will be filled with the one corresponding to the 20 inputs (the close columns, but that's not important now).

I hope it's understandable what I would like to achieve and I hope you can help. :(

CodePudding user response：

You can achieve this by taking the transpose of every 20 columns using reshape likewise:

new_df = pd.DataFrame(df1.Close.values.reshape(-1, 20), 
                columns= ['Close']   ['Close'   str(i) for i in range(1,20)])

If I understood the requirement for the Target column in your comment correctly, you want to store every 20th value including the 1st-row value in the Target column of df1 into the new_df You can use iloc for the same:

new_df['Target'] = df1.iloc[::20].tolist()