Mfcttrf

Odd PCB Layout for Voltage Regulator

Why is quantum gravity non-renormalizable?

Searching for single buildings in QGIS

Disk usage confusion: 10G missing on Linux home partition on SSD

What's the idiomatic (or best) way to trim surrounding whitespace from a string?

To “Er” Is Human

Making arrow with a gradual colour

Why will we fail creating a self sustaining off world colony?

What type of education should I select in this form?

Variable declaration inside main loop

Square wave to sawtooth wave using two BJT

Why is the saxophone not common in classical repertoire?

When does it become illegal to exchange bitcoin for cash?

I agreed to cancel a long-planned vacation (with travel costs) due to project deadlines, but now the timeline has all changed again

What is the meaning of ゴト in the context of 鮎

Why did the Middle Kingdom stop building pyramid tombs?

Is this house-rule removing the increased effect of cantrips at higher character levels balanced?

2019 2-letters 33-length list

Does Dhp 256-257 condone judging others?

Why is my 401k manager recommending me to save more?

How soon after takeoff can you recline your airplane seat?

Old story where computer expert digitally animates The Lord of the Rings

SQL Server Ignoring Instance name when using port number of different instance

Is it OK to throw pebbles and stones in streams, waterfalls, ponds, etc.?

Merging two data frames into a new one with unique items marked with 1 or 0

How to merge two dictionaries in a single expression?How to join (merge) data frames (inner, outer, left, right)Drop data frame columns by name“Large data” work flows using pandasMerging two data frames based on row values in python pandasMerge two python pandas data frames of different length but keep all rows in output data frameGet unmerged data from source tablesMerging two data frames with flexible conditionsCompare and equalize two data frames in PythonJoin two data frame with two columns values of a df with a single column values of another dataframe. based on some conditions?

9

1

I have several dataframes.

Dataframe #1

Feature Coeff
a 0.5
b 0.3
c 0.35
d 0.2

Dataframe #2

Feature Coeff
a 0.7
b 0.2
y 0.75
x 0.1

I want to merge this dataframe and obtain the following one:

Feature | DF1 | DF2
a 1 1
b 1 1
c 1 0
d 1 0
y 0 1
x 0 1

I know that I can do an outer merge but I do not know how to move from there to obtain the final dataframe I presented above. Any ideas?

python pandas dataframe

share|improve this question

edited 7 hours ago

cs95

157k2626 gold badges208208 silver badges279279 bronze badges

asked 9 hours ago

renakrerenakre

5,35911 gold badge1616 silver badges5353 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  just to make sure: in the final DataFrame you don't need any Coeff?
  
  – Adam.Er8
  9 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Adam.Er8 thanks! I do not need them. Thanks!
  
  – renakre
  9 hours ago
  

  
  
  
  

add a comment | 

9

1

I have several dataframes.

Dataframe #1

Feature Coeff
a 0.5
b 0.3
c 0.35
d 0.2

Dataframe #2

Feature Coeff
a 0.7
b 0.2
y 0.75
x 0.1

I want to merge this dataframe and obtain the following one:

Feature | DF1 | DF2
a 1 1
b 1 1
c 1 0
d 1 0
y 0 1
x 0 1

I know that I can do an outer merge but I do not know how to move from there to obtain the final dataframe I presented above. Any ideas?

python pandas dataframe

share|improve this question

edited 7 hours ago

cs95

157k2626 gold badges208208 silver badges279279 bronze badges

asked 9 hours ago

renakrerenakre

5,35911 gold badge1616 silver badges5353 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  just to make sure: in the final DataFrame you don't need any Coeff?
  
  – Adam.Er8
  9 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Adam.Er8 thanks! I do not need them. Thanks!
  
  – renakre
  9 hours ago
  

  
  
  
  

add a comment | 

I have several dataframes.

Dataframe #1

Feature Coeff
a 0.5
b 0.3
c 0.35
d 0.2

Dataframe #2

Feature Coeff
a 0.7
b 0.2
y 0.75
x 0.1

I want to merge this dataframe and obtain the following one:

Feature | DF1 | DF2
a 1 1
b 1 1
c 1 0
d 1 0
y 0 1
x 0 1

I know that I can do an outer merge but I do not know how to move from there to obtain the final dataframe I presented above. Any ideas?

python pandas dataframe

share|improve this question

edited 7 hours ago

cs95

157k2626 gold badges208208 silver badges279279 bronze badges

asked 9 hours ago

renakrerenakre

5,35911 gold badge1616 silver badges5353 bronze badges

Feature Coeff
a 0.5
b 0.3
c 0.35
d 0.2

Feature Coeff
a 0.7
b 0.2
y 0.75
x 0.1

