-2

I am new to Python and this is my first question.

I have df1: DF1:

period  id  cust_id product_id  start_time  end_time
20181001    1   aa  2   01/10/2018 19:04    01/10/2018 19:31
20181001    1   zz  9   01/10/2018 15:57    01/10/2018 16:00
20181001    1   zz  178 01/10/2018 13:01    01/10/2018 13:36
20181001    1   zz  231 02/10/2018 02:51    02/10/2018 02:51

df2: (Look up table)

PERIOD  product_id  Name    Product_info    START_TIME  END_TIME
20181001    2   Xab GHI 01/10/2018 19:00    01/10/2018 19:29
20181001    2   Xab QQQ 01/10/2018 19:30    01/10/2018 19:59
20181001    2   Xab asd 01/10/2018 20:00    01/10/2018 20:29
20181001    9   S2  Angele  01/10/2018 14:00    01/10/2018 14:59
20181001    9   S2  Road    01/10/2018 15:00    01/10/2018 15:59
20181001    9   S2  Flash   01/10/2018 16:00    01/10/2018 16:59
20181001    9   S2  Simpson 01/10/2018 17:00    01/10/2018 17:29
20181001    178 T3  Chase   01/10/2018 13:00    01/10/2018 13:59
20181001    178 T3  Chase   01/10/2018 14:00    01/10/2018 14:59
20181001    178 T3  Elaine  01/10/2018 15:00    01/10/2018 15:59

I need result in DF1 as follow: result in DF1:

period  id  cust_id product_id  start_time  end_time    Product_info    Name
20181001    1   aa  2   01/10/2018 19:04    01/10/2018 19:31    GHI Xab
20181001    1   aa  2   01/10/2018 19:04    01/10/2018 19:31    QQQ Xab
20181001    1   zz  9   01/10/2018 15:57    01/10/2018 16:00    Road    S2
20181001    1   zz  9   01/10/2018 15:57    01/10/2018 16:00    Flash   S2
20181001    1   zz  178 01/10/2018 13:01    01/10/2018 13:36    Chase   T3
20181001    1   zz  231 02/10/2018 02:51    02/10/2018 02:51    None    None

Both DF1 and DF2 are really big tables with lots of rows. Please help me to solve the issue.

I am also adding the image of the tables aswell.enter image description here

Thanks in advance.

Omer Tekbiyik
  • 4,255
  • 1
  • 15
  • 27
pri
  • 31
  • 3
  • 3
    It is very unclear what you want. For example, why is it in your dataframe there id is now one for all of the values when in the source dataframe we have id=1 for aa and if=2 for zz. Please go into more detail about what you are actually trying to accomplish with the lookup. – Edeki Okoh Feb 07 '19 at 23:50
  • Hello, I would like to merge both the tables with on product id as well as when date and time falls in the range of another table. As I mentioned before these two tables are really big to do pd.merge. – pri Feb 08 '19 at 08:34

1 Answers1

1

It looks like you are wanting to merge your two DataFrames, but you have given no criteria. It's not clear why you want to exclude certain entries.

See here for how to structure a question so that we can help the most.

df1.merge(df2[['product_id', 'Product_info', 'Name']], on='product_id', how='outer')

Resulting in:

      period  id cust_id  product_id        start_time          end_time  \
0   20181001   1      aa           2  01/10/2018 19:04  01/10/2018 19:31
1   20181001   1      aa           2  01/10/2018 19:04  01/10/2018 19:31 
2   20181001   1      aa           2  01/10/2018 19:04  01/10/2018 19:31
3   20181001   1      zz           9  01/10/2018 15:57  01/10/2018 16:00
4   20181001   1      zz           9  01/10/2018 15:57  01/10/2018 16:00
5   20181001   1      zz           9  01/10/2018 15:57  01/10/2018 16:00
6   20181001   1      zz           9  01/10/2018 15:57  01/10/2018 16:00
7   20181001   1      zz         178  01/10/2018 13:01  01/10/2018 13:36
8   20181001   1      zz         178  01/10/2018 13:01  01/10/2018 13:36
9   20181001   1      zz         178  01/10/2018 13:01  01/10/2018 13:36
10  20181001   1      zz         231  02/10/2018 02:51  02/10/2018 02:51

   Product_info Name
0           GHI  Xab
1           QQQ  Xab
2           asd  Xab
3        Angele   S2
4          Road   S2
5         Flash   S2
6       Simpson   S2
7         Chase   T3
8         Chase   T3
9        Elaine   T3
10          NaN  NaN

You can see here for more info on merging.

Alex
  • 6,610
  • 3
  • 20
  • 38