Python Pandas Dataframe - 如何將多行row依指定欄位轉換為1行row - How to combine multiple rows to one row -

版本相關資訊：

System version ： Windows 10 64-bit
Python version ： Python 3.6.0 :: Anaconda 4.3.1 (64-bit)
Pandas version ： 0.21.1

How do I get from

  device_id  temp_a  temp_b  temp_c
0         0     0.2     0.8     0.6
1         0     0.1     0.9     0.4
2         1     0.3     0.7     0.2
3         2     0.5     0.5     0.1
4         2     0.1     0.9     0.4
5         2     0.7     0.3     0.9

to

  device_id  temp_a  temp_b  temp_c  temp_a_1  temp_b_1  temp_c_1  temp_a_2  \
0         0     0.2     0.8     0.6       0.1       0.9       0.4       NaN   
1         1     0.3     0.7     0.2       NaN       NaN       NaN       NaN   
2         2     0.5     0.5     0.1       0.1       0.9       0.4       0.7   

   temp_b_2  temp_c_2  
0       NaN       NaN  
1       NaN       NaN  
2       0.3       0.9

Code:

df_raw = pd.DataFrame({'device_id' : ['0','0','1','2','2','2'],
                   'temp_a'    : [0.2,0.1,0.3,0.5,0.1,0.7],
                   'temp_b'    : [0.8,0.9,0.7,0.5,0.9,0.3],
                   'temp_c'    : [0.6,0.4,0.2,0.1,0.4,0.9],
              })

print(df_raw)

df_except = pd.DataFrame({'device_id' : ['0','1','2'],
                          'temp_a':[0.2,0.3,0.5],
                          'temp_b':[0.8,0.7,0.5],
                          'temp_c':[0.6,0.2,0.1],
                          'temp_a_1':[0.1,None,0.1],
                          'temp_b_1':[0.9,None,0.9],
                          'temp_c_1':[0.4,None,0.4],
                          'temp_a_2':[None,None,0.7],
                          'temp_b_2':[None,None,0.3],
                          'temp_c_2':[None,None,0.9],

              })
df_except2 = df_except[['device_id','temp_a','temp_b','temp_c','temp_a_1','temp_b_1','temp_c_1','temp_a_2','temp_b_2','temp_c_2']]
print(df_except2)

# Method1
g = df_raw.groupby('device_id').cumcount()
df = df_raw.set_index(['device_id', g]).unstack().sort_index(axis=1, level=1)
df.columns = ['{}_{}'.format(i,j) if j != 0 else '{}'.format(i) for i, j in df.columns]
df = df.reset_index()
print (df)

# Method2
import numpy as np
device_id_list = df_raw['device_id'].tolist()
device_id_list = list(np.unique(device_id_list))

append_df = pd.DataFrame()
for device_id in device_id_list:
    tmp_df = df_raw.query('device_id=="%s"'%(device_id))

    if len(tmp_df)>1:
        # 拆成單筆row存到list
        one_raw_list=[]
        for i in range(0,len(tmp_df)):
            one_raw_df = tmp_df.iloc[i:i+1]
            one_raw_list.append(one_raw_df)

        # 用來暫存做concat後的資料
        tmp_combine_df = pd.DataFrame()
        for i in range(0,len(one_raw_list)-1):
            # 取得與整理下個row
            next_raw = one_raw_list[i+1].drop(columns=['device_id']).reset_index(drop=True)
            new_name_list=[]
            for old_name in list(next_raw.columns):
                new_name_list.append(old_name+'_'+str(i+1))
            next_raw.columns = new_name_list

            if i==0:
                # 整理第1筆raw
                current_raw = one_raw_list[i].reset_index(drop=True)
                tmp_combine_df = pd.concat([current_raw, next_raw], axis=1)
            else:
                # 與先前整理好的raw做concat
                tmp_combine_df = pd.concat([tmp_combine_df, next_raw], axis=1)
        tmp_df = tmp_combine_df
    tmp_df_columns = tmp_df.columns
    append_df_columns = append_df.columns
    append_df = pd.concat([append_df,tmp_df],ignore_index =True)
    if  len(tmp_df_columns) > len(append_df_columns):
        append_df = append_df[tmp_df_columns]
    else:
        append_df = append_df[append_df_columns]
print(append_df)

​