Concatenate columns skipping pasted rows and columns-CodePudding

I expect to describe well want I need. I have a data frame with the same columns name and another column that works as an index. The data frame looks as follows:

df = pd.DataFrame({'ID':[1,1,1,1,1,2,2,2,3,3,3,3],'X':[1,2,3,4,5,2,3,4,1,3,4,5],'Y':[1,2,3,4,5,2,3,4,5,4,3,2]})

df
Out[21]: 
    ID  X  Y
0    1  1  1
1    1  2  2
2    1  3  3
3    1  4  4
4    1  5  5
5    2  2  2
6    2  3  3
7    2  4  4
8    3  1  5
9    3  3  4
10   3  4  3
11   3  5  2

My intention is to copy X as an index or one column (it doesn't matter) and append Y columns from each 'ID' in the following way:

CodePudding user response：

You can try

out = pd.concat([group.rename(columns={'Y': f'Y{name}'}) for name, group in df.groupby('ID')])
out.columns = out.columns.str.replace(r'\d $', '', regex=True)

print(out)

    ID  X    Y    Y    Y
0    1  1  1.0  NaN  NaN
1    1  2  2.0  NaN  NaN
2    1  3  3.0  NaN  NaN
3    1  4  4.0  NaN  NaN
4    1  5  5.0  NaN  NaN
5    2  2  NaN  2.0  NaN
6    2  3  NaN  3.0  NaN
7    2  4  NaN  4.0  NaN
8    3  1  NaN  NaN  5.0
9    3  3  NaN  NaN  4.0
10   3  4  NaN  NaN  3.0
11   3  5  NaN  NaN  2.0

CodePudding user response：

Here's another way to do it:

df_org = pd.DataFrame({'ID':[1,1,1,1,1,2,2,2,3,3,3,3],'X':[1,2,3,4,5,2,3,4,1,3,4,5]})
df = df_org.copy()
for i in set(df_org['ID']):
    df1 = df_org[df_org['ID']==i]
    col = 'Y' str(i)
    df1.columns = ['ID', col]
    df = pd.concat([ df, df1[[col]] ], axis=1)
df.columns = df.columns.str.replace(r'\d $', '', regex=True)
print(df)

Output:

    ID  X    Y    Y    Y
0    1  1  1.0  NaN  NaN
1    1  2  2.0  NaN  NaN
2    1  3  3.0  NaN  NaN
3    1  4  4.0  NaN  NaN
4    1  5  5.0  NaN  NaN
5    2  2  NaN  2.0  NaN
6    2  3  NaN  3.0  NaN
7    2  4  NaN  4.0  NaN
8    3  1  NaN  NaN  1.0
9    3  3  NaN  NaN  3.0
10   3  4  NaN  NaN  4.0
11   3  5  NaN  NaN  5.0