问题描述:

I have a use case here which I want to achieve using python pandas. So, I have 2 tables "Configuration" & "Transaction". The tables are as seen below. Also I have the results table which i want to achieve -

**Configuration Table :**

Set_id Entry_Type Effective_Date

------ ---------- ---------------

S1 IN 08-2000

S2 IN 09-2002

S3 TO 10-2004

S4 TO 12-2006

**Transaction Table :**

Set_id Entry_Type Accounting_Date

------ ---------- ---------------

S2 IN 09-2004

S4 TO 07-2007

**Result_Table :**

Set_id Entry_Type Effective_Date Accounting_Date

------ ---------- -------------- ---------------

S2 IN 09-2002 09-2004

S4 TO 12-2006 07-2007

Step 1 - Group by Configuration Table by Set_id, Event_Type & Effective_Date

Step 2 - The criteria for the join is -

(Configuration.Set_id = Transaction. Set_id

And

Configuration.Entry_Type = Transaction. Entry_Type

And

Max(Effective_Date) < Accounting_Date)

Please help me with the same.

网友答案:

You can perform a merge, it will automatically align on the common columns and the default type is inner:

In [5]:

df1.merge(df )
Out[5]:
  Set_id Entry_Type Accounting_Date Effective_Date
0     S2         IN         09-2004        09-2002
1     S4         TO         07-2007        12-2006

After merging you can use boolean filtering for the last part:

In [12]:

merged = df1.merge(df )
merged[merged['Effective_Date'].max() < merged['Accounting_Date']]
Out[12]:
  Set_id Entry_Type Accounting_Date Effective_Date
1     S4         TO      2007-07-21     2006-12-21
网友答案:

You can use pd.merge()

Configuration table

In [117]: ct
Out[117]:
  Set_id Entry_Type Effective_Date
0     S1         IN        08-2000
1     S2         IN        09-2002
2     S3         TO        10-2004
3     S4         TO        12-2006

Transaction table

In [119]: tt
Out[119]:
  Set_id Entry_Type Accounting_Date
0     S2         IN         09-2004
1     S4         TO         07-2007

And, the result using merge

In [120]: ct.merge(tt)
Out[120]:
  Set_id Entry_Type Effective_Date Accounting_Date
0     S2         IN        09-2002         09-2004
1     S4         TO        12-2006         07-2007
相关阅读:
Top