CODEWITHSUNDEEP
pythonpandasdata-science
mbadd
mbadd

Reputation: 967

Group pandas rows into pairs then find timedelta

I have a dataframe where I need to group the TX/RX column into pairs, and then put these into a new dataframe with a new index and the timedelta between them as values.

df = pd.DataFrame()
df['time1'] = pd.date_range('2018-01-01', periods=6, freq='H') 
df['time2'] = pd.date_range('2018-01-01', periods=6, freq='1H1min')
df['id'] = ids
df['val'] = vals

                time1               time2  id val
0 2018-01-01 00:00:00 2018-01-01 00:00:00   1   A
1 2018-01-01 01:00:00 2018-01-01 01:01:00   2   B
2 2018-01-01 02:00:00 2018-01-01 02:02:00   3   A
3 2018-01-01 03:00:00 2018-01-01 03:03:00   4   B
4 2018-01-01 04:00:00 2018-01-01 04:04:00   5   A
5 2018-01-01 05:00:00 2018-01-01 05:05:00   6   B

needs to be...

index    timedelta    A   B
  0      1            1   2
  1      1            3   4
  2      1            5   6

I think that pivot_tables or stack/unstack is probably the best way to go about this, but I'm not entirely sure how...

Upvotes: 0

Views: 98

Answers (1)

jezrael
jezrael

Reputation: 863501

I believe you need:

df = pd.DataFrame()
df['time1'] = pd.date_range('2018-01-01', periods=6, freq='H') 
df['time2'] = df['time1'] + pd.to_timedelta([60,60,120,120,180,180], 's')
df['id'] = range(1,7)
df['val'] = ['A','B'] * 3

df['t'] = df['time2'] - df['time1']
print (df)
                time1               time2  id val        t
0 2018-01-01 00:00:00 2018-01-01 00:01:00   1   A 00:01:00
1 2018-01-01 01:00:00 2018-01-01 01:01:00   2   B 00:01:00
2 2018-01-01 02:00:00 2018-01-01 02:02:00   3   A 00:02:00
3 2018-01-01 03:00:00 2018-01-01 03:02:00   4   B 00:02:00
4 2018-01-01 04:00:00 2018-01-01 04:03:00   5   A 00:03:00
5 2018-01-01 05:00:00 2018-01-01 05:03:00   6   B 00:03:00

#if necessary convert to seconds
#df['t'] = (df['time2'] - df['time1']).dt.total_seconds()
df = df.pivot('t','val','id').reset_index().rename_axis(None, axis=1)
#if necessary aggregate values
#df = (df.pivot_table(index='t',columns='val',values='id', aggfunc='mean')
#        .reset_index().rename_axis(None, axis=1))
print (df)
         t  A  B
0 00:01:00  1  2
1 00:02:00  3  4
2 00:03:00  5  6

Upvotes: 1

Related Questions