CODEWITHSUNDEEP
pythonpandasmergedataframeconcatenation
tryptofame
tryptofame

Reputation: 392

Python Pandas concatenate/merge DataFrames using index or column identifiers

I would like to use the 'pandas.concat' method to merge two DataFrames, but I don't fully understand all 'pandas.concat' arguments. I've got two DataFrames, which have the same identifying variables in the columns, but differ in one single column. 

import pandas as pd
dict_data = {'Treatment': ['C', 'C', 'C'], 'Biorep': ['A', 'A', 'A'], 'Techrep': [1, 1, 1], 'AAseq': ['ELVISLIVES', 'ELVISLIVES', 'ELVISLIVES'], 'mz':[500.0, 500.5, 501.0]}
df_a = pd.DataFrame(dict_data)
dict_data = {'Treatment': ['C', 'C', 'C'], 'Biorep': ['A', 'A', 'A'], 'Techrep': [1, 1, 1], 'AAseq': ['ELVISLIVES', 'ELVISLIVES', 'ELVISLIVES'], 'inte':[1100.0, 1050.0, 1010.0]}
df_b = pd.DataFrame(dict_data)

df_a

        AAseq   Biorep  Techrep Treatment   mz
0    ELVISLIVES  A   1   C   500.0
1    ELVISLIVES  A   1   C   500.5
2    ELVISLIVES  A   1   C   501.0

df_b

    AAseq   Biorep  Techrep Treatment   int
0    ELVISLIVES  A   1   C   1100
1    ELVISLIVES  A   1   C   1050
2    ELVISLIVES  A   1   C   1010

I can add the column the following way:

df_m = df_a.copy()
df_m['inte'] = df_b['inte']

     AAseq  Biorep  Techrep Treatment   inte
0    ELVISLIVES  A   1   C   1100
1    ELVISLIVES  A   1   C   1050
2    ELVISLIVES  A   1   C   1010

My real data looks much more complex and I'm afraid that the method above could lead to the wrong order of values in the rows (specially since I want to use 'pandas.melt' beforehand).

When using: 

dfm = pd.concat([df_a, df_b])

     AAseq  Biorep  Techrep Treatment   inte    mz
0    ELVISLIVES  A   1   C   NaN     500.0
1    ELVISLIVES  A   1   C   NaN     500.5
2    ELVISLIVES  A   1   C   NaN     501.0
0    ELVISLIVES  A   1   C   1100    NaN
1    ELVISLIVES  A   1   C   1050    NaN
2    ELVISLIVES  A   1   C   1010    NaN

The concatenated DataFrame extends the values rowwise leading to NaN vals.

Question: How can I achieve the same result (shown above) using 'concat'?

Thank you for your support!

Upvotes: 1

Views: 12918

Answers (1)

furas
furas

Reputation: 142651

Using

 print pd.concat((df_a, df_b['inte']), axis=1)

you can get

        AAseq Biorep  Techrep Treatment     mz  inte
0  ELVISLIVES      A        1         C  500.0  1100
1  ELVISLIVES      A        1         C  500.5  1050
2  ELVISLIVES      A        1         C  501.0  1010

Is this what you expected ?

Or maybe you have more complicated data like this - see different values in column Treatment

        AAseq Biorep  Techrep Treatment     mz
0  ELVISLIVES      A        1         A  500.0
1  ELVISLIVES      A        1         B  500.5
2  ELVISLIVES      A        1         C  501.0

        AAseq Biorep  Techrep Treatment  inte
0  ELVISLIVES      A        1         C  1100
1  ELVISLIVES      A        1         B  1050
2  ELVISLIVES      A        1         A  1010

and you need to keep order using values from columns AAseq Biorep Techrep Treatment then use merge

import pandas as pd
dict_data = {
    'AAseq': ['ELVISLIVES', 'ELVISLIVES', 'ELVISLIVES'],
    'Biorep': ['A', 'A', 'A'],
    'Techrep': [1, 1, 1],
    'Treatment': ['A', 'B', 'C'],
    'mz':[500.0, 500.5, 501.0]
}
df_a = pd.DataFrame(dict_data)

dict_data = {
    'AAseq': ['ELVISLIVES', 'ELVISLIVES', 'ELVISLIVES'],
    'Biorep': ['A', 'A', 'A'],
    'Techrep': [1, 1, 1],
    'Treatment': ['C', 'B', 'A'],
    'inte':[1100.0, 1050.0, 1010.0]
}
df_b = pd.DataFrame(dict_data)

print pd.merge(left=df_a, right=df_b, on=['AAseq', 'Biorep', 'Techrep', 'Treatment'])

result:

        AAseq Biorep  Techrep Treatment     mz  inte
0  ELVISLIVES      A        1         A  500.0  1010
1  ELVISLIVES      A        1         B  500.5  1050
2  ELVISLIVES      A        1         C  501.0  1100

Upvotes: 2

Related Questions