flattening time series data from pandas df

user_13955141 0
Viewed 728

I have a df that looks like this:

enter image description here

And I'm trying to turn it into this:

enter image description here

the following code gets me a list of a list that I can convert to a df and includes the first 3 columns of expected output, but not sure how to get the number columns I need (note: I have way more than 3 number columns but using this as a simple illustration).

x=[['ID','Start','End','Number1','Number2','Number3']]
for i in range(len(df)):
    if not(df.iloc[i-1]['DateSpellIndicator']):
        ID= df.iloc[i]['ID']
        start = df.iloc[i]['Date']
    if not(df.iloc[i]['DateSpellIndicator']):
        newrow = [ID, start,df.iloc[i]['Date'],...]
        x.append(newrow)
pandas time-series flatten python loops
2 Answers

Here's one way to do it by making use of pandas groupby.

Input Dataframe:

    ID  DATE        NUM TORF
0   1   2020-01-01  40  True
1   1   2020-02-01  50  True
2   1   2020-03-01  60  False
3   1   2020-06-01  70  True
4   2   2020-07-01  20  True
5   2   2020-08-01  30  False

Output Dataframe:

    END         ID  Number1 Number2 Number3 START
0   2020-08-01  2   20      30.0    NaN     2020-07-01
1   2020-06-01  1   70      NaN     NaN     2020-06-01
2   2020-03-01  1   40      50.0    60.0    2020-01-01

Code:

new_df=pd.DataFrame()
#create groups based on ID
for index, row in df.groupby('ID'):
    #Within each group split at the occurence of False
    dfnew=np.split(row, np.where(row.TORF == False)[0] + 1)
    for sub_df in dfnew:
        #within each subgroup
        if sub_df.empty==False:
            dfmod=pd.DataFrame({'ID':sub_df['ID'].iloc[0],'START':sub_df['DATE'].iloc[0],'END':sub_df['DATE'].iloc[-1]},index=[0])        
            j=0
            for nindex, srow in sub_df.iterrows():
                dfmod['Number{}'.format(j+1)]=srow['NUM']
                j=j+1
            #concatenate the existing and modified dataframes
            new_df=pd.concat([dfmod, new_df], axis=0)
        
new_df.reset_index(drop=True)
user_104350270

Some of the steps could be reduced to get the same output. I used cumsum to get the fist and last date. Used list to get the columns the way you want. Please note the output has different column names than your example. I assume you can change them the way you want.

df ['new1'] = ~df['datespell']
df['new2'] = df['new1'].cumsum()-df['new1']
check = df.groupby(['id', 'new2']).agg({'date': {'start': 'first', 'end': 'last'}, 'number': {'cols': lambda x: list(x)}})
check.columns = check.columns.droplevel(0)
check.reset_index(inplace=True)
pd.concat([check,check['cols'].apply(pd.Series)], axis=1).drop(['cols'], axis=1)


id  new2    start   end 0   1   2
0   1   0   2020-01-01  2020-03-01  40.0    50.0    60.0
1   1   1   2020-06-01  2020-06-01  70.0    NaN NaN
2   2   1   2020-07-01  2020-08-01  20.0    30.0    NaN

Here is the dataframe i used.

    id  date    number  datespell   new1    new2
0   1   2020-01-01  40  True    False   0
1   1   2020-02-01  50  True    False   0
2   1   2020-03-01  60  False   True    0
3   1   2020-06-01  70  True    False   1
4   2   2020-07-01  20  True    False   1
5   2   2020-08-01  30  False   True    1
user_58547310
Related