Move every second row to row above in pandas dataframe
concat
on df.iloc[::2]
and df.iloc[1::2]
:
df1= (df.iloc[::2].dropna(axis=1).reset_index(drop=True))
df2 = (df.iloc[1::2].reset_index(drop=True))
print (pd.concat([df1,df2],ignore_index=True,axis=1))
#
0 1 2 3 4 5 6
0 213-1 XL 21 22.0 12.0 232.0 101.32
1 23-0 L 12 23 12.0 232.2 NaN
2 31-0 LS 70 70 23.0 NaN 21.22
master_df = df[~df['C'].isna()].reset_index(drop=True)
master_df[['ID','Name']] = pd.DataFrame(df[df['C'].isna()][['A','B']].reset_index(drop=True), index=master_df.index)
Output
##print(master_df[['ID','Name','A', 'B', 'C', 'D', 'E']])
ID Name A B C D E
0 213-1 XL 21 22.0 12.0 232.0 101.32
1 23-0 L 12 23 12.0 232.2 NaN
2 31-0 LS 70 70 23.0 NaN 21.22
I would use concat
:
new_df = pd.concat((df.iloc[::2, :2].reset_index(drop=True),
df.iloc[1::2].reset_index(drop=True)),
axis=1)
# rename
new_df.columns = ['ID', 'Name'] + new_df.columns[2:].to_list()
Output:
ID Name A B C D E
0 213-1 XL 21 22.0 12.0 232.0 101.32
1 23-0 L 12 23 12.0 232.2 NaN
2 31-0 LS 70 70 23.0 NaN 21.22