Python Pandas inserting into a list in a cell using data from previous cells

user_1435281 0
Viewed 59

import pandas as pd

d = {'date': ['2019-01-01', '2019-01-01', '2019-01-01', '2019-02-01', '2019-02-01', '2019-02-01', '2019-02-01', '2019-02-01', '2019-03-01'], 
'group': ['group_a', 'group_a', 'group_a', 'group_a', 'group_b', 'group_b', 'group_b', 'group_b', 'group_c'], 
'description': ['desc_a', 'desc_a', 'desc_a', 'desc_a', 'desc_b', 'desc_b', 'desc_b', 'desc_b', 'desc_c'], 
'quantity': [10, 21, 33, 12, 23, 31, 23, 11, 41]}

df = pd.DataFrame(data=d)

df['weeknum'] = pd.to_datetime(df['date']).dt.isocalendar().week
df['month'] = pd.DatetimeIndex(df['date']).month
df['year'] = pd.DatetimeIndex(df['date']).year
df['dayofyear'] = pd.DatetimeIndex(df['date']).dayofyear


b = df.groupby(['month', 'year', 'group', 'description']).sum('quantity').reset_index()

b['sales_chart'] = [[0] * 12 for _ in range(len(b))]

b['test2'] = b.apply(lambda x: x['sales_chart'].pop(int(x['month']-1)), axis=1)
b['test2'] = b.apply(lambda x: x['sales_chart'].insert(int(x['month']-1), x['quantity']), axis=1)

g = b.groupby(["group","description"],sort=False)['quantity']
u = b.assign(pct_chg = g.pct_change().add(1).fillna(0))

Current Output:

|    |   month |   year | group   | description   |   quantity |   weeknum |   dayofyear | sales_chart                              | test2   |   pct_chg |
|---:|--------:|-------:|:--------|:--------------|-----------:|----------:|------------:|:-----------------------------------------|:--------|----------:|
|  0 |       1 |   2019 | group_a | desc_a        |         64 |         3 |           3 | [64, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] |         |    0      |
|  1 |       2 |   2019 | group_a | desc_a        |         12 |         5 |          32 | [0, 12, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] |         |    0.1875 |
|  2 |       2 |   2019 | group_b | desc_b        |         88 |        20 |         128 | [0, 88, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] |         |    0      |
|  3 |       3 |   2019 | group_c | desc_c        |         41 |         9 |          60 | [0, 0, 41, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] |         |    0      |
[3]

What I have been trying to do is alter the sales chart so that subsequent months are being inserted into their appropriate spots in the sales_chart list for the groups.

|    |   month |   year | group   | description   |   quantity |   weeknum |   dayofyear | sales_chart                              | test2   |   pct_chg |
|---:|--------:|-------:|:--------|:--------------|-----------:|----------:|------------:|:-----------------------------------------|:--------|----------:|
|  0 |       1 |   2019 | group_a | desc_a        |         64 |         3 |           3 | [64, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] |         |    0      |
|  1 |       2 |   2019 | group_a | desc_a        |         12 |         5 |          32 | [64, 12, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] |         |    0.1875 |
|  2 |       2 |   2019 | group_b | desc_b        |         88 |        20 |         128 | [0, 88, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] |         |    0      |
|  3 |       3 |   2019 | group_c | desc_c        |         41 |         9 |          60 | [0, 0, 41, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] |         |    0      |
[3]

The best I've been able to do is the above line: b['test2'] = b.apply(lambda x: x['sales_chart'].insert(int(x['month']-1), x['quantity']), axis=1)

which only inserts it into the appropriate month space. I've tried using a function to shift() and copy the list but I've been unsuccessful so far. Appreciate the assistance.

As a final note, this is example data, the current data has multiple years with some full(and partial) month ranges, meaning not all 12 months are represented so that's why I 'm inserting by month position.

pandas dataframe lambda python
1 Answers

I can suggest a work around that I'm not certain would be feasible on large data sets, but might help you find a different direction. I used a temp dataframe to create the complete list of months' quantity per year/per group

c = b.pivot_table(values = 'quantity', index=['group', 'year'], columns = 'month').fillna(0)
c = c.reindex(columns = [i for i in range(1,13)], fill_value=0)
c['sale_chart'] = c.apply(lambda x: x.values.tolist(), axis=1)
c = c['sale_chart'].reset_index()

Then you can simply add these lists as a new column to the original dataframe.

b = b.merge(c, how='left', on = ['group', 'year'])

If I understood correctly and you want the amounts to be added up to the current month of each row, you can modify the lists like this:

b.sc = b[['month', 'sale_chart']].apply(lambda x: x.sale_chart[:x.month]+[0]*(12-x.month), axis=1)
user_43374020
Related