-2
$\begingroup$

I have two data frames. One data frame with one column and second data frame with two columns. I need to merge first data frame with either column of the second data frame and returns the values. After merging the values,the order of the first column should be same as the input. Please find the sample input below

first_column = c("ENSG00000165588","ENSG00000213551")
df1 = data.frame(first_column)
first_column = c("ENSG00000142192", "ENSG00000140575", "ENSG00000165588", "ENSG00000165588", "ENSG00000213551", "ENSG00000213551","ENSG00000197153")
second_column = c("ENSG00000165588", "ENSG00000165588", "ENSG00000186908", "ENSG00000135446", "ENSG00000273983", "ENSG00000274267","ENSG00000213551")
df2 = data.frame(first_column,second_column)

Please find the output below.

first_column = c("ENSG00000165588","ENSG00000165588","ENSG00000165588","ENSG00000165588","ENSG00000213551","ENSG00000213551","ENSG00000213551")
second_column = c("ENSG00000142192","ENSG00000140575","ENSG00000186908","ENSG00000135446","ENSG00000273983","ENSG00000274267","ENSG00000197153")
output = data.frame(first_column,second_column)
$\endgroup$
4
  • $\begingroup$ Just to confirm are you happy for a Python pandas solution? I recognise this is the implication of the title, but the code is in R $\endgroup$
    – M__
    Dec 28, 2022 at 13:22
  • 1
    $\begingroup$ Yes, I am happy with python $\endgroup$ Dec 28, 2022 at 16:28
  • 1
    $\begingroup$ This does not look like something I'd call a data frame merge. Are you able to give a bit more context around this problem (i.e. what you want to do with this data, rather than the intermediate problem you're trying to solve)? This helps us to wrap our heads around the problem and give better answers. $\endgroup$
    – gringer
    Dec 28, 2022 at 18:09
  • $\begingroup$ History This question was shifted from 'deleted' to 'closed' by me, which apparently was a mistake - apologies therein and then moved from 'closed' to 'opened'. I've now fully addressed the question. There was a misunderstanding: I originally provided a column merge solution when the OP wanted an index merge. Both solutions have now been provided. All code is vectorised and will be fast over a large data set. $\endgroup$
    – M__
    Jan 1, 2023 at 6:21

2 Answers 2

1
$\begingroup$

I think this question is more about subsetting than merging. You can try this solution in R:

first_column = c("ENSG00000165588","ENSG00000213551")
df1 = data.frame(first_column)
first_column = c("ENSG00000142192", "ENSG00000140575", "ENSG00000165588", "ENSG00000165588",
                 "ENSG00000213551", "ENSG00000213551","ENSG00000197153")
second_column = c("ENSG00000165588", "ENSG00000165588", "ENSG00000186908", "ENSG00000135446",
                  "ENSG00000273983", "ENSG00000274267","ENSG00000213551")
df2 = data.frame(first_column,second_column)

output = rbind(df2[df2$first_column %in% df1$first_column,],
               df2[df2$second_column %in% df1$first_column,])

output <- transform(output, second_column = ifelse(output$second_column%in%df1$first_column,
                                                   first_column, second_column),
                            first_column = ifelse(output$second_column%in%df1$first_column,
                                                  second_column, first_column))
output <- output[ order(row.names(output)), ]
$\endgroup$
0
$\begingroup$

Edit The OP in hindsight is requesting an index merge. The first solution, presented here as Column merge solution was rejected by the OP.

From the comments below this post the order requested by the OP was:

# 0 ENSG00000165588 ENSG00000142192 
# 1 ENSG00000165588 ENSG00000140575 
# 2 ENSG00000165588 ENSG00000186908 
# 3 ENSG00000165588 ENSG00000135446 
# 4 ENSG00000213551 ENSG00000273983 
# 5 ENSG00000213551 ENSG00000274267 
# 6 ENSG00000213551 ENSG00000197153,

Column merge solution

  • The first operation is a concatenation and inner merge *.
  • The second operation are two outer merges, the second requiring a 'left-hand' merge *.

The code is here:

import pandas as pd

df1 = pd.DataFrame({'A':["ENSG00000165588","ENSG00000213551"]})
df2i = pd.DataFrame({'A':["ENSG00000142192", "ENSG00000140575", "ENSG00000165588",\
                          "ENSG00000165588", "ENSG00000213551", "ENSG00000213551", "ENSG00000197153"]})
df2ii = pd.DataFrame({'A':["ENSG00000165588", "ENSG00000165588", "ENSG00000186908", \
                            "ENSG00000135446", "ENSG00000273983", "ENSG00000274267","ENSG00000213551"]})
    
