How to convert two rows of data into a single row-CodePudding

I want convert below data into one Using pandas

Orginal data

ID Name m1 m2 m3
1   X   2  6  6
1   Y   1  2  3
2   A   2  4  7
2   y   5  6  7

I want To covert into below format using pandas libray

ID Name1 m1 m2 m3 Name2 m1 m2 m3
1  X     2  6  6  Y     1  2  3
2  A     2  4  7  y     6  6  7

CodePudding user response：

Let's assume this is your data:

data = {'ID':[1, 1, 2, 2],
        'Name':['X', 'Y', 'A', 'y'],
        'm1':[2, 1, 2, 5], 'm2':[6,2,4,6], 
        'm3':[6, 3, 7, 7] }
df = pd.DataFrame(data)

Step 1: Sort the data by ID:

df = df.sort_values(by=['ID'])

Step 2: drop duplicates and keep the first records

df1 = df.drop_duplicates(subset=['ID'], keep='first')

Step 3: again drop duplicates but keep the last records

df2 = df.drop_duplicates(subset=['ID'], keep='last')

Step 4: finally, merge the two dataframe on the same ID

df = df1.merge(df2, on='ID')

Expected output would be look like:

    ID  Name_x  m1_x    m2_x    m3_x    Name_y  m1_y    m2_y    m3_y
0   1   X        2       6      6        Y       1       2       3
1   2   A        2       4      7        y       5       6       7