CODEWITHSUNDEEP
pythonpandas
Stats DUB01
Stats DUB01

Reputation: 441

Cumulative Collection of String values in pandas column

I have a table that looks like the screenshot below.

enter image description here

I am trying to add a column to the end of the table, which will contain all of the previous lead_id values. This is what I have tried so far:

total = pd.Series()
test = pd.concat([test, total], axis=1)
test.rename(columns={0: 'total'}, inplace=True)
test.loc[0, 'total'] = test.loc[0, 'lead_id']

enter image description here

for i in range(1, 2):
    test.loc[i, 'total'] = test.loc[i-1, 'total'] + test.loc[i, 'lead_id']

However, this is not working and is giving me the following error:

    ---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-245-0e11e468a37a> in <module>()
      1 for i in range(1, 2):
----> 2     test.loc[i, 'total'] = test.loc[i-1, 'total'] + test.loc[i, 'lead_id']

/opt/conda/lib/python3.6/site-packages/pandas/core/indexing.py in __setitem__(self, key, value)
    188             key = com.apply_if_callable(key, self.obj)
    189         indexer = self._get_setitem_indexer(key)
--> 190         self._setitem_with_indexer(indexer, value)
    191 
    192     def _validate_key(self, key, axis):

/opt/conda/lib/python3.6/site-packages/pandas/core/indexing.py in _setitem_with_indexer(self, indexer, value)
    609 
    610                     if len(labels) != len(value):
--> 611                         raise ValueError('Must have equal len keys and value '
    612                                          'when setting with an iterable')
    613 

ValueError: Must have equal len keys and value when setting with an iterable

Effectivley, I need to collect all of the previous lead_id values into a sort of cumulative collection of lead_id's. These would be deduplicated also if possible. I know that the sample data below doesn't have any duplicates, but there will be when I apply it to the real data.

Expected Output (apologies for poor quality)

enter image description here

Data:

[{'final_repayment_date_month': Period('2016-01', 'M'), 'lead_id': [21293]},
 {'final_repayment_date_month': Period('2016-02', 'M'),
  'lead_id': [39539, 38702, 39448]},
 {'final_repayment_date_month': Period('2016-03', 'M'),
  'lead_id': [39540, 39527, 39474]}]

Upvotes: 1

Views: 249

Answers (2)

Voyager
Voyager

Reputation: 759

import pandas as pd
import itertools as it

test =pd.DataFrame([
    {'final_repayment_date_month': pd.Period('2016-01', 'M'), 
    'lead_id': [21293]},
    {'final_repayment_date_month': pd.Period('2016-02', 'M'),
    'lead_id': [39539, 38702, 39448]},
    {'final_repayment_date_month': pd.Period('2016-03', 'M'),
    'lead_id': [39540, 39527, 39474]}
    ]
)
test['total']=list(it.accumulate(test['lead_id'],lambda x,y:sorted(x+y)))
print(test)

You took a detour. please give me 5 star :)

output

  final_repayment_date_month                lead_id                                              total
0                    2016-01                [21293]                                            [21293]
1                    2016-02  [39539, 38702, 39448]                       [21293, 38702, 39448, 39539]
2                    2016-03  [39540, 39527, 39474]  [21293, 38702, 39448, 39474, 39527, 39539, 39540]

Upvotes: 1

balderman
balderman

Reputation: 23815

Code below. Duplicates are handled by using set()

from collections import namedtuple
import pprint

Period = namedtuple('Period', 'data other')

data = [{'final_repayment_date_month': Period('2016-01', 'M'), 'lead_id': [21293, 21293]},
        {'final_repayment_date_month': Period('2016-02', 'M'),
         'lead_id': [39539, 38702, 39448]},
        {'final_repayment_date_month': Period('2016-03', 'M'),
         'lead_id': [39540, 39527, 39474]}]

grand_total = set()
for entry in data:
    for l in entry['lead_id']:
        grand_total.add(l)
    entry['total'] = sum(grand_total)
    pprint.pprint(entry)

Output

  {'final_repayment_date_month': Period(data='2016-01', other='M'),
 'lead_id': [21293, 21293],
 'total': 21293}
{'final_repayment_date_month': Period(data='2016-02', other='M'),
 'lead_id': [39539, 38702, 39448],
 'total': 138982}
{'final_repayment_date_month': Period(data='2016-03', other='M'),
 'lead_id': [39540, 39527, 39474],
 'total': 257523}

Upvotes: 1

Related Questions