0

I have an Excel file containing data from multiple machines. Each second a machine writes it's 14 sensor values in columns with a time stamp. The time stamps are redundant/the same for all machines. The data looks like this:

Time     machine    rpm01     rpm02 .... rpm14
00:01    M1         100       396        321
00:02    M1         200       323        213
........
00:01    M2         123       163        242
00:02    M2         243       128        221
........
00:01    M60        134       222        231
00:02    M60        245       113        241

How can I move each "block of data" to the right into new columns? It should look like this: 

                    M1               |             M2               |             M60              |
Time      rpm01     rpm02 .... rpm14 |   rpm01     rpm02 .... rpm14 |   rpm01     rpm02 .... rpm14 |
00:01     100       396        321   |   123       163        242   |   134       222        231   | 
00:02     200       323        213   |   243       128        221   |   245       113        241   | 
........                             |   ........                   |   ........                   |

Thanks!

PK2013dk
  • 1
  • @Jezrael is it pivoting? I was thinking of using multiindex rather than pivoting. – Celius Stingher Jan 10 '20 at 11:48
  • 1
    @CeliusStingher - I think pivoting with returned MultiIndex – jezrael Jan 10 '20 at 11:49
  • @Jezrael @CeliusStingher - Thank you for the hint to pivot the data. However, when I try to use the follwoing code, "machine" is a subgroup of "rpm" and not vice versa. When I use the rpm values as columns I get an error. `df.pivot_table(values=['rpm01','rpm02','rpm03','rpm04','rpm05','rpm06','rpm07','rpm08','rpm09','rpm10','rpm11','rpm12','rpm13','rpm14'], index='Time', columns='machine', fill_value=0, aggfunc='mean')` – PK2013dk Jan 13 '20 at 09:42
  • @PK2013dk - What is error? – jezrael Jan 13 '20 at 09:43
  • The solution for my specific case is: `df.pivot_table(values=['rpm01','rpm02','rpm03','rpm04','rpm05','rpm06','rpm07','rpm08','rpm09','rpm10','rpm11','rpm12','rpm13','rpm14'], index='Time', columns='machine', fill_value=0, aggfunc='mean').sort_index(level=1, axis=1)` The sorting was the last element missing. Thanks for the help with the hint to "pivoting" – PK2013dk Jan 13 '20 at 11:51

0 Answers0