Styjun

What attributes and how big would a sea creature(s) need to be able to tow a ship?

Is there a strong legal guarantee that the U.S. can give to another country that it won't attack them?

When I press the space bar it deletes the letters in front of it

How to drill holes in 3/8" steel plates?

The three greedy pirates

Why is a mixture of two normally distributed variables only bimodal if their means differ by at least two times the common standard deviation?

[Future]Historical experience as a guide to warship design?

What are the indigenous English words for a prostitute?

Why do we need common sense in AI?

The joke office

WTB Horizon 47c - small crack in the middle of the tire

Is a request to book a business flight ticket for a graduate student an unreasonable one?

How can I effectively communicate to recruiters that a phone call is not possible?

How to tell someone I'd like to become friends without letting them think I'm romantically interested in them?

Write a function

Misrepresented my work history

How to know if blackberries are safe to eat

The rigidity of the countable product of free groups

Why does every calorie tracking app give a different target calorie count for the same goals?

What is /bin/red

Yet another hash table in C

When did "&" stop being taught alongside the alphabet?

Why different specifications for telescopes and binoculars?

What is this little owl-like bird?

How can I create a new column in a pandas pivot table with only matching values of populated columns?

How can I safely create a nested directory?Adding new column to existing DataFrame in Python pandasHow can I replace all the NaN values with Zero's in a column of a pandas dataframeHow to drop rows of Pandas DataFrame whose value in certain columns is NaNColumn Differences in Python Pivot-TableSelect rows from a DataFrame based on values in a column in pandasDeleting DataFrame row in Pandas based on column valueHow to count the NaN values in a column in pandas DataFrameHow to check if any value is NaN in a Pandas DataFrameTranspose Pandas Pivot Table

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I have a pandas pivot table that lists individuals in rows and data sources across the columns. There are hundreds of individuals going down amongst the rows and hundreds of sources going across along the columns.

 Desired_Value Source_1 Source_2 Source_3 ... Source_50
person1 20 20 20 20
person2 5 5 5 5
person3 Review 3 4 4 4
...
person50 1 1 1

What I want to do is create the Desired_Value column above. I want to pull in a value so long as it matches across all values (ignoring blank fields). If values do not match I want to show Review.

I use this pandas command to print my df to excel currently (without any Desired_Value column):

df13 = df12.pivot_table(index='person', columns = 'source_name', values = 'actual_data', aggfunc='first')

I'm new to Python so apologies if this is a silly question.

python pandas dataframe

share|improve this question

edited Mar 26 at 0:31

martineau

73.5k1010 gold badges101101 silver badges191191 bronze badges

asked Mar 26 at 0:30

bvdbvd

2333 bronze badges

add a comment | 

0

I have a pandas pivot table that lists individuals in rows and data sources across the columns. There are hundreds of individuals going down amongst the rows and hundreds of sources going across along the columns.

 Desired_Value Source_1 Source_2 Source_3 ... Source_50
person1 20 20 20 20
person2 5 5 5 5
person3 Review 3 4 4 4
...
person50 1 1 1

What I want to do is create the Desired_Value column above. I want to pull in a value so long as it matches across all values (ignoring blank fields). If values do not match I want to show Review.

I use this pandas command to print my df to excel currently (without any Desired_Value column):

df13 = df12.pivot_table(index='person', columns = 'source_name', values = 'actual_data', aggfunc='first')

I'm new to Python so apologies if this is a silly question.

python pandas dataframe

share|improve this question

edited Mar 26 at 0:31

martineau

73.5k1010 gold badges101101 silver badges191191 bronze badges

asked Mar 26 at 0:30

bvdbvd

2333 bronze badges

add a comment | 

I have a pandas pivot table that lists individuals in rows and data sources across the columns. There are hundreds of individuals going down amongst the rows and hundreds of sources going across along the columns.

 Desired_Value Source_1 Source_2 Source_3 ... Source_50
person1 20 20 20 20
person2 5 5 5 5
person3 Review 3 4 4 4
...
person50 1 1 1

What I want to do is create the Desired_Value column above. I want to pull in a value so long as it matches across all values (ignoring blank fields). If values do not match I want to show Review.

I use this pandas command to print my df to excel currently (without any Desired_Value column):

df13 = df12.pivot_table(index='person', columns = 'source_name', values = 'actual_data', aggfunc='first')

I'm new to Python so apologies if this is a silly question.

python pandas dataframe

share|improve this question

edited Mar 26 at 0:31

martineau

73.5k1010 gold badges101101 silver badges191191 bronze badges

asked Mar 26 at 0:30

bvdbvd

2333 bronze badges

 Desired_Value Source_1 Source_2 Source_3 ... Source_50
person1 20 20 20 20
person2 5 5 5 5
person3 Review 3 4 4 4
...
person50 1 1 1

df13 = df12.pivot_table(index='person', columns = 'source_name', values = 'actual_data', aggfunc='first')

share|improve this question

edited Mar 26 at 0:31

martineau

73.5k1010 gold badges101101 silver badges191191 bronze badges

asked Mar 26 at 0:30

bvdbvd

2333 bronze badges

share|improve this question

edited Mar 26 at 0:31

martineau

73.5k1010 gold badges101101 silver badges191191 bronze badges

asked Mar 26 at 0:30

bvdbvd

2333 bronze badges

edited Mar 26 at 0:31

martineau

73.5k1010 gold badges101101 silver badges191191 bronze badges

edited Mar 26 at 0:31

martineau

73.5k1010 gold badges101101 silver badges191191 bronze badges

asked Mar 26 at 0:30

bvdbvd

2333 bronze badges

asked Mar 26 at 0:30

bvdbvd

2333 bronze badges

1 Answer
1

active

oldest