# Column 'A'
df3in = pd.concat([df2i, df2ii])
df4inner = pd.merge(df3in,df1, on='A', how='inner')

# Column 'A+'
df3out = pd.merge(df2i, df2ii, on='A', how='outer')
df4outer = pd.merge(df3in,df1, on='A', how='outer', indicator = True).query('_merge=="left_only"')
df5 = df4outer.drop('_merge', axis=1).reset_index(drop=True).rename(columns={'A':'A+'})   
df6 = df4inner.join(df5)

print (df6)

Output

                 A               A+
0  ENSG00000165588  ENSG00000142192
1  ENSG00000165588  ENSG00000140575
2  ENSG00000165588  ENSG00000197153
3  ENSG00000165588  ENSG00000186908
4  ENSG00000213551  ENSG00000135446
5  ENSG00000213551  ENSG00000273983
6  ENSG00000213551  ENSG00000274267
                      

This approach was rejected (comments) because of the position of ENSG00000197153 at row 2, Column A+ in relation to ENSG00000165588.


Index merge solution

import pandas as pd

def indexmerge(dfa, dfb, df1):
    df3out = dfa.reset_index().merge(dfb, how="left").set_index('index')
    df4outer = pd.merge(df3out,df1, on='A', how='outer', indicator = True).query('_merge=="left_only"')
    return df4outer

if __name__ == '__main__':

    df1 = pd.DataFrame({'A':["ENSG00000165588","ENSG00000213551"]})
    df2i = pd.DataFrame({'A':["ENSG00000142192", "ENSG00000140575", "ENSG00000165588",\
                              "ENSG00000165588", "ENSG00000213551", "ENSG00000213551", "ENSG00000197153"]})
    df2ii = pd.DataFrame({'A':["ENSG00000165588", "ENSG00000165588", "ENSG00000186908", \
                                "ENSG00000135446", "ENSG00000273983", "ENSG00000274267","ENSG00000213551"]})
    df3in = pd.concat([df2i, df2ii])
    df4inner = pd.merge(df3in,df1, on='A', how='inner')

    df4outer = indexmerge(df2i,df2ii,df1)
    df4outer2 = indexmerge(df2ii,df2i,df1)
    df4outer2.rename(columns={'A':'_A'},inplace=True)
    df6 = pd.concat([df4outer,df4outer2],axis=1)
    df6['A+'] = df6['A'].astype(str).replace('nan','') + df6['_A'].astype(str).replace('nan','')
    df7 = df6.sort_index().reset_index()
    df8 = df4inner.join(df7['A+'])
    print (df8)

Output

                 A               A+
0  ENSG00000165588  ENSG00000142192
1  ENSG00000165588  ENSG00000140575
2  ENSG00000165588  ENSG00000186908
3  ENSG00000165588  ENSG00000135446
4  ENSG00000213551  ENSG00000273983
5  ENSG00000213551  ENSG00000274267
6  ENSG00000213551  ENSG00000197153

This solution is identical to the order requested in the comments and is now also presented at the beginning of this post. The solution used here pivots around line 1 of indexmerge() and thus is an index merge operation. The remaining code is common pandas manipulation supporting this operation.

Note the new position of ENSG00000197153 row 6 column A+. The index merge retains ENSG00000197153 at the base of the column and therefore in association, i.e. same row, as ENSG00000213551.


* technical pandas terminology

$\endgroup$
3
  • $\begingroup$ Can you please check the output. The output must be in the dataframe. The order may be different but column A and column B entries should match. "ENSG00000213551" match in one case is wrong. If possible check my sample output above. $\endgroup$ Dec 29, 2022 at 4:31
  • $\begingroup$ 0 ENSG00000165588 ENSG00000142192 2 ENSG00000165588 ENSG00000140575 3 ENSG00000165588 ENSG00000186908 4 ENSG00000165588 ENSG00000135446 5 ENSG00000213551 ENSG00000273983 6 ENSG00000213551 ENSG00000274267 7 ENSG00000213551 ENSG00000197153, this is the output which i need. For eg. ENSG00000213551 should interact with ENSG00000273983, ENSG00000274267, ENSG00000197153, but your program output shows ENSG00000213551 interact with ENSG00000135446, ENSG00000274267, ENSG00000197153. $\endgroup$ Dec 29, 2022 at 5:00
  • $\begingroup$ I have added the sample output and my question is correct. $\endgroup$ Dec 29, 2022 at 5:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.