Feature | DF1 | DF2
a 1 1
b 1 1
c 1 0
d 1 0
y 0 1
x 0 1

share|improve this question

edited 7 hours ago

cs95

157k2626 gold badges208208 silver badges279279 bronze badges

asked 9 hours ago

renakrerenakre

5,35911 gold badge1616 silver badges5353 bronze badges

share|improve this question

edited 7 hours ago

cs95

157k2626 gold badges208208 silver badges279279 bronze badges

asked 9 hours ago

renakrerenakre

5,35911 gold badge1616 silver badges5353 bronze badges

edited 7 hours ago

cs95

157k2626 gold badges208208 silver badges279279 bronze badges

edited 7 hours ago

cs95

157k2626 gold badges208208 silver badges279279 bronze badges

asked 9 hours ago

renakrerenakre

5,35911 gold badge1616 silver badges5353 bronze badges

asked 9 hours ago

renakrerenakre

5,35911 gold badge1616 silver badges5353 bronze badges

4 Answers
4

active

oldest

votes

8

Using concat+ get_dummies

u = pd.concat([df1, df2], axis=0, keys=['DF1', 'DF2'])

pd.get_dummies(u.Feature).sum(level=0).T

 DF1 DF2
a 1 1
b 1 1
c 1 0
d 1 0
x 0 1
y 0 1

share|improve this answer

answered 9 hours ago

user3483203user3483203

35.1k88 gold badges3131 silver badges6060 bronze badges

add a comment | 

5

You can use merge with series.str.get_dummies() together to achieve this:

m=df1[['Feature']].merge(df2[['Feature']],how='outer',indicator=True)

---

d='both':'DF1,DF2','left_only':'DF1','right_only':'DF2'
m=m.assign(_merge=m._merge.map(d))
m[['Feature']].join(m._merge.str.get_dummies(','))

---

 Feature DF1 DF2
0 a 1 1
1 b 1 1
2 c 1 0
3 d 1 0
4 y 0 1
5 x 0 1

share|improve this answer

answered 9 hours ago

anky_91anky_91

18.3k55 gold badges1111 silver badges2525 bronze badges

add a comment | 

4

Same Idea like user3483203 but with crosstab

u = pd.concat([df1, df2], axis=0, keys=['DF1', 'DF2'])


pd.crosstab(u.Feature, u.index.get_level_values(0))

share|improve this answer

answered 8 hours ago

WeNYoBenWeNYoBen

144k88 gold badges5151 silver badges8080 bronze badges

add a comment | 

2

I merged two dataframes using pd.merge and used list comprehension to assign values.

df = df1.merge(df2, on='Feature', how='outer')
df['DF1'] = [1 if x > 0 else 0 for x in df['Coeff_x']]
df['DF2'] = [1 if x > 0 else 0 for x in df['Coeff_y']]
df.drop(['Coeff_x', 'Coeff_y'], axis=1, inplace=True)

---

 Feature DF1 DF2
0 a 1 1
1 b 1 1
2 c 1 0
3 d 1 0
4 y 0 1
5 x 0 1

I've seen other -- pandas specific-- answers, and I would like to ask what are the advantages of methods like series.str.get_dummies() if you can achieve the same using built-in methods/functions? Is it much faster?
Genuinely curious since I'm a newbie myself.

(sorry I need more reputation points to leave comments directly under other answers!)

share|improve this answer

answered 9 hours ago

political scientistpolitical scientist

6588 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Not sure about performance, but you can assign the columns before the merge, then they get brought along df1.assign(DF1=1).merge(....) Just need a .fillna after :D
  
  – ALollz
  6 hours ago
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f56826251%2fmerging-two-data-frames-into-a-new-one-with-unique-items-marked-with-1-or-0%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

8

Using concat+ get_dummies

u = pd.concat([df1, df2], axis=0, keys=['DF1', 'DF2'])

pd.get_dummies(u.Feature).sum(level=0).T

 DF1 DF2
a 1 1
b 1 1
c 1 0
d 1 0
x 0 1
y 0 1

share|improve this answer

answered 9 hours ago

user3483203user3483203

35.1k88 gold badges3131 silver badges6060 bronze badges

add a comment | 

8

Using concat+ get_dummies

u = pd.concat([df1, df2], axis=0, keys=['DF1', 'DF2'])

pd.get_dummies(u.Feature).sum(level=0).T

 DF1 DF2
a 1 1
b 1 1
c 1 0
d 1 0
x 0 1
y 0 1

share|improve this answer

answered 9 hours ago

user3483203user3483203

35.1k88 gold badges3131 silver badges6060 bronze badges

add a comment | 

Using concat+ get_dummies

