0

CONTEXT:

I have two dataframes that have the following set up:

df1 looks like this...and goes on for about 3500 rows:

| id1 | id2   | 
|:----|------:|
| a   | name1 |  
| b   | name2 | 
| c   | name3 | 
| d   | name4 | 
| e   | name5 |   
| f   | name6 |

df2 looks like this...and goes on for about 4000 rows and about 8 columns

| id1 | ranktrial1   | ranktrial2   | ...
|:----|-------------:|-------------:| ...
| a   | rank1        |rank1         | ...
| b   | rank2        |rank2         | ...
| c   | rank3        |rank3         | ...
| d   | rank4        |rank4         | ...
| e   | rank5        |rank5         | ...  
| f   | rank6        |rank6         | ...

NOTE1: some of the id1s, do not have id2s. Meaning they'll be NaNs when they're mapped; and I'll just drop them whenever I get to that step. I don't know if this is relevant, but I just wanted to add it in case it was.

QUESTION:

I need to append/join/place (don't know correct jargon here) the corresponding id2 names to the second dataframe, iff the id1 entry == id1 entry of df2. How do I do this?

The desired dataframe would look like this:

| id1 | id2   | ranktrial1   | tranktrail2  | ...
|:----|------:|-------------:|-------------:| ...
| a   | name1 | rank1        | rank1        | ...  
| b   | name2 | rank2        | rank2        | ...
| c   | name3 | rank3        | rank3        | ...
| d   | name4 | rank4        | rank4        | ...
| e   | name5 | rank5        | rank5        | ...
| f   | name6 | rank6        | rank6        | ...

I feel as if this is probably really simple and I'm being a bit of a doofus, as I am a novice Pythoner. However, I have not been able to use similar question's responses to achieve my goal. It is quite likely my fault though :p

Thanks in advance for your help!

edits changed 4000 entries --> 4000 rows. LIkewise for 3500 entries

cross12tamu
  • 13
  • 4
  • 1
    Did you read about merge? – Vaishali Nov 07 '18 at 18:31
  • Yes! And I have tried numerous methods that I thought would give results that I had hoped. Either, i'd create an empty dataframe; or I'd make a dataframe that did more of a concatenation. Thanks for the response! – cross12tamu Nov 07 '18 at 18:40

1 Answers1

1

Given you are dropping the missing bits afterwards, this is an inner join and can be accomplished with merge. By default, merge uses all commonly named columns. In this case, the only commonly named column is id1. Also, how='inner' si also the default.

df1.merge(df2)

  id1    id2 ranktrial1 tranktrail2
0   a  name1      rank1       rank1
1   b  name2      rank2       rank2
2   c  name3      rank3       rank3
3   d  name4      rank4       rank4
4   e  name5      rank5       rank5
5   f  name6      rank6       rank6

You could be more explicit with

df1.merge(df2, how='inner', on='id1')
piRSquared
  • 285,575
  • 57
  • 475
  • 624
  • Thanks for the prompt result! I feel as if I had tried some of the merge (and merge combinations). But let me give it a spin! – cross12tamu Nov 07 '18 at 18:38
  • Yea, this doesn't work as intended. This creates an empty dataframe that looks something like this (with the indexes like the following:) ` |id1|id2|ranktrial1|ranktrial2|...etc...` and then everything is empty. I don't know why this occurs. – cross12tamu Nov 07 '18 at 18:44
  • Then that means something else is going on. Likely, you are importing a file and you end up with one column that is a big string per row. You don't know what to expect so assume that it is a dataframe. If I'm right, and this is a file, show us what the file looks like and we'll show you how to parse it. Then this suggestion should work. If I'm wrong... then idk what to do. – piRSquared Nov 07 '18 at 18:50
  • The first file with the id1/id2 columns is a RData object that I brought in and converted with rpy2 to a pandas dataframe. After I converted it, I cut out some unecessary data so it would only have the id1 and id2 columns. The other file was a tab delimited .txt file, that I brought in with pd.read_csv and had the sep as '\t' – cross12tamu Nov 07 '18 at 18:58
  • https://gyazo.com/7a879545491365af5ef80864750fde70 https://gyazo.com/544867157d206909c9c697f3cf36b073 Screenshots from the Spyder IDE, showing the two dataframes and what they look like (obviously, not with the generic names I have used in this example) – cross12tamu Nov 07 '18 at 19:02
  • and with a sample of the "combo" dataframe, with the merge function used: https://gyazo.com/68ac78e474973c5e50697ee47cd35cbf – cross12tamu Nov 07 '18 at 19:05
  • My only other guess is that your ids are strings in one df and ints in the other. Try `df1.astype({'id1': str}).merge(df2.astype({'id1': str}))` – piRSquared Nov 07 '18 at 19:32
  • This makes sense. I'll give it a try when I can get back to my computer. Thanks! – cross12tamu Nov 07 '18 at 20:31
  • Wow. I think that was the issue! My next question is does this "default" method automatically drop rows that contain NaN? Because the size is about what I would expect. I am just double checking. Again, thank you SO MUCH for your time and contribution. – cross12tamu Nov 07 '18 at 21:27
  • default is `'inner'` so yes. – piRSquared Nov 07 '18 at 21:28