We want to add the values of several different rows into one single row. In the image you can see an example of want we want to do, on the left (column ABC) the data we have, on the right the data we want.
We have a large dataset and thus want to write a script. Currently we have a pandas dataframe. We want to add five rows into one.
Does anyone have a simple solution?
You can do this:
inport pandas as pd
# reads an 1 Dimensional List and reads it as columns
pd.DataFrame([
[j for j in i for i in df.values] # makes 2D matrix of all values to 1D list
])
the [] in (pd.DataFrame([...])) means that the first row is the following data -> horizontal formatting
Here's a way you can try:
from itertools import product
# sample data
df = pd.DataFrame(np.random.randint(1, 10, size=9).reshape(-1, 3), columns=['X','Y','Z'])
X Y Z
0 2 6 5
1 5 6 2
2 2 4 5
# get all values
total_values = df.count().sum()
# existing column name
cols = df.columns
nums = [1,2,3]
# create new column names
new_cols = ['_'.join((str(i) for i in x)) for x in list(product(cols, nums))]
df2 = pd.DataFrame(df.values.reshape(-1, total_values), columns=new_cols)
X_1 X_2 X_3 Y_1 Y_2 Y_3 Z_1 Z_2 Z_3
0 2 6 5 5 6 2 2 4 5
I'd do this:
import pandas as pd, numpy as np
df=pd.DataFrame(np.arange(1,10).reshape(3,3),columns=["X","Y","Z"])
print(df)
X Y Z
0 1 2 3
1 4 5 6
2 7 8 9
dat = df.to_numpy()
d = np.column_stack([dat[:,x].reshape(1,dat.shape[0]) for x in range(dat.shape[1])])
pd.DataFrame(d,columns=(x+str(y) for x in df.columns for y in range(len(df)) ))
X0 X1 X2 Y0 Y1 Y2 Z0 Z1 Z2
0 1 4 7 2 5 8 3 6 9
Assuming this is a numpy array. (if its a csv you can read in as numpy array)
yourArray.flatten(order='C')
