python pandas dataframe merge or join dataframe

Question

I want to help yours

if i have a pandas dataframe merge

first dataframe is

D = { Year, Age, Location, column1, column2... }
      2013, 20 , america, ..., ...
      2013, 35, usa, ..., ...
      2011, 32, asia, ..., ...
      2008, 45, japan, ..., ...

shape is 38654rows x 14 columns

second dataframe is

D = { Year, Location, column1, column2... }
      2008, usa, ..., ...
      2008, usa, ..., ...
      2009, asia, ..., ...
      2009, asia, ..., ...
      2010, japna, ..., ...

shape is 96rows x 7 columns

I want to merge or join two different dataframe. How can I do it?

thanks

jezrael · Accepted Answer · 2016-10-19T11:35:15.383

4

IIUC you need merge with parameter how='left' if need left join on column Year and Location:

print (df1)
   Year  Age Location  column1  column2
0  2013   20  america        7        5
1  2008   35      usa        8        1
2  2011   32     asia        9        3
3  2008   45    japan        7        1

print (df2)
   Year Location  column1  column2
0  2008      usa        8        9
1  2008      usa        7        2
2  2009     asia        8        2
3  2009     asia        0        1
4  2010    japna        9        3

df = pd.merge(df1,df2, on=['Year','Location'], how='left')
print (df)
   Year  Age Location  column1_x  column2_x  column1_y  column2_y
0  2013   20  america          7          5        NaN        NaN
1  2008   35      usa          8          1        8.0        9.0
2  2008   35      usa          8          1        7.0        2.0
3  2011   32     asia          9          3        NaN        NaN
4  2008   45    japan          7          1        NaN        NaN

You can also check documentation.

edited Oct 19 '16 at 11:35

answered Oct 19 '16 at 11:27

jezrael

822,522
95
1,334
1,252

not print dataframe... – GrayHash Oct 19 '16 at 11:28
only viewing columns – GrayHash Oct 19 '16 at 11:28
why can i see the nan data.. :( – GrayHash Oct 19 '16 at 11:40
That's good dataframe merge I think, but.. I can't see it.. – GrayHash Oct 19 '16 at 11:40
You see `nan` data where column are not match - e.g. in my sample in `df2` are not `2013` with `20` data. – jezrael Oct 19 '16 at 11:41
hm.. question's failed. I think... – GrayHash Oct 19 '16 at 11:44
If... only Yearly is just 1 data, but another dataframe have a lots of data that Years. it just extent to it? – GrayHash Oct 19 '16 at 11:46
Why do you think? – jezrael Oct 19 '16 at 11:46
sorry extent -> extend. – GrayHash Oct 19 '16 at 11:47
Hmmm, i am not sure if I understand you. Maybe help check nice pictures in [docs](http://pandas.pydata.org/pandas-docs/stable/merging.html#brief-primer-on-merge-methods-relational-algebra) - do you need left, inner or outer join? What column are joined on? What is desired output from input? – jezrael Oct 19 '16 at 11:49
well.. i want to this. year, age, location, column1, column2... but.. I using it docs that join, merge, etc.. but I can't.. :( – GrayHash Oct 19 '16 at 11:52
If when I using it left and right merge, just view to dataframe1, not viewing dataframe2.. – GrayHash Oct 19 '16 at 11:53
with your real data? – jezrael Oct 19 '16 at 11:54
yes. it's real data.. first dataframe using sas preprocessing, another dataframe is read mysql data. – GrayHash Oct 19 '16 at 11:57
I have a idea - what is `df1.info()` and `df2.info()` ? – jezrael Oct 19 '16 at 11:58
class 'pandas.core.frame.DataFrame'> Int64Index: 38654 entries, 0 to 38653 Data columns (total 14 columns): – GrayHash Oct 19 '16 at 12:00
Int64Index: 96 entries, 0 to 95 Data columns (total 7 columns): – GrayHash Oct 19 '16 at 12:00
first dataframe type is here dtypes: category(1), float64(12), object(1), another dataframe type is here dtypes: float64(5), object(2) – GrayHash Oct 19 '16 at 12:01
hmm, formating is not nice. But I think you can check `dtypes` of matching columns - what is `df1.dtypes` and `df2.dtypes`? – jezrael Oct 19 '16 at 12:02
dtypes comment is 14,15 comments. thank's alot jezrael – GrayHash Oct 19 '16 at 12:04
Now it working? – jezrael Oct 19 '16 at 12:05
not this working .. :( how can i using it one to many data handling? – GrayHash Oct 19 '16 at 12:05
I think there is problem `dtypes` of matching columns are different - e.g. in `df1` it is `object` and in `df2` it is `int`. – jezrael Oct 19 '16 at 12:12
hm... ok... do that... – GrayHash Oct 19 '16 at 12:15

python pandas dataframe merge or join dataframe

1 Answers1