Flexible and powerful data analysis / manipulation library for Python, providing labeled data structures similar to R data.frame objects, statistical functions, and much more
In [1]: import pandas as pd
In [2]: df1 = pd.DataFrame([['a', 'x', 0.123], ['a','x', 0.234],
...: ['a', 'y', 0.451], ['b', 'x', 0.453]],
...: columns=['first', 'second', 'value1']
...: ).set_index(['first', 'second'])
...:
In [3]: df2 = pd.DataFrame([['a', 10],['b', 20]],
...: columns=['first', 'value']).set_index(['first'])
...:
In [4]: df3 = pd.DataFrame([['a', 1], ['b', 2], ['c', 3]],
...: columns=['first', 'final_val']).set_index(['first'])
...:
In [5]: df1
Out[5]:
value1
first second
a x 0.123
x 0.234
y 0.451
b x 0.453
In [6]: df2
Out[6]:
value
first
a 10
b 20
In [7]: df3
Out[7]:
final_val
first
a 1
b 2
c 3
In [8]: df1.join(df3, how='outer')
Out[8]:
value1 final_val
first second
a x 0.123 1
x 0.234 1
y 0.451 1
b x 0.453 2
In [9]: df2.join(df3, how='outer')
Out[9]:
value final_val
first
a 10.0 1
b 20.0 2
c NaN 3
Problem description
I expect the result of an outer join to have all values of the level of the index on which the join is being performed, similar to how the single-index to single-index outer join includes c in the index of the result.
Expected Output
I expect the result from df1.join(df3, how='outer') to be:
value1 final_val
first second
a x 0.123 1
x 0.234 1
y 0.451 1
b x 0.453 2
c NaN NaN 3
or something similar (i.e. maybe '' instead of NaN for second, since it's not numeric).
Code Sample
Problem description
I expect the result of an outer join to have all values of the level of the index on which the join is being performed, similar to how the single-index to single-index outer join includes
c
in the index of the result.Expected Output
I expect the result from
df1.join(df3, how='outer')
to be:or something similar (i.e. maybe
''
instead ofNaN
forsecond
, since it's not numeric).Output of
pd.show_versions()