代码之家  ›  专栏  ›  技术社区  ›  matteo

在python中每3列添加小时值

pandas python
  •  1
  • matteo  · 技术社区  · 6 年前

    想象一下有一个原始数据帧,如下所示:

    为了能够处理数据,我想拥有的是重新排列数据,以便每3列(代表每天的小时值)创建一个新行,日期为 时间为 值(例如 2015-05-31 00:00:00 , 2015-05-31 01:00:00 , 2015-05-31 02:00:00 ,etc.)以便仅以4列结束: date>, tmin>, tmax>,and nsum 。

    这里是导入的csv的原始字典(只有几行):。 

    {'Date': {0: '2015-04-30', 1: '2015-05-01', 2: '2015-05-02', 3: '2015-05-03', 4: '2015-05-04'}, 'T min °C': {0: 11.7, 1: 8.3, 2: 8.3, 3: 11.6, 4: 12.4}, 'T max °C': {0: 11.9, 1: 8.9, 2: 8.4, 3: 11.8, 4: 12.7}, 'N sum mm': {0: 0.0, 1: 0.0, 2: 0.6, 3: 1.9, 4: 0.0}, 'T min °C.1': {0: 11.6, 1: 8.0, 2: 8.3, 3: 11.4, 4: 12.4}, 'T max °C.1': {0: 11.8, 1: 8.2, 2: 8.3, 3: 11.6, 4: 12.4}, 'N sum mm.1': {0: 0.0, 1: 0.1, 2: 0.6, 3: 0.3, 4: 0.0}, 'T min °C.2': {0: 10.2, 1: 7.9, 2: 8.2, 3: 11.1, 4: 12.2}, 'T max °C.2': {0: 11.2, 1: 8.1, 2: 8.3, 3: 11.4, 4: 12.3}, 'N sum mm.2': {0: 0.0, 1: 0.0, 2: 1.5, 3: 0.2, 4: 0.0}, 'T min °C.3': {0: 9.2, 1: 7.5, 2: 8.1, 3: 11.0, 4: 12.1}, 'T max °C.3': {0: 9.8, 1: 7.8, 2: 8.2, 3: 11.1, 4: 12.2}, 'N sum mm.3': {0: 0.0, 1: 0.0, 2: 0.4, 3: 0.0, 4: 0.0}, 'T min °C.4': {0: 8.8, 1: 7.0, 2: 8.2, 3: 10.8, 4: 12.0}, 'T max °C.4': {0: 9.2, 1: 7.5, 2: 8.2, 3: 10.9, 4: 12.1}, 'N sum mm.4': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.1, 4: 0.0}, 'T min °C.5': {0: 8.4, 1: 7.0, 2: 8.2, 3: 10.6, 4: 11.9}, 'T max °C.5': {0: 8.6, 1: 7.1, 2: 8.3, 3: 10.8, 4: 12.1}, 'N sum mm.5': {0: 0.1, 1: 0.0, 2: 0.0, 3: 0.2, 4: 0.0}, 'T min °C.6': {0: 8.6, 1: 6.9, 2: 8.1, 3: 10.5, 4: 11.8}, 'T max °C.6': {0: 8.7, 1: 7.0, 2: 8.3, 3: 10.6, 4: 11.9}, 'N sum mm.6': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.1, 4: 0.0}, 'T min °C.7': {0: 8.5, 1: 6.8, 2: 8.4, 3: 10.4, 4: 11.8}, 'T max °C.7': {0: 8.7, 1: 7.0, 2: 8.9, 3: 10.5, 4: 12.0}, 'N sum mm.7': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.2, 4: 0.2}, 'T min °C.8': {0: 8.4, 1: 7.0, 2: 9.1, 3: 10.6, 4: 12.0}, 'T max °C.8': {0: 8.4, 1: 7.2, 2: 10.8, 3: 10.8, 4: 12.8}, 'N sum mm.8': {0: 1.4, 1: 0.0, 2: 0.0, 3: 0.1, 4: 0.0}, 'T min °C.9': {0: 7.0, 1: 7.3, 2: 11.2, 3: 10.9, 4: 13.0}, 'T max °C.9': {0: 8.3, 1: 7.8, 2: 12.5, 3: 11.4, 4: 13.5}, 'N sum mm.9': {0: 2.9, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.10': {0: 6.7, 1: 8.0, 2: 12.3, 3: 11.5, 4: 13.6}, 'T max °C.10': {0: 6.9, 1: 8.2, 2: 13.9, 3: 12.3, 4: 14.8}, 'N sum mm.10': {0: 2.9, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.11': {0: 6.5, 1: 8.2, 2: 14.5, 3: 12.3, 4: 15.0}, 'T max °C.11': {0: 6.6, 1: 8.5, 2: 16.1, 3: 12.7, 4: 15.8}, 'N sum mm.11': {0: 3.7, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.12': {0: 6.7, 1: 8.3, 2: 16.3, 3: 12.8, 4: 15.9}, 'T max °C.12': {0: 7.3, 1: 8.4, 2: 17.6, 3: 13.4, 4: 16.3}, 'N sum mm.12': {0: 1.1, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.13': {0: 7.6, 1: 8.4, 2: 17.8, 3: 13.6, 4: 16.3}, 'T max °C.13': {0: 8.8, 1: 8.5, 2: 18.6, 3: 13.9, 4: 17.0}, 'N sum mm.13': {0: 0.0, 1: 0.1, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.14': {0: 9.5, 1: 8.6, 2: 19.2, 3: 14.1, 4: 17.0}, 'T max °C.14': {0: 11.4, 1: 9.1, 2: 19.8, 3: 14.3, 4: 17.3}, 'N sum mm.14': {0: 0.0, 1: 0.3, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.15': {0: 11.4, 1: 9.0, 2: 20.0, 3: 14.4, 4: 16.7}, 'T max °C.15': {0: 12.6, 1: 9.1, 2: 20.5, 3: 15.0, 4: 17.0}, 'N sum mm.15': {0: 0.0, 1: 0.4, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.16': {0: 12.6, 1: 9.1, 2: 20.0, 3: 14.8, 4: 16.8}, 'T max °C.16': {0: 13.4, 1: 9.3, 2: 20.4, 3: 14.9, 4: 17.1}, 'N sum mm.16': {0: 0.0, 1: 0.2, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.17': {0: 13.7, 1: 9.2, 2: 19.6, 3: 14.6, 4: 16.3}, 'T max °C.17': {0: 14.1, 1: 9.3, 2: 20.0, 3: 14.7, 4: 16.5}, 'N sum mm.17': {0: 0.0, 1: 0.1, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.18': {0: 12.9, 1: 8.9, 2: 17.7, 3: 14.2, 4: 16.0}, 'T max °C.18': {0: 13.9, 1: 9.1, 2: 19.4, 3: 14.6, 4: 16.4}, 'N sum mm.18': {0: 0.0, 1: 0.1, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.19': {0: 11.0, 1: 8.7, 2: 16.0, 3: 14.0, 4: 15.3}, 'T max °C.19': {0: 12.2, 1: 8.9, 2: 17.9, 3: 14.1, 4: 16.1}, 'N sum mm.19': {0: 0.0, 1: 0.2, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.20': {0: 9.9, 1: 8.6, 2: 14.6, 3: 13.2, 4: 14.5}, 'T max °C.20': {0: 10.9, 1: 8.7, 2: 16.0, 3: 14.0, 4: 15.4}, 'N sum mm.20': {0: 0.0, 1: 0.7, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.21': {0: 10.2, 1: 8.6, 2: 13.8, 3: 12.8, 4: 14.2}, 'T max °C.21': {0: 10.5, 1: 8.6, 2: 14.9, 3: 13.4, 4: 14.9}, 'N sum mm.21': {0: 0.0, 1: 1.5, 2: 0.2, 3: 0.0, 4: 0.0}, 'T min °C.22': {0: 9.1, 1: 8.5, 2: 12.1, 3: 12.8, 4: 13.8}, 'T max °C.22': {0: 10.2, 1: 8.5, 2: 13.2, 3: 12.9, 4: 14.3}, 'N sum mm.22': {0: 0.0, 1: 1.3, 2: 0.7, 3: 0.0, 4: 0.0}, 'T min °C.23': {0: 9.1, 1: 8.4, 2: 11.9, 3: 12.7, 4: 13.4}, 'T max °C.23': {0: 9.6, 1: 8.4, 2: 12.7, 3: 12.8, 4: 14.1}, 'N sum mm.23': {0: 0.0, 1: 1.3, 2: 2.1, 3: 0.0, 4: 0.0}}

    


    2015-05-31 00:00:002015-05-31 01:00:002015-05-31 02:00:00DateTminTmaxNsum
    


    


    {'Date': {0: '2015-04-30', 1: '2015-05-01', 2: '2015-05-02', 3: '2015-05-03', 4: '2015-05-04'}, 'T min °C': {0: 11.7, 1: 8.3, 2: 8.3, 3: 11.6, 4: 12.4}, 'T max °C': {0: 11.9, 1: 8.9, 2: 8.4, 3: 11.8, 4: 12.7}, 'N sum mm': {0: 0.0, 1: 0.0, 2: 0.6, 3: 1.9, 4: 0.0}, 'T min °C.1': {0: 11.6, 1: 8.0, 2: 8.3, 3: 11.4, 4: 12.4}, 'T max °C.1': {0: 11.8, 1: 8.2, 2: 8.3, 3: 11.6, 4: 12.4}, 'N sum mm.1': {0: 0.0, 1: 0.1, 2: 0.6, 3: 0.3, 4: 0.0}, 'T min °C.2': {0: 10.2, 1: 7.9, 2: 8.2, 3: 11.1, 4: 12.2}, 'T max °C.2': {0: 11.2, 1: 8.1, 2: 8.3, 3: 11.4, 4: 12.3}, 'N sum mm.2': {0: 0.0, 1: 0.0, 2: 1.5, 3: 0.2, 4: 0.0}, 'T min °C.3': {0: 9.2, 1: 7.5, 2: 8.1, 3: 11.0, 4: 12.1}, 'T max °C.3': {0: 9.8, 1: 7.8, 2: 8.2, 3: 11.1, 4: 12.2}, 'N sum mm.3': {0: 0.0, 1: 0.0, 2: 0.4, 3: 0.0, 4: 0.0}, 'T min °C.4': {0: 8.8, 1: 7.0, 2: 8.2, 3: 10.8, 4: 12.0}, 'T max °C.4': {0: 9.2, 1: 7.5, 2: 8.2, 3: 10.9, 4: 12.1}, 'N sum mm.4': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.1, 4: 0.0}, 'T min °C.5': {0: 8.4, 1: 7.0, 2: 8.2, 3: 10.6, 4: 11.9}, 'T max °C.5': {0: 8.6, 1: 7.1, 2: 8.3, 3: 10.8, 4: 12.1}, 'N sum mm.5': {0: 0.1, 1: 0.0, 2: 0.0, 3: 0.2, 4: 0.0}, 'T min °C.6': {0: 8.6, 1: 6.9, 2: 8.1, 3: 10.5, 4: 11.8}, 'T max °C.6': {0: 8.7, 1: 7.0, 2: 8.3, 3: 10.6, 4: 11.9}, 'N sum mm.6': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.1, 4: 0.0}, 'T min °C.7': {0: 8.5, 1: 6.8, 2: 8.4, 3: 10.4, 4: 11.8}, 'T max °C.7': {0: 8.7, 1: 7.0, 2: 8.9, 3: 10.5, 4: 12.0}, 'N sum mm.7': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.2, 4: 0.2}, 'T min °C.8': {0: 8.4, 1: 7.0, 2: 9.1, 3: 10.6, 4: 12.0}, 'T max °C.8': {0: 8.4, 1: 7.2, 2: 10.8, 3: 10.8, 4: 12.8}, 'N sum mm.8': {0: 1.4, 1: 0.0, 2: 0.0, 3: 0.1, 4: 0.0}, 'T min °C.9': {0: 7.0, 1: 7.3, 2: 11.2, 3: 10.9, 4: 13.0}, 'T max °C.9': {0: 8.3, 1: 7.8, 2: 12.5, 3: 11.4, 4: 13.5}, 'N sum mm.9': {0: 2.9, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.10': {0: 6.7, 1: 8.0, 2: 12.3, 3: 11.5, 4: 13.6}, 'T max °C.10': {0: 6.9, 1: 8.2, 2: 13.9, 3: 12.3, 4: 14.8}, 'N sum mm.10': {0: 2.9, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.11': {0: 6.5, 1: 8.2, 2: 14.5, 3: 12.3, 4: 15.0}, 'T max °C.11': {0: 6.6, 1: 8.5, 2: 16.1, 3: 12.7, 4: 15.8}, 'N sum mm.11': {0: 3.7, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.12': {0: 6.7, 1: 8.3, 2: 16.3, 3: 12.8, 4: 15.9}, 'T max °C.12': {0: 7.3, 1: 8.4, 2: 17.6, 3: 13.4, 4: 16.3}, 'N sum mm.12': {0: 1.1, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.13': {0: 7.6, 1: 8.4, 2: 17.8, 3: 13.6, 4: 16.3}, 'T max °C.13': {0: 8.8, 1: 8.5, 2: 18.6, 3: 13.9, 4: 17.0}, 'N sum mm.13': {0: 0.0, 1: 0.1, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.14': {0: 9.5, 1: 8.6, 2: 19.2, 3: 14.1, 4: 17.0}, 'T max °C.14': {0: 11.4, 1: 9.1, 2: 19.8, 3: 14.3, 4: 17.3}, 'N sum mm.14': {0: 0.0, 1: 0.3, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.15': {0: 11.4, 1: 9.0, 2: 20.0, 3: 14.4, 4: 16.7}, 'T max °C.15': {0: 12.6, 1: 9.1, 2: 20.5, 3: 15.0, 4: 17.0}, 'N sum mm.15': {0: 0.0, 1: 0.4, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.16': {0: 12.6, 1: 9.1, 2: 20.0, 3: 14.8, 4: 16.8}, 'T max °C.16': {0: 13.4, 1: 9.3, 2: 20.4, 3: 14.9, 4: 17.1}, 'N sum mm.16': {0: 0.0, 1: 0.2, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.17': {0: 13.7, 1: 9.2, 2: 19.6, 3: 14.6, 4: 16.3}, 'T max °C.17': {0: 14.1, 1: 9.3, 2: 20.0, 3: 14.7, 4: 16.5}, 'N sum mm.17': {0: 0.0, 1: 0.1, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.18': {0: 12.9, 1: 8.9, 2: 17.7, 3: 14.2, 4: 16.0}, 'T max °C.18': {0: 13.9, 1: 9.1, 2: 19.4, 3: 14.6, 4: 16.4}, 'N sum mm.18': {0: 0.0, 1: 0.1, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.19': {0: 11.0, 1: 8.7, 2: 16.0, 3: 14.0, 4: 15.3}, 'T max °C.19': {0: 12.2, 1: 8.9, 2: 17.9, 3: 14.1, 4: 16.1}, 'N sum mm.19': {0: 0.0, 1: 0.2, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.20': {0: 9.9, 1: 8.6, 2: 14.6, 3: 13.2, 4: 14.5}, 'T max °C.20': {0: 10.9, 1: 8.7, 2: 16.0, 3: 14.0, 4: 15.4}, 'N sum mm.20': {0: 0.0, 1: 0.7, 2: 0.0, 3: 0.0, 4: 0.0}, 'T min °C.21': {0: 10.2, 1: 8.6, 2: 13.8, 3: 12.8, 4: 14.2}, 'T max °C.21': {0: 10.5, 1: 8.6, 2: 14.9, 3: 13.4, 4: 14.9}, 'N sum mm.21': {0: 0.0, 1: 1.5, 2: 0.2, 3: 0.0, 4: 0.0}, 'T min °C.22': {0: 9.1, 1: 8.5, 2: 12.1, 3: 12.8, 4: 13.8}, 'T max °C.22': {0: 10.2, 1: 8.5, 2: 13.2, 3: 12.9, 4: 14.3}, 'N sum mm.22': {0: 0.0, 1: 1.3, 2: 0.7, 3: 0.0, 4: 0.0}, 'T min °C.23': {0: 9.1, 1: 8.4, 2: 11.9, 3: 12.7, 4: 13.4}, 'T max °C.23': {0: 9.6, 1: 8.4, 2: 12.7, 3: 12.8, 4: 14.1}, 'N sum mm.23': {0: 0.0, 1: 1.3, 2: 2.1, 3: 0.0, 4: 0.0}}
    1 回复  |  直到 6 年前
        1
  •  1
  •   jezrael    6 年前

    DatetimeIndex numpy.repeat 

    df = df.set_index('Date')

df = pd.DataFrame(df.values.reshape(-1, 3), 
                  index=pd.to_datetime(np.repeat(df.index, len(df.columns) // 3)),
                  columns=['Tmin', 'Tmax', 'Nsum'])

    hour timedelta 

    df.index += pd.to_timedelta(np.arange(len(df)) % 24, unit='h')
df = df.rename_axis('Date').reset_index()

    print (df.head(30))
                  Date  Tmin  Tmax  Nsum
0  2015-04-30 00:00:00  11.7  11.9   0.0
1  2015-04-30 01:00:00  11.6  11.8   0.0
2  2015-04-30 02:00:00  10.2  11.2   0.0
3  2015-04-30 03:00:00   9.2   9.8   0.0
4  2015-04-30 04:00:00   8.8   9.2   0.0
5  2015-04-30 05:00:00   8.4   8.6   0.1
6  2015-04-30 06:00:00   8.6   8.7   0.0
7  2015-04-30 07:00:00   8.5   8.7   0.0
8  2015-04-30 08:00:00   8.4   8.4   1.4
9  2015-04-30 09:00:00   7.0   8.3   2.9
10 2015-04-30 10:00:00   6.7   6.9   2.9
11 2015-04-30 11:00:00   6.5   6.6   3.7
12 2015-04-30 12:00:00   6.7   7.3   1.1
13 2015-04-30 13:00:00   7.6   8.8   0.0
14 2015-04-30 14:00:00   9.5  11.4   0.0
15 2015-04-30 15:00:00  11.4  12.6   0.0
16 2015-04-30 16:00:00  12.6  13.4   0.0
17 2015-04-30 17:00:00  13.7  14.1   0.0
18 2015-04-30 18:00:00  12.9  13.9   0.0
19 2015-04-30 19:00:00  11.0  12.2   0.0
20 2015-04-30 20:00:00   9.9  10.9   0.0
21 2015-04-30 21:00:00  10.2  10.5   0.0
22 2015-04-30 22:00:00   9.1  10.2   0.0
23 2015-04-30 23:00:00   9.1   9.6   0.0
24 2015-05-01 00:00:00   8.3   8.9   0.0
25 2015-05-01 01:00:00   8.0   8.2   0.1
26 2015-05-01 02:00:00   7.9   8.1   0.0
27 2015-05-01 03:00:00   7.5   7.8   0.0
28 2015-05-01 04:00:00   7.0   7.5   0.0
29 2015-05-01 05:00:00   7.0   7.1   0.0
    推荐文章
    July  ·  如何定义数字间隔,然后四舍五入
    1 年前
    Community wiki  ·  对象名称前的单下划线和双下划线的含义是什么?
    1 年前
    Brian Johnson  ·  为什么在Python中列出字典列表会引发TypeError?[已关闭]
    1 年前
    user026  ·  如何根据特定窗口的平均值(行数)创建新列?
    1 年前
    Ashok Shrestha  ·  需要追踪特定的颜色线并获取坐标
    1 年前
    Nicote Ool  ·  在FastApi和Vue3中获得422
    1 年前
    NeoExceptCodeBad  ·  如果我有很多垂直线,我如何找到它们的边缘?
    1 年前
    Abdulaziz  ·  如何对集合内的列表进行排序[重复]
    1 年前
    user2743931  ·  带有src目录的Python setup.py
    1 年前
    asmgx  ·  为什么合并数据帧不能按照python中的预期方式工作
    1 年前
    关于  
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号