Best solution to generating a list in pandas up to a certain date

user_4666912 0
Viewed 23

I have the following dataframe.

    person_id    month  firmid
0   1            20     12
1   1            18     10
2   1            15     13
3   2            18     13
4   2            17     11
5   2            15     13

I have a list of people with different IDs (person_id) and the month they worked in a firm in the form of a number. So person with id 1 worked in firm with id 12 during the month 20.

I want to generate a list of all the firm ids he has worked in up to date (ie the current month).

So the output should be:

    person_id    month  firms_worked_at_up_to_current_month
0   1            20     [12,10,13]
1   1            18     [10,13]
2   1            15     [13]
3   2            18     [13,11]
4   2            17     [11,13]
5   2            15     [13]

The above is just an example. My actual file has millions of lines, so I need an efficient way to do this that doesn't involve looping.

pandas python
1 Answers

If you can sort the DataFrame, you can do:

# sort by person and month
df = df.sort_values(by=['person_id', 'month'])

# create a unitary list
df['firms'] = df.firmid.apply(lambda x: [x])

# apply cumulative transformation
df['firms'] = df.groupby('person_id')['firms'].transform(lambda x: x.cumsum().apply(set))

print(df)

Output

   person_id  month  firmid         firms
2          1     15      13          {13}
1          1     18      10      {10, 13}
0          1     20      12  {10, 12, 13}
5          2     15      13          {13}
4          2     17      11      {11, 13}
3          2     18      13      {11, 13}

Notice that the final result in the column firms is a set, as it seems you only want unique values.

user_40015920
Related