Find first and last non-zero column in each row of a pandas dataframeAdd one row to pandas DataFrameSelecting...

What is the purpose of putting a capacitor on the primary side of a step-down transformer?

What are good ways to spray paint a QR code on a footpath?

Can a function nowhere continuous have a connected graph?

Why do we use a cylinder as a Gaussian surface for infinitely long charged wire?

What's the rule for a natural 20 on a Perception check?

Is there reliable evidence that depleted uranium from the 1999 NATO bombing is causing cancer in Serbia?

One folder having two different locations on Ubuntu 18.04

Are all commands with an optional argument fragile?

Can Aziraphale and Crowley actually become native?

Buliding a larger matrix from a smaller one

Could human civilization live 150 years in a nuclear-powered aircraft carrier colony without resorting to mass killing/ cannibalism?

Is the location of an aircraft spoiler really that vital?

Who voices the character "Finger" in The Fifth Element?

Adjective for 'made of pus' or 'corrupted by pus' or something of something of pus

Donkey as Democratic Party symbolic animal

Does a return economy-class seat between London and San Francisco release 5.28 t of CO2e?

Are these intended activities legal to do in the USA under the VWP?

How to securely dispose of a smartphone?

Can I travel from Germany to England alone as an unaccompanied minor?

Is this homebrew Half-Phoenix race balanced?

What is "oversubscription" in Networking?

Indexes getting highly fragmented during normal usage of system

How can I deal with extreme temperatures in a hotel room?

How can I write a panicked scene without it feeling like it was written in haste?

Find first and last non-zero column in each row of a pandas dataframe

Add one row to pandas DataFrameSelecting multiple columns in a pandas dataframeAdding new column to existing DataFrame in Python pandasDelete column from pandas DataFrameHow to drop rows of Pandas DataFrame whose value in certain columns is NaNHow do I get the row count of a pandas DataFrame?How to iterate over rows in a DataFrame in Pandas?Select rows from a DataFrame based on values in a column in pandasDeleting DataFrame row in Pandas based on column valueGet list from pandas DataFrame column headers

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

I have DataFrame in view of Name and Date with values of weights in cells :

Name        Jan17  Jun18  Dec18    Apr19  count 

Nick         0      1.7     3.7      0     2

Jack         0       0      2.8     3.5    2       

Fox          0      1.7      0       0     1

Rex          1.0     0      3.0     4.2    3

Snack        0       0      2.8     4.4    2

Yosee        0       0       0      4.3    1 

Petty        0.5    1.3     2.8     3.5    4

Start and Finish should be added to the dataFrame in reference to the next definition:

Start first non zero value in row started from Jan17
column to Apr19

Finish first non zero value in
sequence Apr19 till to Jan17

Also, if row has only one non-zero value in row then Start andFinish are the same.

To find first non zero element in row I tried data[col].keys, np.argmax() and it works as expected.

date_col_list = ['Jan17','Jun18','Dec18', 'Apr19']