u = pd.concat([df1, df2], axis=0, keys=['DF1', 'DF2'])

pd.get_dummies(u.Feature).sum(level=0).T

 DF1 DF2
a 1 1
b 1 1
c 1 0
d 1 0
x 0 1
y 0 1

share|improve this answer

answered 9 hours ago

user3483203user3483203

35.1k88 gold badges3131 silver badges6060 bronze badges

u = pd.concat([df1, df2], axis=0, keys=['DF1', 'DF2'])

pd.get_dummies(u.Feature).sum(level=0).T

 DF1 DF2
a 1 1
b 1 1
c 1 0
d 1 0
x 0 1
y 0 1

share|improve this answer

answered 9 hours ago

user3483203user3483203

35.1k88 gold badges3131 silver badges6060 bronze badges

answered 9 hours ago

user3483203user3483203

35.1k88 gold badges3131 silver badges6060 bronze badges

answered 9 hours ago

user3483203user3483203

35.1k88 gold badges3131 silver badges6060 bronze badges

5

You can use merge with series.str.get_dummies() together to achieve this:

m=df1[['Feature']].merge(df2[['Feature']],how='outer',indicator=True)

---

d='both':'DF1,DF2','left_only':'DF1','right_only':'DF2'
m=m.assign(_merge=m._merge.map(d))
m[['Feature']].join(m._merge.str.get_dummies(','))

---

 Feature DF1 DF2
0 a 1 1
1 b 1 1
2 c 1 0
3 d 1 0
4 y 0 1
5 x 0 1

share|improve this answer

answered 9 hours ago

anky_91anky_91

18.3k55 gold badges1111 silver badges2525 bronze badges

add a comment | 

5

You can use merge with series.str.get_dummies() together to achieve this:

m=df1[['Feature']].merge(df2[['Feature']],how='outer',indicator=True)

---

d='both':'DF1,DF2','left_only':'DF1','right_only':'DF2'
m=m.assign(_merge=m._merge.map(d))
m[['Feature']].join(m._merge.str.get_dummies(','))

---

 Feature DF1 DF2
0 a 1 1
1 b 1 1
2 c 1 0
3 d 1 0
4 y 0 1
5 x 0 1

share|improve this answer

answered 9 hours ago

anky_91anky_91

18.3k55 gold badges1111 silver badges2525 bronze badges

add a comment | 

You can use merge with series.str.get_dummies() together to achieve this:

m=df1[['Feature']].merge(df2[['Feature']],how='outer',indicator=True)

---

d='both':'DF1,DF2','left_only':'DF1','right_only':'DF2'
m=m.assign(_merge=m._merge.map(d))
m[['Feature']].join(m._merge.str.get_dummies(','))

---

 Feature DF1 DF2
0 a 1 1
1 b 1 1
2 c 1 0
3 d 1 0
4 y 0 1
5 x 0 1

share|improve this answer

answered 9 hours ago

anky_91anky_91

18.3k55 gold badges1111 silver badges2525 bronze badges

m=df1[['Feature']].merge(df2[['Feature']],how='outer',indicator=True)

d='both':'DF1,DF2','left_only':'DF1','right_only':'DF2'
m=m.assign(_merge=m._merge.map(d))
m[['Feature']].join(m._merge.str.get_dummies(','))

 Feature DF1 DF2
0 a 1 1
1 b 1 1
2 c 1 0
3 d 1 0
4 y 0 1
5 x 0 1

share|improve this answer

answered 9 hours ago

anky_91anky_91

18.3k55 gold badges1111 silver badges2525 bronze badges

answered 9 hours ago

anky_91anky_91

18.3k55 gold badges1111 silver badges2525 bronze badges

answered 9 hours ago

anky_91anky_91

18.3k55 gold badges1111 silver badges2525 bronze badges

4

Same Idea like user3483203 but with crosstab

u = pd.concat([df1, df2], axis=0, keys=['DF1', 'DF2'])


pd.crosstab(u.Feature, u.index.get_level_values(0))

share|improve this answer

answered 8 hours ago

WeNYoBenWeNYoBen

144k88 gold badges5151 silver badges8080 bronze badges

add a comment | 

4

Same Idea like user3483203 but with crosstab

u = pd.concat([df1, df2], axis=0, keys=['DF1', 'DF2'])


pd.crosstab(u.Feature, u.index.get_level_values(0))

share|improve this answer

answered 8 hours ago

WeNYoBenWeNYoBen

144k88 gold badges5151 silver badges8080 bronze badges

add a comment | 

Same Idea like user3483203 but with crosstab

u = pd.concat([df1, df2], axis=0, keys=['DF1', 'DF2'])


pd.crosstab(u.Feature, u.index.get_level_values(0))

