1

I have a dataframe say :

A | B
1   a
2   b
3   c
4   d

and another one like :

A  |  G
1     Gate

and i want to combine the first dataframe with the other dataframe such that it shows : 

A  | B   |   G
1    a    Gate
2    b     
3    c
4    d

I have not been able to create the new column using pd.merge and i am new to python/pandas. Any help would be appreciated.

Another condition i failed to mention was that the dataframe with one row can have multiple changes since it is in a loop and the variables keep changing.

So, 

A  |  G
1    Gate

could be 

A  | H
2    Gate 2

or 

A |  F     
4   gate 4

So the final output would say : 

A  |  B  |  G  |   H   |  F |
1     a    Gate
2     b         Gate 2  
3     c
4     d                  Gate 4

At the end of this loop i would like to write this output to an excel file using pd.to_excel.

TheNoob
  • 185
  • 1
  • 12

1 Answers1

3

If you are trying to use merge, you can do a left join like this:

df1.merge(df2, how='left', on='A')
Ted Petrou
  • 59,042
  • 19
  • 131
  • 136
  • Please remove your answer because dupe, maybe find some better one. – jezrael Nov 12 '17 at 17:17
  • What if the dataframe with a single row is in a for loop and it keeps updating the that one row... Also what if the column name changes as well? will the merge work for all the times? I can update my question. – TheNoob Nov 12 '17 at 18:44
  • @jezrael i can add this into my questions then it wont be a dupe? – TheNoob Nov 12 '17 at 19:20
  • It depends what add, if no dupe please give me know I can reopen. Tte best is add [mcve](https://stackoverflow.com/help/mcve) and explain why dupe does not work. – jezrael Nov 12 '17 at 19:27
  • @jezrael Could you have a look at the question. I just edited it... – TheNoob Nov 12 '17 at 19:42
  • Unfortunately need map solution from duplicated answer :( – jezrael Nov 12 '17 at 20:07
  • @jezrael could you please elaborate what a map solution from duplicated answer means. Thank You. – TheNoob Nov 12 '17 at 20:55
  • Sure. So need `d1 = df2.set_index('A')['G'].to_dict() df1['G'] = df1['A'].map(d1)`, `d2 = df3.set_index('A')['H'].to_dict() df1['H'] = df1['A'].map(d2)`. Explanation - create dict with each new one row df and map to new column. – jezrael Nov 12 '17 at 21:02
  • @jezrael Thank you! Could you also please tell me how would you write this dict to the base excel sheet ? Is it after creating the dictionary completely or after inserting each new key in the dictionary? Sorry, this is my first time. – TheNoob Nov 12 '17 at 22:25
  • @jezrael the base excel sheet has the column A and B and i would like to add the new columns to that sheet with the A in one dataframe matching the other dataframe. – TheNoob Nov 12 '17 at 22:26
  • Hmmm, you create new datagrame columns by map, get finsl dataframe with columns A,B,G,H,F and then write dataframe to excel. There is no write dictionary to excel. – jezrael Nov 13 '17 at 05:14
  • @jezrael alright!I will try and let you know! Thanks a lot!!! :) – TheNoob Nov 13 '17 at 05:34