data['Start']=data[date_col_list].keys([np.argmax(data[date_col_list].values!=0, axis=1)]

Result is:

Name        Jan17  Jun18  Dec18    Apr19  count   Start 

Nick         0      1.7     3.7      0     2      Jun18        

Jack         0       0      2.8     3.5    2      Dec18           

Fox          0      1.7      0       0     1      Jun18    

Rex          1.0     0      3.0     4.2    3      Jan18    

Snack        0       0      2.8     4.4    2      Dec18    

Yosee        0       0       0      4.3    1      Apr19     

Petty        0.5    1.3     2.8     3.5    4      Jan17

To detect values for Finish column I tried to use:
np.apply_along_axis as:
def func_X(i):
return np.argmax(np.where(i!=0))

np.apply_along_axis(func1d = func_X, axis=1, arr=data[date_col_list].values)

Result is error:
'tuple' object has no attribute 'argmax'

Expected dataframe is:

Name        Jan17  Jun18  Dec18    Apr19  count   Start  Finish

Nick         0      1.7     3.7      0     2      Jun18   Dec18     

Jack         0       0      2.8     3.5    2      Dec18   Apr19        

Fox          0      1.7      0       0     1      Jun18   Jun18 

Rex          1.0     0      3.0     4.2    3      Jan18   Apr19 

Snack        0       0      2.8     4.4    2      Dec18   Apr19 

Yosee        0       0       0      4.3    1      Apr19   Apr19  

Petty        0.5    1.3     2.8     3.5    4      Jan17   Apr19

How can I find Finish in reference to non-zero value in direction from the last column (Apr19) to the first one (Jan17)?

edited 9 hours ago

cs95

155k26 gold badges208 silver badges278 bronze badges

asked 9 hours ago

Cindy

11311 bronze badges

add a comment |

I have DataFrame in view of Name and Date with values of weights in cells :

Name        Jan17  Jun18  Dec18    Apr19  count 

Nick         0      1.7     3.7      0     2

Jack         0       0      2.8     3.5    2       

Fox          0      1.7      0       0     1

Rex          1.0     0      3.0     4.2    3

Snack        0       0      2.8     4.4    2

Yosee        0       0       0      4.3    1 

Petty        0.5    1.3     2.8     3.5    4

Start and Finish should be added to the dataFrame in reference to the next definition:

Start first non zero value in row started from Jan17
column to Apr19

Finish first non zero value in
sequence Apr19 till to Jan17

Also, if row has only one non-zero value in row then Start andFinish are the same.

To find first non zero element in row I tried data[col].keys, np.argmax() and it works as expected.

date_col_list = ['Jan17','Jun18','Dec18', 'Apr19']

data['Start']=data[date_col_list].keys([np.argmax(data[date_col_list].values!=0, axis=1)]

Result is:

Name        Jan17  Jun18  Dec18    Apr19  count   Start 

Nick         0      1.7     3.7      0     2      Jun18        

Jack         0       0      2.8     3.5    2      Dec18           

Fox          0      1.7      0       0     1      Jun18    

Rex          1.0     0      3.0     4.2    3      Jan18    

Snack        0       0      2.8     4.4    2      Dec18    

Yosee        0       0       0      4.3    1      Apr19     

Petty        0.5    1.3     2.8     3.5    4      Jan17

To detect values for Finish column I tried to use:
np.apply_along_axis as:
def func_X(i):
return np.argmax(np.where(i!=0))

np.apply_along_axis(func1d = func_X, axis=1, arr=data[date_col_list].values)

Result is error:
'tuple' object has no attribute 'argmax'

Expected dataframe is:

Name        Jan17  Jun18  Dec18    Apr19  count   Start  Finish

Nick         0      1.7     3.7      0     2      Jun18   Dec18     

Jack         0       0      2.8     3.5    2      Dec18   Apr19        

Fox          0      1.7      0       0     1      Jun18   Jun18 

Rex          1.0     0      3.0     4.2    3      Jan18   Apr19 

Snack        0       0      2.8     4.4    2      Dec18   Apr19 

Yosee        0       0       0      4.3    1      Apr19   Apr19  

Petty        0.5    1.3     2.8     3.5    4      Jan17   Apr19

How can I find Finish in reference to non-zero value in direction from the last column (Apr19) to the first one (Jan17)?

edited 9 hours ago

cs95

155k26 gold badges208 silver badges278 bronze badges

asked 9 hours ago

Cindy

11311 bronze badges

add a comment |

I have DataFrame in view of Name and Date with values of weights in cells :

Name        Jan17  Jun18  Dec18    Apr19  count 

Nick         0      1.7     3.7      0     2

Jack         0       0      2.8     3.5    2       

Fox          0      1.7      0       0     1

Rex          1.0     0      3.0     4.2    3

Snack        0       0      2.8     4.4    2

Yosee        0       0       0      4.3    1 

Petty        0.5    1.3     2.8     3.5    4

Start and Finish should be added to the dataFrame in reference to the next definition:

Start first non zero value in row started from Jan17
column to Apr19

Finish first non zero value in
sequence Apr19 till to Jan17

Also, if row has only one non-zero value in row then Start andFinish are the same.

To find first non zero element in row I tried data[col].keys, np.argmax() and it works as expected.

date_col_list = ['Jan17','Jun18','Dec18', 'Apr19']

data['Start']=data[date_col_list].keys([np.argmax(data[date_col_list].values!=0, axis=1)]

Result is:

Name        Jan17  Jun18  Dec18    Apr19  count   Start 

Nick         0      1.7     3.7      0     2      Jun18        

Jack         0       0      2.8     3.5    2      Dec18           

Fox          0      1.7      0       0     1      Jun18    

Rex          1.0     0      3.0     4.2    3      Jan18    

Snack        0       0      2.8     4.4    2      Dec18    

Yosee        0       0       0      4.3    1      Apr19     

Petty        0.5    1.3     2.8     3.5    4      Jan17

To detect values for Finish column I tried to use:
np.apply_along_axis as:
def func_X(i):
return np.argmax(np.where(i!=0))

np.apply_along_axis(func1d = func_X, axis=1, arr=data[date_col_list].values)

Result is error:
'tuple' object has no attribute 'argmax'

Expected dataframe is:

Name        Jan17  Jun18  Dec18    Apr19  count   Start  Finish

Nick         0      1.7     3.7      0     2      Jun18   Dec18     

Jack         0       0      2.8     3.5    2      Dec18   Apr19        

Fox          0      1.7      0       0     1      Jun18   Jun18 

Rex          1.0     0      3.0     4.2    3      Jan18   Apr19 

Snack        0       0      2.8     4.4    2      Dec18   Apr19 

Yosee        0       0       0      4.3    1      Apr19   Apr19  

Petty        0.5    1.3     2.8     3.5    4      Jan17   Apr19

How can I find Finish in reference to non-zero value in direction from the last column (Apr19) to the first one (Jan17)?

edited 9 hours ago

cs95

155k26 gold badges208 silver badges278 bronze badges

asked 9 hours ago

Cindy

11311 bronze badges

I have DataFrame in view of Name and Date with values of weights in cells :

Name        Jan17  Jun18  Dec18    Apr19  count 

Nick         0      1.7     3.7      0     2

Jack         0       0      2.8     3.5    2       

Fox          0      1.7      0       0     1

Rex          1.0     0      3.0     4.2    3

Snack        0       0      2.8     4.4    2

Yosee        0       0       0      4.3    1 

Petty        0.5    1.3     2.8     3.5    4

Start and Finish should be added to the dataFrame in reference to the next definition:

Start first non zero value in row started from Jan17
column to Apr19

Finish first non zero value in
sequence Apr19 till to Jan17

Also, if row has only one non-zero value in row then Start andFinish are the same.

To find first non zero element in row I tried data[col].keys, np.argmax() and it works as expected.

date_col_list = ['Jan17','Jun18','Dec18', 'Apr19']

data['Start']=data[date_col_list].keys([np.argmax(data[date_col_list].values!=0, axis=1)]

Result is:

Name        Jan17  Jun18  Dec18    Apr19  count   Start 

Nick         0      1.7     3.7      0     2      Jun18        

Jack         0       0      2.8     3.5    2      Dec18           

Fox          0      1.7      0       0     1      Jun18    

Rex          1.0     0      3.0     4.2    3      Jan18    

Snack        0       0      2.8     4.4    2      Dec18    

Yosee        0       0       0      4.3    1      Apr19     

Petty        0.5    1.3     2.8     3.5    4      Jan17

To detect values for Finish column I tried to use:
np.apply_along_axis as:
def func_X(i):
return np.argmax(np.where(i!=0))

np.apply_along_axis(func1d = func_X, axis=1, arr=data[date_col_list].values)

Result is error:
'tuple' object has no attribute 'argmax'

Expected dataframe is:

Name        Jan17  Jun18  Dec18    Apr19  count   Start  Finish

Nick         0      1.7     3.7      0     2      Jun18   Dec18     

Jack         0       0      2.8     3.5    2      Dec18   Apr19        

Fox          0      1.7      0       0     1      Jun18   Jun18 

Rex          1.0     0      3.0     4.2    3      Jan18   Apr19 

Snack        0       0      2.8     4.4    2      Dec18   Apr19 

Yosee        0       0       0      4.3    1      Apr19   Apr19  

Petty        0.5    1.3     2.8     3.5    4      Jan17   Apr19

How can I find Finish in reference to non-zero value in direction from the last column (Apr19) to the first one (Jan17)?

python pandas dataframe argmax

edited 9 hours ago

cs95

155k26 gold badges208 silver badges278 bronze badges

asked 9 hours ago

Cindy

11311 bronze badges

edited 9 hours ago

cs95

155k26 gold badges208 silver badges278 bronze badges

asked 9 hours ago

Cindy

11311 bronze badges

edited 9 hours ago

cs95

155k26 gold badges208 silver badges278 bronze badges

edited 9 hours ago

cs95

155k26 gold badges208 silver badges278 bronze badges

edited 9 hours ago

cs95

155k26 gold badges208 silver badges278 bronze badges

asked 9 hours ago

Cindy

11311 bronze badges

asked 9 hours ago

Cindy

11311 bronze badges

asked 9 hours ago

Cindy

11311 bronze badges

add a comment |

4 Answers
4

active

oldest

votes

`first_valid_index` and `last_valid_index`

d = df.mask(df == 0).drop(['Name', 'count'], 1)

df.assign(

    Start=d.apply(pd.Series.first_valid_index, 1),

    Finish=d.apply(pd.Series.last_valid_index, 1)

)



    Name  Jan17  Jun18  Dec18  Apr19  count  Start Finish

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

`stack` then `groupby`

d = df.mask(df == 0).drop(['Name', 'count'], 1)

def fl(s): return s.xs(s.name).index[[0, -1]]

s, f = d.stack().groupby(level=0).apply(fl).str

df.assign(Start=s, Finish=f)



    Name  Jan17  Jun18  Dec18  Apr19  count  Start Finish

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

edited 9 hours ago

answered 9 hours ago

piRSquared

169k25 gold badges179 silver badges329 bronze badges

add a comment |

In your case try something different with dot

s=df.loc[:,'Jan17':'Apr19'].ne(0)

s=s.dot(s.columns+',').str[:-1].str.split(',')

s.str[0],s.str[-1]

Out[899]: 

(0    Jun18

 1    Dec18

 2    Jun18

 3    Jan17

 4    Dec18

 5    Apr19

 6    Jan17

 dtype: object, 0    Dec18

 1    Apr19

 2    Jun18

 3    Apr19

 4    Apr19

 5    Apr19

 6    Apr19

 dtype: object)

 #df['Start'],df['End']=s.str[0],s.str[-1]

answered 9 hours ago

WeNYoBen

142k8 gold badges51 silver badges79 bronze badges

add a comment |

Using cumsum on the underlying array

m = df.drop(['Name', 'count'], axis=1)

u = m.to_numpy().cumsum(1)



start = (u!=0).argmax(1)

end = u.argmax(1)



df.assign(start=m.columns[start], end=m.columns[end])

    Name  Jan17  Jun18  Dec18  Apr19  count  start    end

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

answered 9 hours ago

user3483203

34.9k8 gold badges31 silver badges60 bronze badges

add a comment |

`idxmax`

mask = df.drop(['Name', 'count'], axis=1) > 0

df.assign(start=mask.idxmax(axis=1), end=mask.iloc[:,::-1].idxmax(axis=1))



    Name  Jan17  Jun18  Dec18  Apr19  count  start    end

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

Drop irrelevant columns, then use idxmax first on the columns, then on the reversed columns to find the first and last valid indices respectively.

edited 9 hours ago

answered 9 hours ago

cs95

155k26 gold badges208 silver badges278 bronze badges

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');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f56757092%2ffind-first-and-last-non-zero-column-in-each-row-of-a-pandas-dataframe%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

4 Answers
4

active

oldest

votes

4 Answers
4

active

oldest

votes

`first_valid_index` and `last_valid_index`

d = df.mask(df == 0).drop(['Name', 'count'], 1)

df.assign(

    Start=d.apply(pd.Series.first_valid_index, 1),

    Finish=d.apply(pd.Series.last_valid_index, 1)

)



    Name  Jan17  Jun18  Dec18  Apr19  count  Start Finish

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

`stack` then `groupby`

d = df.mask(df == 0).drop(['Name', 'count'], 1)

def fl(s): return s.xs(s.name).index[[0, -1]]

s, f = d.stack().groupby(level=0).apply(fl).str

df.assign(Start=s, Finish=f)



    Name  Jan17  Jun18  Dec18  Apr19  count  Start Finish

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

edited 9 hours ago

answered 9 hours ago

piRSquared

169k25 gold badges179 silver badges329 bronze badges

add a comment |

`first_valid_index` and `last_valid_index`

d = df.mask(df == 0).drop(['Name', 'count'], 1)

df.assign(

    Start=d.apply(pd.Series.first_valid_index, 1),

    Finish=d.apply(pd.Series.last_valid_index, 1)

)



    Name  Jan17  Jun18  Dec18  Apr19  count  Start Finish

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

`stack` then `groupby`

d = df.mask(df == 0).drop(['Name', 'count'], 1)

def fl(s): return s.xs(s.name).index[[0, -1]]

s, f = d.stack().groupby(level=0).apply(fl).str

df.assign(Start=s, Finish=f)



    Name  Jan17  Jun18  Dec18  Apr19  count  Start Finish

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

edited 9 hours ago

answered 9 hours ago

piRSquared

169k25 gold badges179 silver badges329 bronze badges

add a comment |

`first_valid_index` and `last_valid_index`

d = df.mask(df == 0).drop(['Name', 'count'], 1)

df.assign(

    Start=d.apply(pd.Series.first_valid_index, 1),

    Finish=d.apply(pd.Series.last_valid_index, 1)

)



    Name  Jan17  Jun18  Dec18  Apr19  count  Start Finish

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

`stack` then `groupby`

d = df.mask(df == 0).drop(['Name', 'count'], 1)

def fl(s): return s.xs(s.name).index[[0, -1]]

s, f = d.stack().groupby(level=0).apply(fl).str

df.assign(Start=s, Finish=f)



    Name  Jan17  Jun18  Dec18  Apr19  count  Start Finish

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

edited 9 hours ago

answered 9 hours ago

piRSquared

169k25 gold badges179 silver badges329 bronze badges

`first_valid_index` and `last_valid_index`

d = df.mask(df == 0).drop(['Name', 'count'], 1)

df.assign(

    Start=d.apply(pd.Series.first_valid_index, 1),

    Finish=d.apply(pd.Series.last_valid_index, 1)

)



    Name  Jan17  Jun18  Dec18  Apr19  count  Start Finish

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

`stack` then `groupby`

d = df.mask(df == 0).drop(['Name', 'count'], 1)

def fl(s): return s.xs(s.name).index[[0, -1]]

s, f = d.stack().groupby(level=0).apply(fl).str

df.assign(Start=s, Finish=f)



    Name  Jan17  Jun18  Dec18  Apr19  count  Start Finish

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

edited 9 hours ago

answered 9 hours ago

piRSquared

169k25 gold badges179 silver badges329 bronze badges

edited 9 hours ago

answered 9 hours ago

piRSquared

169k25 gold badges179 silver badges329 bronze badges

answered 9 hours ago

piRSquared

169k25 gold badges179 silver badges329 bronze badges

answered 9 hours ago

piRSquared

169k25 gold badges179 silver badges329 bronze badges

add a comment |

In your case try something different with dot

s=df.loc[:,'Jan17':'Apr19'].ne(0)

s=s.dot(s.columns+',').str[:-1].str.split(',')

s.str[0],s.str[-1]

Out[899]: 

(0    Jun18

 1    Dec18

 2    Jun18

 3    Jan17

 4    Dec18

 5    Apr19

 6    Jan17

 dtype: object, 0    Dec18

 1    Apr19

 2    Jun18

 3    Apr19

 4    Apr19

 5    Apr19

 6    Apr19

 dtype: object)

 #df['Start'],df['End']=s.str[0],s.str[-1]

answered 9 hours ago

WeNYoBen

142k8 gold badges51 silver badges79 bronze badges

add a comment |

In your case try something different with dot

s=df.loc[:,'Jan17':'Apr19'].ne(0)

s=s.dot(s.columns+',').str[:-1].str.split(',')

s.str[0],s.str[-1]

Out[899]: 

(0    Jun18

 1    Dec18

 2    Jun18

 3    Jan17

 4    Dec18

 5    Apr19

 6    Jan17

 dtype: object, 0    Dec18

 1    Apr19

 2    Jun18

 3    Apr19

 4    Apr19

 5    Apr19

 6    Apr19

 dtype: object)

 #df['Start'],df['End']=s.str[0],s.str[-1]

answered 9 hours ago

WeNYoBen

142k8 gold badges51 silver badges79 bronze badges

add a comment |

In your case try something different with dot

s=df.loc[:,'Jan17':'Apr19'].ne(0)

s=s.dot(s.columns+',').str[:-1].str.split(',')

s.str[0],s.str[-1]

Out[899]: 

(0    Jun18

 1    Dec18

 2    Jun18

 3    Jan17

 4    Dec18

 5    Apr19

 6    Jan17

 dtype: object, 0    Dec18

 1    Apr19

 2    Jun18

 3    Apr19

 4    Apr19

 5    Apr19

 6    Apr19

 dtype: object)

 #df['Start'],df['End']=s.str[0],s.str[-1]

answered 9 hours ago

WeNYoBen

142k8 gold badges51 silver badges79 bronze badges

In your case try something different with dot

s=df.loc[:,'Jan17':'Apr19'].ne(0)

s=s.dot(s.columns+',').str[:-1].str.split(',')

s.str[0],s.str[-1]

Out[899]: 

(0    Jun18

 1    Dec18

 2    Jun18

 3    Jan17

 4    Dec18

 5    Apr19

 6    Jan17

 dtype: object, 0    Dec18

 1    Apr19

 2    Jun18

 3    Apr19

 4    Apr19

 5    Apr19

 6    Apr19

 dtype: object)

 #df['Start'],df['End']=s.str[0],s.str[-1]

answered 9 hours ago

WeNYoBen

142k8 gold badges51 silver badges79 bronze badges

answered 9 hours ago

WeNYoBen

142k8 gold badges51 silver badges79 bronze badges

answered 9 hours ago

WeNYoBen

142k8 gold badges51 silver badges79 bronze badges

answered 9 hours ago

WeNYoBen

142k8 gold badges51 silver badges79 bronze badges

add a comment |

Using cumsum on the underlying array

m = df.drop(['Name', 'count'], axis=1)

u = m.to_numpy().cumsum(1)



start = (u!=0).argmax(1)

end = u.argmax(1)



df.assign(start=m.columns[start], end=m.columns[end])

    Name  Jan17  Jun18  Dec18  Apr19  count  start    end

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

answered 9 hours ago

user3483203

34.9k8 gold badges31 silver badges60 bronze badges

add a comment |

Using cumsum on the underlying array

m = df.drop(['Name', 'count'], axis=1)

u = m.to_numpy().cumsum(1)



start = (u!=0).argmax(1)

end = u.argmax(1)



df.assign(start=m.columns[start], end=m.columns[end])

    Name  Jan17  Jun18  Dec18  Apr19  count  start    end

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

answered 9 hours ago

user3483203

34.9k8 gold badges31 silver badges60 bronze badges

add a comment |

Using cumsum on the underlying array

m = df.drop(['Name', 'count'], axis=1)

u = m.to_numpy().cumsum(1)



start = (u!=0).argmax(1)

end = u.argmax(1)



df.assign(start=m.columns[start], end=m.columns[end])

    Name  Jan17  Jun18  Dec18  Apr19  count  start    end

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

answered 9 hours ago

user3483203

34.9k8 gold badges31 silver badges60 bronze badges

Using cumsum on the underlying array

m = df.drop(['Name', 'count'], axis=1)

u = m.to_numpy().cumsum(1)



start = (u!=0).argmax(1)

end = u.argmax(1)



df.assign(start=m.columns[start], end=m.columns[end])

    Name  Jan17  Jun18  Dec18  Apr19  count  start    end

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

answered 9 hours ago

user3483203

34.9k8 gold badges31 silver badges60 bronze badges

answered 9 hours ago

user3483203

34.9k8 gold badges31 silver badges60 bronze badges

answered 9 hours ago

user3483203

34.9k8 gold badges31 silver badges60 bronze badges

answered 9 hours ago

user3483203

34.9k8 gold badges31 silver badges60 bronze badges

add a comment |

`idxmax`

mask = df.drop(['Name', 'count'], axis=1) > 0

df.assign(start=mask.idxmax(axis=1), end=mask.iloc[:,::-1].idxmax(axis=1))



    Name  Jan17  Jun18  Dec18  Apr19  count  start    end

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

Drop irrelevant columns, then use idxmax first on the columns, then on the reversed columns to find the first and last valid indices respectively.

edited 9 hours ago

answered 9 hours ago

cs95

155k26 gold badges208 silver badges278 bronze badges

add a comment |

`idxmax`

mask = df.drop(['Name', 'count'], axis=1) > 0

df.assign(start=mask.idxmax(axis=1), end=mask.iloc[:,::-1].idxmax(axis=1))



    Name  Jan17  Jun18  Dec18  Apr19  count  start    end

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

Drop irrelevant columns, then use idxmax first on the columns, then on the reversed columns to find the first and last valid indices respectively.

edited 9 hours ago

answered 9 hours ago

cs95

155k26 gold badges208 silver badges278 bronze badges

add a comment |

`idxmax`

mask = df.drop(['Name', 'count'], axis=1) > 0

df.assign(start=mask.idxmax(axis=1), end=mask.iloc[:,::-1].idxmax(axis=1))



    Name  Jan17  Jun18  Dec18  Apr19  count  start    end

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

Drop irrelevant columns, then use idxmax first on the columns, then on the reversed columns to find the first and last valid indices respectively.

edited 9 hours ago

answered 9 hours ago

cs95

155k26 gold badges208 silver badges278 bronze badges

`idxmax`

mask = df.drop(['Name', 'count'], axis=1) > 0

df.assign(start=mask.idxmax(axis=1), end=mask.iloc[:,::-1].idxmax(axis=1))



    Name  Jan17  Jun18  Dec18  Apr19  count  start    end

0   Nick    0.0    1.7    3.7    0.0      2  Jun18  Dec18

1   Jack    0.0    0.0    2.8    3.5      2  Dec18  Apr19

2    Fox    0.0    1.7    0.0    0.0      1  Jun18  Jun18

3    Rex    1.0    0.0    3.0    4.2      3  Jan17  Apr19

4  Snack    0.0    0.0    2.8    4.4      2  Dec18  Apr19

5  Yosee    0.0    0.0    0.0    4.3      1  Apr19  Apr19

6  Petty    0.5    1.3    2.8    3.5      4  Jan17  Apr19

Drop irrelevant columns, then use idxmax first on the columns, then on the reversed columns to find the first and last valid indices respectively.

edited 9 hours ago

answered 9 hours ago

cs95

155k26 gold badges208 silver badges278 bronze badges

edited 9 hours ago

answered 9 hours ago

cs95

155k26 gold badges208 silver badges278 bronze badges

answered 9 hours ago

cs95

155k26 gold badges208 silver badges278 bronze badges

answered 9 hours ago

cs95

155k26 gold badges208 silver badges278 bronze badges

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Mdthbs

Find first and last non-zero column in each row of a pandas dataframeAdd one row to pandas DataFrameSelecting...

4 Answers
4

`first_valid_index` and `last_valid_index`

`stack` then `groupby`

`idxmax`

Your Answer

Post as a guest

4 Answers
4

4 Answers
4

`first_valid_index` and `last_valid_index`

`stack` then `groupby`

`first_valid_index` and `last_valid_index`

`stack` then `groupby`

`first_valid_index` and `last_valid_index`

`stack` then `groupby`

`first_valid_index` and `last_valid_index`

`stack` then `groupby`

`idxmax`

`idxmax`

`idxmax`

`idxmax`

Post as a guest

Popular posts from this blog

Hudson River Historic District Contents Geography History The district today Aesthetics Cultural...

The number designs the writing. Feandra Aversely Definition: The act of ingrafting a sprig or shoot of one...

Ayherre Geografie Demografie Externe links Navigatiemenu43° 23′ NB, 1° 15′ WL43° 23′ NB, 1°...

Find first and last non-zero column in each row of a pandas dataframeAdd one row to pandas DataFrameSelecting...

4 Answers 4

first_valid_index and last_valid_index

stack then groupby

idxmax

Your Answer

Sign up or log in

Post as a guest

Post as a guest

4 Answers 4

4 Answers 4

first_valid_index and last_valid_index

stack then groupby

first_valid_index and last_valid_index

stack then groupby

first_valid_index and last_valid_index

stack then groupby

first_valid_index and last_valid_index

stack then groupby

idxmax

idxmax

idxmax

idxmax

Sign up or log in

Post as a guest

Post as a guest

Sign up or log in

Post as a guest

Sign up or log in

Post as a guest

Sign up or log in

Post as a guest

Popular posts from this blog

Hudson River Historic District Contents Geography History The district today Aesthetics Cultural...

The number designs the writing. Feandra Aversely Definition: The act of ingrafting a sprig or shoot of one...

Ayherre Geografie Demografie Externe links Navigatiemenu43° 23′ NB, 1° 15′ WL43° 23′ NB, 1°...

4 Answers
4

`first_valid_index` and `last_valid_index`

`stack` then `groupby`

`idxmax`

4 Answers
4

4 Answers
4

`first_valid_index` and `last_valid_index`

`stack` then `groupby`

`first_valid_index` and `last_valid_index`

`stack` then `groupby`

`first_valid_index` and `last_valid_index`

`stack` then `groupby`

`first_valid_index` and `last_valid_index`

`stack` then `groupby`

`idxmax`

`idxmax`

`idxmax`

`idxmax`