votes

0

This is one method to do it:

df = df13.copy()
df = df.astype('Int64') # So NaN and Int values can coexist

# Create new column at the front of the data frame
df['Desired_Value'] = np.nan
cols = df.columns.tolist()
cols = cols[-1:] + cols[:-1]
df = df[cols]

# Loop over all rows and flag columns for review
for idx, row in df.iterrows():
 val = row.dropna().unique()
 if len(val) == 1:
 df.loc[idx, 'Desired_Value'] = val
 else:
 df.loc[idx, 'Desired_Value'] = 'Review'

print(df)

 Desired_Value Source_1 Source_2 Source_3 Source_50
person1 20 20 20 NaN 20
person2 5 5 NaN 5 5
person3 Review 3 4 4 4
person50 1 1 NaN NaN 1

share|improve this answer

edited Mar 26 at 1:06

answered Mar 26 at 0:56

NathanielNathaniel

2,29533 silver badges1414 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  this worked. thank you!
  
  – bvd
  Mar 26 at 1:16
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You're welcome! Feel free to accept this answer if it was helpful.
  
  – Nathaniel
  Mar 26 at 1:38
  
  

  
  
  
  

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%2f55348251%2fhow-can-i-create-a-new-column-in-a-pandas-pivot-table-with-only-matching-values%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

This is one method to do it:

df = df13.copy()
df = df.astype('Int64') # So NaN and Int values can coexist

# Create new column at the front of the data frame
df['Desired_Value'] = np.nan
cols = df.columns.tolist()
cols = cols[-1:] + cols[:-1]
df = df[cols]

# Loop over all rows and flag columns for review
for idx, row in df.iterrows():
 val = row.dropna().unique()
 if len(val) == 1:
 df.loc[idx, 'Desired_Value'] = val
 else:
 df.loc[idx, 'Desired_Value'] = 'Review'

print(df)

 Desired_Value Source_1 Source_2 Source_3 Source_50
person1 20 20 20 NaN 20
person2 5 5 NaN 5 5
person3 Review 3 4 4 4
person50 1 1 NaN NaN 1

share|improve this answer

edited Mar 26 at 1:06

answered Mar 26 at 0:56

NathanielNathaniel

2,29533 silver badges1414 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  this worked. thank you!
  
  – bvd
  Mar 26 at 1:16
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You're welcome! Feel free to accept this answer if it was helpful.
  
  – Nathaniel
  Mar 26 at 1:38
  
  

  
  
  
  

add a comment | 

0

This is one method to do it:

df = df13.copy()
df = df.astype('Int64') # So NaN and Int values can coexist

# Create new column at the front of the data frame
df['Desired_Value'] = np.nan
cols = df.columns.tolist()
cols = cols[-1:] + cols[:-1]
df = df[cols]

# Loop over all rows and flag columns for review
for idx, row in df.iterrows():
 val = row.dropna().unique()
 if len(val) == 1:
 df.loc[idx, 'Desired_Value'] = val
 else:
 df.loc[idx, 'Desired_Value'] = 'Review'

print(df)

 Desired_Value Source_1 Source_2 Source_3 Source_50
person1 20 20 20 NaN 20
person2 5 5 NaN 5 5
person3 Review 3 4 4 4
person50 1 1 NaN NaN 1

share|improve this answer

edited Mar 26 at 1:06

answered Mar 26 at 0:56

NathanielNathaniel

2,29533 silver badges1414 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  this worked. thank you!
  
  – bvd
  Mar 26 at 1:16
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You're welcome! Feel free to accept this answer if it was helpful.
  
  – Nathaniel
  Mar 26 at 1:38
  
  

  
  
  
  

add a comment | 

This is one method to do it:

df = df13.copy()
df = df.astype('Int64') # So NaN and Int values can coexist

# Create new column at the front of the data frame
df['Desired_Value'] = np.nan
cols = df.columns.tolist()
cols = cols[-1:] + cols[:-1]
df = df[cols]

# Loop over all rows and flag columns for review
for idx, row in df.iterrows():
 val = row.dropna().unique()
 if len(val) == 1:
 df.loc[idx, 'Desired_Value'] = val
 else:
 df.loc[idx, 'Desired_Value'] = 'Review'

print(df)

 Desired_Value Source_1 Source_2 Source_3 Source_50
person1 20 20 20 NaN 20
person2 5 5 NaN 5 5
person3 Review 3 4 4 4
person50 1 1 NaN NaN 1

share|improve this answer

edited Mar 26 at 1:06

answered Mar 26 at 0:56

NathanielNathaniel

2,29533 silver badges1414 bronze badges

df = df13.copy()
df = df.astype('Int64') # So NaN and Int values can coexist

# Create new column at the front of the data frame
df['Desired_Value'] = np.nan
cols = df.columns.tolist()
cols = cols[-1:] + cols[:-1]
df = df[cols]

# Loop over all rows and flag columns for review
for idx, row in df.iterrows():
 val = row.dropna().unique()
 if len(val) == 1:
 df.loc[idx, 'Desired_Value'] = val
 else:
 df.loc[idx, 'Desired_Value'] = 'Review'

print(df)

 Desired_Value Source_1 Source_2 Source_3 Source_50
person1 20 20 20 NaN 20
person2 5 5 NaN 5 5
person3 Review 3 4 4 4
person50 1 1 NaN NaN 1

share|improve this answer

edited Mar 26 at 1:06

answered Mar 26 at 0:56

NathanielNathaniel

2,29533 silver badges1414 bronze badges

answered Mar 26 at 0:56

NathanielNathaniel

2,29533 silver badges1414 bronze badges

answered Mar 26 at 0:56

NathanielNathaniel

2,29533 silver badges1414 bronze badges