Xjyuk

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

Finding an optimal set without forbidden subsets

What does this Pokemon Trainer mean by saying the player is "SHELLOS"?

Trace in the category of propositional statements

A world with roman numeral alphabet

GFCI versus circuit breaker

How soon after takeoff can you recline your airplane seat?

usage of y" not just for locations?

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

Wings for orbital transfer bioships?

How to track mail undetectably?

Are all notation equal by derivatives?

Can I deep fry food in butter instead of vegetable oil?

Sentences with no verb, but an ablative

Tricky riddle from sister

What could a Medieval society do with excess animal blood?

Replacing 5 gang light switches that have 3 of them daisy chained together

How come having a Deathly Hallow is not a big deal?

Crop production in mountains?

Could citing a database like libgen get one into trouble?

*p++->str : Understanding evaluation of ->

Is it theoretically possible to hack printer using scanner tray?

Lenovo Legion PXI-E61 Media Test Failure, Check Cable. Exiting PXE ROM. Then restarts and works fine

What is the meaning of "it" in "as luck would have it"?

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 6 hours ago

cs95

157k2626 gold badges208208 silver badges279279 bronze badges

asked 8 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
  8 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Adam.Er8 thanks! I do not need them. Thanks!
  
  – renakre
  8 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 6 hours ago

cs95

157k2626 gold badges208208 silver badges279279 bronze badges

asked 8 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
  8 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Adam.Er8 thanks! I do not need them. Thanks!
  
  – renakre
  8 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 6 hours ago

cs95

157k2626 gold badges208208 silver badges279279 bronze badges

asked 8 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 6 hours ago

cs95

157k2626 gold badges208208 silver badges279279 bronze badges

asked 8 hours ago

renakrerenakre

5,35911 gold badge1616 silver badges5353 bronze badges

share|improve this question

edited 6 hours ago

cs95

157k2626 gold badges208208 silver badges279279 bronze badges

asked 8 hours ago

renakrerenakre

5,35911 gold badge1616 silver badges5353 bronze badges

edited 6 hours ago

cs95

157k2626 gold badges208208 silver badges279279 bronze badges

edited 6 hours ago

cs95

157k2626 gold badges208208 silver badges279279 bronze badges

asked 8 hours ago

renakrerenakre

5,35911 gold badge1616 silver badges5353 bronze badges

asked 8 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 8 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 8 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 7 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 8 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
  4 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 8 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 8 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 8 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 8 hours ago

user3483203user3483203

35.1k88 gold badges3131 silver badges6060 bronze badges

answered 8 hours ago

user3483203user3483203

35.1k88 gold badges3131 silver badges6060 bronze badges

answered 8 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 8 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 8 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 8 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 8 hours ago

anky_91anky_91

18.3k55 gold badges1111 silver badges2525 bronze badges

answered 8 hours ago

anky_91anky_91

18.3k55 gold badges1111 silver badges2525 bronze badges

answered 8 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 7 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 7 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 7 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 7 hours ago

WeNYoBenWeNYoBen

144k88 gold badges5151 silver badges8080 bronze badges

answered 7 hours ago

WeNYoBenWeNYoBen

144k88 gold badges5151 silver badges8080 bronze badges

answered 7 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 8 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
  4 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 8 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
  4 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 8 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 8 hours ago

political scientistpolitical scientist

6588 bronze badges

answered 8 hours ago

political scientistpolitical scientist

6588 bronze badges

answered 8 hours ago

political scientistpolitical scientist

6588 bronze badges