CODEWITHSUNDEEP
pythonpandas
matteo
matteo

Reputation: 4873

Adding hours values each 3 columns in Python

Imagine to have a raw dataframe like the following:

enter image description here

What I would like to have in order to be able to work on the data is to rearrange it so that every 3 columns (that represent the hourly values of each day) creating a new row with the date time values (e.g. 2015-05-31 00:00:00, 2015-05-31 01:00:00, 2015-05-31 02:00:00, etc.) in order to end up with just 4 columns: Date, Tmin, Tmax, and Nsum.

Here the raw dictionary from the imported CSV (just a few rows):

{'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}}

Upvotes: 1

Views: 36

Answers (1)

jezrael
jezrael

Reputation: 862481

First create DatetimeIndex, then reshape values for 3 columns, create new index by 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'])

Last add hours by converted modulo to timedeltas:

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

Upvotes: 1

Related Questions