Pandas pivot time series data column into column of lists?

Question

I am working with some time series data that is set up in the following way:

The indx column starts at 1 and goes to around 434-460 (some series are longer than others) and then starts back at 1 again. What I would like to do is to transpose this so my dataframe looks like this where each row contains a couple lists for the time series:

         Time                                   MAG                      TAU               
[time1,  time2,  ...,  timen]        [MAG1,  MAG2,  ..., MAGn]   (this is the value stored every time
          ...                                   ...               indx=1)
          ...                                   ...                       ...

Edit

I have now tried to use pd.pivot() rather than melt() and this as as close as I've gotten. Using:

pd.pivot_table(df, index=['indx'], values=['MAG', 'time'], aggfunc=lambda x:list(x))

However, this just makes a really long list grouped by indx:

1. Please [don't paste data as pictures](https://stackoverflow.com/questions/20109391). 2. Does [pandas.pivot](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.pivot.html) help? — Bill Huang, Oct 18 '20 at 18:11
1. I always wondered how people did that, I will try to edit. 2. This might be what I need I'll try it out. — Patrick Maynard, Oct 18 '20 at 18:15
@BillHuang unfortunately google colab won't let me copy data to a clipboard. So far unsuccessful with pivot. — Patrick Maynard, Oct 18 '20 at 18:34

score 0 · Accepted Answer · answered Oct 18 '20 at 19:50

In case anyone comes across a similar situation I was able to figure it out by using .apply() to append on a number for each time series and then I used that to index the pivot:

global count
count = 0

def check_count(ind):
  global count
  if ind == 1:
    count += 1
    return count
  else:
    return count


df['LC'] = df['indx'].apply(lambda x: check_count(x))

df = pd.pivot_table(df, index=['LC'], values=['MAG', 'time'], aggfunc=lambda x: list(x))

df['V'] = df['time'].apply(lambda x: x.pop(0))

df['MAG'].apply(lambda x: x.pop(0));

Pandas pivot time series data column into column of lists?

1 Answers1