python - 在 Python 中按列顺序将特定列转换为行并保持其余列相同

Question

我有一个如下所示的数据框:

Screenshot of the dataframe

df = pd.DataFrame({'Date': {0: '01/08/2016 0:00', 1: '01/08/2016 1:00', 2: '01/08/2016 2:00'},
 'amount_1': {0: 29189, 1: 27614, 2: 26579},
 'amount_2': {0: 26277, 1: 24992, 2: 23533},
 'target': {0: 28602, 1: 27112, 2: 25975}})

我希望保持日期和目标列相同，并转置 amount_1 和 amount_2 行，同时保持其顺序。因此，“日期”列之后的 3 列属于每小时的 amount_1，然后是每小时的 amount_2 列。

这是我迄今为止尝试过的。

df_t = df.melt(id_vars=['Date','target']).drop('variable', 1).sort_values(['Date','target'])
df_t.T

我想要的输出是:

df_t = pd.DataFrame({'Date': {0: '01/08/2016 0:00', 1: '01/08/2016 1:00', 2: '01/08/2016 2:00'},
 'amount_1_hour0': {0: 29189, 1: 29189, 2: 29189},
 'amount_1_hour1': {0: 27614, 1: 27614, 2: 27614},
 'amount_1_hour2': {0: 26579, 1: 26579, 2: 26579},
 'amount_2_hour0': {0: 26277, 1: 26277, 2: 26277},
 'amount_2_hour1': {0: 24992, 1: 24992, 2: 24992},
 'amount_2_hour2': {0: 23533, 1: 23533, 2: 23533},
 'target': {0: 28602, 1: 27112, 2: 25975}})

Screenshot of the output

Answer 1

我认为你需要:

#convert values to datetimes
df['Date'] = pd.to_datetime(df['Date'])
#create hour column
df['h'] = df['Date'].dt.hour

#melting by 3 columns
df = df.melt(id_vars=['Date','target', 'h'])
#add hours to amounts strings (variable column)
df['variable'] += '_hour' + df['h'].astype(str)
#pivoting
df = df.pivot_table(index=['Date','target'], columns='variable', values='value').reset_index()
#replace missing values per days
df = df.groupby(df['Date'].dt.date).apply(lambda x: x.ffill().bfill())
print (df)
variable                Date  target  amount_1_hour0  amount_1_hour1  \
0        2016-01-08 00:00:00   28602         29189.0         27614.0   
1        2016-01-08 01:00:00   27112         29189.0         27614.0   
2        2016-01-08 02:00:00   25975         29189.0         27614.0   

variable  amount_1_hour2  amount_2_hour0  amount_2_hour1  amount_2_hour2  
0                26579.0         26277.0         24992.0         23533.0  
1                26579.0         26277.0         24992.0         23533.0  
2                26579.0         26277.0         24992.0         23533.0  

编辑:

df = pd.DataFrame({'Date': {0: '01/08/2016 0:00', 1: '01/08/2016 2:00', 2: '01/08/2016 10:00'},
 'amount_1': {0: 29189, 1: 27614, 2: 26579},
 'amount_2': {0: 26277, 1: 24992, 2: 23533},
 'target': {0: 28602, 1: 27112, 2: 25975}})
print (df)
               Date  amount_1  amount_2  target
0   01/08/2016 0:00     29189     26277   28602
1   01/08/2016 2:00     27614     24992   27112
2  01/08/2016 10:00     26579     23533   25975

#convert values to datetimes
df['Date'] = pd.to_datetime(df['Date'])
#create hour column
df['h'] = df['Date'].dt.hour

#melting by 3 columns
df = df.melt(id_vars=['Date','target', 'h'])
#pivoting
df = df.pivot_table(index=['Date','target'], columns=['variable','h'], values='value')
#join MultiIndex with hours
df.columns = df.columns.map(lambda x: f'{x[0]}_hour{x[1]}')
df = df.reset_index()
#replace missing values per days
df = df.groupby(df['Date'].dt.date).apply(lambda x: x.ffill().bfill())
print (df)
                 Date  target  amount_1_hour0  amount_1_hour2  \
0 2016-01-08 00:00:00   28602         29189.0         27614.0   
1 2016-01-08 02:00:00   27112         29189.0         27614.0   
2 2016-01-08 10:00:00   25975         29189.0         27614.0   

   amount_1_hour10  amount_2_hour0  amount_2_hour2  amount_2_hour10  
0          26579.0         26277.0         24992.0          23533.0  
1          26579.0         26277.0         24992.0          23533.0  
2          26579.0         26277.0         24992.0          23533.0