Outer join vertically on Python

Question

Table 1

Category Date           Value
A        01/01/2015       4
A        02/01/2015       1
B        01/01/2015       6
B        02/01/2015       7

Table 1 above has the values for each category organized by month.

Table 2

Category Date           Value
A        03/01/2015       10
C        03/01/2015       66
D        03/01/2015       9

Suppose table 2 comes in, which has the values for each category in March, 2015.

Table 3

Category Date           Value
A        01/01/2015       4
A        02/01/2015       1
A        03/01/2015       10
B        01/01/2015       6
B        02/01/2015       7
B        03/01/2015       0
C        01/01/2015       0
C        02/01/2015       0
C        03/01/2015       66
D        01/01/2015       0
D        02/01/2015       0
D        03/01/2015       9

I want to "outer-join" the two tables "vertically" on Python: If Table2 has a category that Table1 doesn't have, then it adds that category to Table3 and assign a value of 0 for 01/01/2015 and 02/01/2015. Also, the category that is in table1 but not in table2 will also be added in table 3, by assigning a value of 0 for 03/01/2015. If both have the same categories, they will just be added vertically with the values in the table1 and table2.

Any advice or help will be greatly appreciated.. I've been thinking about this all day and still can't find an efficient way to do this. Thanks so much!

With the use of "outer-join", I would almost suggest to try and use Python's sqlite built-in module. — user707650
– user707650, Commented Jul 13, 2015 at 5:04
Also: what have you so far tried? You should have tried something with a double loop, I assume. Perhaps anything with dicts? — user707650
– user707650, Commented Jul 13, 2015 at 5:04
Or perhaps you can even do something nice with itertools, e.g. itertools.product(). — user707650
– user707650, Commented Jul 13, 2015 at 5:07
@Evert I thought about using the command "merge" (with outer option) on the two tables, and then making a new 3-column data table (Let's say it's table4) with the merged table's categories, Table2's dates, and Table2's values columns, and then "concat" the merged table with Table4. But this doesn't let me add categories that were not in Table1 but were in Table 2. — Jenny Bunge
– Jenny Bunge, Commented Jul 13, 2015 at 5:20
@Evert I also don't really understand the itertools.product() command. Can you actually elaborate further on this? Thanks! — Jenny Bunge
– Jenny Bunge, Commented Jul 13, 2015 at 5:21

miriamsimone · Accepted Answer · 2015-07-13 06:15:24Z

I would do this using Pandas as follows (I'll call your tables df1 and df2):

First prepare the list of dates and categories for the final table using set together with concat to select only the unique values from your original tables:

import pandas as pd
dates = set(pd.concat([df1.Date,df2.Date]))
cats = set(pd.concat([df1.Category,df2.Category]))

Then we create the landing table by iterating through these sets (that's where itertools.product is a neat trick although note that you have to cast it as a list to feed it into the dataframe constructor):

df3 = pd.DataFrame(list(itertools.product(cats,dates)),columns = ['Category','Date'])
df3

Out[88]: 
   Category        Date
0         D  01/01/2015
1         D  03/01/2015
2         D  02/01/2015
3         C  01/01/2015
4         C  03/01/2015
5         C  02/01/2015
6         A  01/01/2015
7         A  03/01/2015
8         A  02/01/2015
9         B  01/01/2015
10        B  03/01/2015
11        B  02/01/2015

Finally we bring in the values using merge (setting on='left'), using np.fmax to consolidate the two sets (you have use fmax instead of max to ignore the nans):

df3['Value'] = np.fmax(pd.merge(df3,df1,how='left')['Value'],pd.merge(df3,df2,how='left')['Value'])

df3
Out[127]: 
   Category        Date  Value
0         D  01/01/2015    NaN
1         D  03/01/2015      9
2         D  02/01/2015    NaN
3         C  01/01/2015    NaN
4         C  03/01/2015     66
5         C  02/01/2015    NaN
6         A  01/01/2015      4
7         A  03/01/2015     10
8         A  02/01/2015      1
9         B  01/01/2015      6
10        B  03/01/2015    NaN
11        B  02/01/2015      7

Collectives™ on Stack Overflow

Outer join vertically on Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related