CODEWITHSUNDEEP
pythonpandasdataframepandas-groupby
Vivian Marcello
Vivian Marcello

Reputation: 21

Pandas groupby rolling apply list

Here is the sample code.

df = pd.DataFrame({'A':  ['2020-04-28','2020-04-28','2020-04-29','2020-04-29','2020-04-30','2020-04-30'],
      'B': ['11-000-000-246_1064461', '30-000-015-488_1191035','11-000-000-246_1064461','30-000-015-488_1191035','30-000-015-488_1191035','11-000-000-246_1064461'],
      'C': [[4700652221, 4700652723],[4700653241], 0, [4700652781, 4700656546],[4700646464, 4700645646],[4700652748, 4700659873, 4700659238]]
     })

And my dataframe looks like:

    A           B                       C
0   2020-04-28  11-000-000-246_1064461  [4700652221, 4700652723]
1   2020-04-28  30-000-015-488_1191035  [4700653241]
2   2020-04-29  11-000-000-246_1064461  []
3   2020-04-29  30-000-015-488_1191035  [4700652781, 4700656546]
4   2020-04-30  30-000-015-488_1191035  [4700646464, 4700645646]
5   2020-04-30  11-000-000-246_1064461  [4700652748, 4700659873, 4700659238]

I tried to get a new column called D containing an array of all C array items in a rolling window of 2 days with this code, but it doesn't worked:

df = df.groupby(['A','B'])['C'].rolling(2).apply(list).reset_index(name = 'D')

I need to get something like this:

    A           B                       D
0   2020-04-28  11-000-000-246_1064461  Nan
1   2020-04-28  30-000-015-488_1191035  Nan
2   2020-04-29  11-000-000-246_1064461  [4700652221, 4700652723]
3   2020-04-29  30-000-015-488_1191035  [4700652781, 4700656546, 4700653241]
4   2020-04-30  30-000-015-488_1191035  [4700646464, 4700645646, 4700652781, 4700656546]
5   2020-04-30  11-000-000-246_1064461  [4700652748, 4700659873, 4700659238]

Upvotes: 2

Views: 962

Answers (1)

Shubham Sharma
Shubham Sharma

Reputation: 71689

Use, DataFrame.groupby on column B then use .transform on the column C. In this transform method use Series.shift to shift the column and then concatenate the column with itself:

df['D'] = (
    df.groupby('B')['C']
    .transform(lambda s: s + s.shift(1))
)

df1 = df.drop('C', 1)

# print(df1)
            A                       B                                                 D
0  2020-04-28  11-000-000-246_1064461                                               NaN
1  2020-04-28  30-000-015-488_1191035                                               NaN
2  2020-04-29  11-000-000-246_1064461                          [4700652221, 4700652723]
3  2020-04-29  30-000-015-488_1191035              [4700652781, 4700656546, 4700653241]
4  2020-04-30  30-000-015-488_1191035  [4700646464, 4700645646, 4700652781, 4700656546]
5  2020-04-30  11-000-000-246_1064461              [4700652748, 4700659873, 4700659238]

Upvotes: 1

Related Questions