重塑数据帧并基于其他24列修改列

Question

我有一个excel有26列

Date，Unique ID和H01，H02，H03…H24

这里H{n}表示小时，即对于UIDsome_code在19/7/2017 01.00.00的值是199。在19/7/2017 02.00.00时，值为7等

+--------------------+---------------+----------+---------------+
|       Date         | UID           | H01      | H02           |
+--------------------+---------------+----------+---------------+
| 19/7/2017 00.00.00 | some_code     |      199 |             7 |
| 19/7/2017 00.00.00 | another_code  |      164 |            18 |
| 19/7/2017 00.00.00 | new_code      |      209 |             1 |
| 19/7/2017 00.00.00 | code_5        |       85 |             4 |
| 19/7/2017 00.00.00 | what          |       45 |             6 |

我正在阅读excel并创建一个类似于上面的数据框

我想修改这个数据帧，这样我可以在下面看到

+--------------------+---------------+----------+
|       Date         | UID           | Value    |
+--------------------+---------------+----------+
| 19/7/2017 01.00.00 | some_code     |      199 |
| 19/7/2017 02.00.00 | some_code     |        7 |
| 19/7/2017 03.00.00 | some_code     |      ... |
.................................................
.................................................
| 19/7/2017 00.00.00 | some_code     |      ... |
| 19/7/2017 01.00.00 | another_code  |      164 |
| 19/7/2017 02.00.00 | another_code  |       18 |
| 19/7/2017 03.00.00 | another_code  |       ...|
.................................................
.................................................
| 19/7/2017 00.00.00 | another_code  |       ...|

我对Python和Pandas还不熟悉，无法理解stack/unstack/pivot

Answer 1

您可以使用：

• 第一次转换Date^{}
• 通过^{}创建MultiIndex—所有其他列都是H列
• ^{}数字并转换为^{}
• 按^{}重塑形状
• 将带有timedeltas的列添加到dates，并通过^{}将其删除

---

df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%Y %H.%M.%S')
df = df.set_index(['Date','UID'])
df.columns=pd.to_timedelta(df.columns.str.extract('(\d+)',expand=False).astype(int),unit='H')
df = df.stack().reset_index(name='Value')
df['Date'] = df['Date'] + df['level_2']
df = df.drop('level_2', axis=1)
print (df)
                 Date           UID  Value
0 2017-07-19 01:00:00     some_code    199
1 2017-07-19 02:00:00     some_code      7
2 2017-07-19 01:00:00  another_code    164
3 2017-07-19 02:00:00  another_code     18
4 2017-07-19 01:00:00      new_code    209
5 2017-07-19 02:00:00      new_code      1
6 2017-07-19 01:00:00        code_5     85
7 2017-07-19 02:00:00        code_5      4
8 2017-07-19 01:00:00          what     45
9 2017-07-19 02:00:00          what      6

对于相同格式的日期，添加^{}：

...
df['Date'] = (df['Date'] + df['level_2']).dt.strftime('%d/%m/%Y %H.%M.%S')
df = df.drop('level_2', axis=1)
print (df)
                  Date           UID  Value
0  19/07/2017 01.00.00     some_code    199
1  19/07/2017 02.00.00     some_code      7
2  19/07/2017 01.00.00  another_code    164
3  19/07/2017 02.00.00  another_code     18
4  19/07/2017 01.00.00      new_code    209
5  19/07/2017 02.00.00      new_code      1
6  19/07/2017 01.00.00        code_5     85
7  19/07/2017 02.00.00        code_5      4
8  19/07/2017 01.00.00          what     45
9  19/07/2017 02.00.00          what      6