1

Let's say I have a similar pandas.DataFrame:

    Date    L1  L2  L3  x1  x2  target
0   2020-07-01  0   0   Canada  13  53  1079461752
1   2020-07-01  0   0   US  10  55  3753714491
2   2020-07-01  0   0   Europe  12  55  3070135668
3   2020-07-01  0   1   Canada  13  52  384636447
4   2020-07-01  0   1   US  12  52  7934572515
... ... ... ... ... ... ... ...
2245    2020-07-30  4   3   US  14  53  9352550370
2246    2020-07-30  4   3   Europe  10  50  3589288902
2247    2020-07-30  4   4   Canada  14  55  8793280897
2248    2020-07-30  4   4   US  10  54  9289037195
2249    2020-07-30  4   4   Europe  10  52  2109067647

I want to add a column to the dataframe that will say what was the date where the combination of L1, L2 and L3 first appeared.

I thought about doing df.groupby(by=['L1', 'L2','L3']).Date.min() and I get the following result:

L1  L2  L3    
0   0   Canada   2020-07-01
        Europe   2020-07-01
        US       2020-07-01
    1   Canada   2020-07-01
        Europe   2020-07-01
                    ...    
4   3   Europe   2020-07-01
        US       2020-07-01
    4   Canada   2020-07-01
        Europe   2020-07-01
        US       2020-07-01

but I don't know how to merge the result to the main dataframe. I would greatly appreciate any help. Thanks

lalaland
  • 379
  • 3
  • 15
  • 1
    Rather `transform`: `df['new_col'] = df.groupby(by=['L1', 'L2','L3']).Date.transform('min')` – mozway Jul 20 '22 at 14:18
  • 1
    That's super helpful. Thanks. I never heard about `transform`. that will be super helpful for the future. Do you want to put it as an answer, so I accept it? – lalaland Jul 20 '22 at 14:22
  • 1
    I think it's a many times duplicate ;) – mozway Jul 20 '22 at 14:22
  • Also, I would like to add that `transform` could take more time with large data sets than using `merge` (which is kinda weird in my experience). In case you want some practice, after using `groupby`, you can use `reset_index` to remove the multilevel index and then `merge` them as normal. – PTQuoc Jul 22 '22 at 01:51
  • @PTQuoc Thanks for your comment. However, I am afraid I don't really understand what you mean by the second sentence. Can you please give an example? Also, do you know the reason why transform is slower than merge? – lalaland Jul 22 '22 at 14:30

0 Answers0