CODEWITHSUNDEEP
pythonpandasdataframe
jason
jason

Reputation: 4449

Pandas: join a row of data on specific index number

import pandas
a=[['Date', 'letters', 'numbers', 'mixed'], ['1/2/2014', 'a', '6', 'z1'], ['1/2/2014', 'a', '3', 'z1'], ['1/3/2014', 'c', '1', 'x3']]
df = pandas.DataFrame.from_records(a[1:],columns=a[0])

b= [['a', 'b', 'c'], ['a', 'b', 'c']]
df2 = pandas.DataFrame.from_records(b[1:],columns=b[0])

I want to join df2 on df so it looks like this:

       Date letters numbers mixed    a    b    c
0  1/2/2014       a       6    z1  
1  1/2/2014       a       3    z1    a    b    c
2  1/3/2014       c       1    x3

Looking through the docs, I got as close as df=df.join(df2,how='outer')

which gives you this:

       Date letters numbers mixed    a    b    c
0  1/2/2014       a       6    z1    a    b    c
1  1/2/2014       a       3    z1  NaN  NaN  NaN
2  1/3/2014       c       1    x3  NaN  NaN  NaN

I want something like df=df.join(df2,how='outer', on_index = 1)

Upvotes: 0

Views: 1639

Answers (1)

Ffisegydd
Ffisegydd

Reputation: 53698

It already does do a join with a specific index, it just so happens to be that your index in df2 is 0 and so when it joins it places the 'a', 'b', 'c' in index 0.

import pandas

a=[['Date', 'letters', 'numbers', 'mixed'], ['1/2/2014', 'a', '6', 'z1'], ['1/2/2014', 'a', '3', 'z1'], ['1/3/2014', 'c', '1', 'x3']]
df = pandas.DataFrame.from_records(a[1:],columns=a[0])

b= [['a', 'b', 'c'], ['a', 'b', 'c']]
df2 = pandas.DataFrame.from_records(b[1:],columns=b[0], index=[1])

df=df.join(df2,how='outer')

print(df)
#        Date letters numbers mixed    a    b    c
# 0  1/2/2014       a       6    z1  NaN  NaN  NaN
# 1  1/2/2014       a       3    z1    a    b    c
# 2  1/3/2014       c       1    x3  NaN  NaN  NaN

In this code I have set the index of df2 with the keyword argument index = [1]. If you cannot use this keyword argument then you can change the index (in this particular example) using df2.index = [1], this should be done before joining the two DataFrames.

Upvotes: 2

Related Questions