0

I have a csv file that is a series of rssi data recordings at different access points over a period of time, of the form:

Time     | Signal | Location      
07:37:47 | -98.0  | bedroom             
07:37:47 | -96.0  | bedroom                 
07:37:47 | -91.0  | kitchen             
07:37:47 | -95.0  | kitchen             
07:37:47 | -68.0  | stairs                  
07:37:48 | -60.0  | bedroom  
07:37:48 | -60.0  | stairs              
07:37:48 | -62.0  | stairs   
07:37:48 | -60.0  | kitchen  
07:37:48 | -70.0  | kitchen

etc.

And I want to rearrange it to output with the average signal strength for each different location per second to look like this:

Time     | bedroom | kitchen  | stairs  
07:37:47 | -97.0   |    -93.0 | -68.0                           
07:37:48 | -60.0   |    -65.0 | -61.0

etc.

I.e. I want to move the location of the access point to be a heading of each new column and have the average signal per second in the rows.

Is there a clean way this can be done using pandas? I have been fiddling around with .groupby() and .resample() but can't figure out an easy way of doing it. Any help would be hugely appreciated.

Thank you.

koPytok
  • 3,453
  • 1
  • 14
  • 29
Milo Welch
  • 1
  • 1
  • 3
    Have you looked at pivot? see [How to Pivot a DataFrame](https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe) – Umar.H Mar 05 '21 at 14:42
  • Does this answer your question? [How to pivot a dataframe?](https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe) – Umar.H Mar 05 '21 at 14:57

1 Answers1

0

Use pd.pivot_table to get pivot table and then reset_index to make Time regular column:

import pandas as pd
import numpy as np

pd.pivot_table(data=df, values="Signal", index="Time",
               columns="Location", aggfunc=np.mean)\
    .reset_index()
koPytok
  • 3,453
  • 1
  • 14
  • 29
  • Hey, thanks for the help, this reshapes it exactly right however it changes the 'Time' to become an index, how would I keep the time as just a column or is there an extra part I can do to change it back? – Milo Welch Mar 08 '21 at 14:53
  • It is possible to `.reset_index()`. I've added it in comment – koPytok Mar 09 '21 at 06:45