share|improve this answer

answered 8 hours ago

WeNYoBenWeNYoBen

144k88 gold badges5151 silver badges8080 bronze badges

u = pd.concat([df1, df2], axis=0, keys=['DF1', 'DF2'])


pd.crosstab(u.Feature, u.index.get_level_values(0))

share|improve this answer

answered 8 hours ago

WeNYoBenWeNYoBen

144k88 gold badges5151 silver badges8080 bronze badges

answered 8 hours ago

WeNYoBenWeNYoBen

144k88 gold badges5151 silver badges8080 bronze badges

answered 8 hours ago

WeNYoBenWeNYoBen

144k88 gold badges5151 silver badges8080 bronze badges

2

I merged two dataframes using pd.merge and used list comprehension to assign values.

df = df1.merge(df2, on='Feature', how='outer')
df['DF1'] = [1 if x > 0 else 0 for x in df['Coeff_x']]
df['DF2'] = [1 if x > 0 else 0 for x in df['Coeff_y']]
df.drop(['Coeff_x', 'Coeff_y'], axis=1, inplace=True)

---

 Feature DF1 DF2
0 a 1 1
1 b 1 1
2 c 1 0
3 d 1 0
4 y 0 1
5 x 0 1

I've seen other -- pandas specific-- answers, and I would like to ask what are the advantages of methods like series.str.get_dummies() if you can achieve the same using built-in methods/functions? Is it much faster?
Genuinely curious since I'm a newbie myself.

(sorry I need more reputation points to leave comments directly under other answers!)

share|improve this answer

answered 9 hours ago

political scientistpolitical scientist

6588 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Not sure about performance, but you can assign the columns before the merge, then they get brought along df1.assign(DF1=1).merge(....) Just need a .fillna after :D
  
  – ALollz
  6 hours ago
  

  
  
  
  

add a comment | 

2

I merged two dataframes using pd.merge and used list comprehension to assign values.

df = df1.merge(df2, on='Feature', how='outer')
df['DF1'] = [1 if x > 0 else 0 for x in df['Coeff_x']]
df['DF2'] = [1 if x > 0 else 0 for x in df['Coeff_y']]
df.drop(['Coeff_x', 'Coeff_y'], axis=1, inplace=True)

---

 Feature DF1 DF2
0 a 1 1
1 b 1 1
2 c 1 0
3 d 1 0
4 y 0 1
5 x 0 1

I've seen other -- pandas specific-- answers, and I would like to ask what are the advantages of methods like series.str.get_dummies() if you can achieve the same using built-in methods/functions? Is it much faster?
Genuinely curious since I'm a newbie myself.

(sorry I need more reputation points to leave comments directly under other answers!)

share|improve this answer

answered 9 hours ago

political scientistpolitical scientist

6588 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Not sure about performance, but you can assign the columns before the merge, then they get brought along df1.assign(DF1=1).merge(....) Just need a .fillna after :D
  
  – ALollz
  6 hours ago
  

  
  
  
  

add a comment | 

I merged two dataframes using pd.merge and used list comprehension to assign values.

df = df1.merge(df2, on='Feature', how='outer')
df['DF1'] = [1 if x > 0 else 0 for x in df['Coeff_x']]
df['DF2'] = [1 if x > 0 else 0 for x in df['Coeff_y']]
df.drop(['Coeff_x', 'Coeff_y'], axis=1, inplace=True)

---

 Feature DF1 DF2
0 a 1 1
1 b 1 1
2 c 1 0
3 d 1 0
4 y 0 1
5 x 0 1

I've seen other -- pandas specific-- answers, and I would like to ask what are the advantages of methods like series.str.get_dummies() if you can achieve the same using built-in methods/functions? Is it much faster?
Genuinely curious since I'm a newbie myself.

(sorry I need more reputation points to leave comments directly under other answers!)

share|improve this answer

answered 9 hours ago

political scientistpolitical scientist

6588 bronze badges

df = df1.merge(df2, on='Feature', how='outer')
df['DF1'] = [1 if x > 0 else 0 for x in df['Coeff_x']]
df['DF2'] = [1 if x > 0 else 0 for x in df['Coeff_y']]
df.drop(['Coeff_x', 'Coeff_y'], axis=1, inplace=True)

 Feature DF1 DF2
0 a 1 1
1 b 1 1
2 c 1 0
3 d 1 0
4 y 0 1
5 x 0 1

share|improve this answer

answered 9 hours ago

political scientistpolitical scientist

6588 bronze badges

answered 9 hours ago

political scientistpolitical scientist

6588 bronze badges

answered 9 hours ago

political scientistpolitical scientist

6588 bronze badges