Name: Towards AI Legal Name: Towards AI, Inc. Description: Towards AI is the world's leading artificial intelligence (AI) and technology publication. Read by thought-leaders and decision-makers around the world. Phone Number: +1-650-246-9381 Email: [email protected]
228 Park Avenue South New York, NY 10003 United States
Website: Publisher: https://towardsai.net/#publisher Diversity Policy: https://towardsai.net/about Ethics Policy: https://towardsai.net/about Masthead: https://towardsai.net/about
Name: Towards AI Legal Name: Towards AI, Inc. Description: Towards AI is the world's leading artificial intelligence (AI) and technology publication. Founders: Roberto Iriondo, , Job Title: Co-founder and Advisor Works for: Towards AI, Inc. Follow Roberto: X, LinkedIn, GitHub, Google Scholar, Towards AI Profile, Medium, ML@CMU, FreeCodeCamp, Crunchbase, Bloomberg, Roberto Iriondo, Generative AI Lab, Generative AI Lab Denis Piffaretti, Job Title: Co-founder Works for: Towards AI, Inc. Louie Peters, Job Title: Co-founder Works for: Towards AI, Inc. Louis-François Bouchard, Job Title: Co-founder Works for: Towards AI, Inc. Cover:
Towards AI Cover
Logo:
Towards AI Logo
Areas Served: Worldwide Alternate Name: Towards AI, Inc. Alternate Name: Towards AI Co. Alternate Name: towards ai Alternate Name: towardsai Alternate Name: towards.ai Alternate Name: tai Alternate Name: toward ai Alternate Name: toward.ai Alternate Name: Towards AI, Inc. Alternate Name: towardsai.net Alternate Name: pub.towardsai.net
5 stars – based on 497 reviews

Frequently Used, Contextual References

TODO: Remember to copy unique IDs whenever it needs used. i.e., URL: 304b2e42315e

Resources

Unlock the full potential of AI with Building LLMs for Productionβ€”our 470+ page guide to mastering LLMs with practical projects and expert insights!

Publication

Survival Analysis can be done in excel too.
Latest

Survival Analysis can be done in excel too.

Last Updated on September 9, 2021 by Editorial Team

Author(s): Supriya Ghosh

Data Analysis

Image by Peggy und Marco Lachmann-Anke fromΒ Pixabay

The intention of writing this article is to show you all, β€œ how Survival Analysis can be done using simple formulas ofΒ excel”.

Before I proceed further, let me share a formal definition of Survival analysis.

Photo by Denny Ryanto onΒ Unsplash

β€œSurvival analysis is a collection of statistical procedures for data analysis where the outcome variable of interest is time until an event occurs. The survivor function represents the probability that an individual survives from the time of origin to sometime beyond time,Β t.”

Keeping this definition in mind let us now proceed with our objective of implementing Survival Analysis usingΒ excel.

Data set forΒ Analysis

I am considering a simple Case of the Manufacturing Unit where a bunch of old Machines having high chances of breakdown is chosen for Maintenance. Our job is to perform Survival Analysis and find out the probability of Survival of these machines after the end of the Maintenance period.

Photo by Possessed Photography onΒ Unsplash

The data set shows how long old machines were under maintenance (column A) and whether machines β€œbroke down or not” after the end of the maintenance period(column B).

By Author

Here 1 = Machine Breakdown and 0 = Machine Available

and total number of Machines included in the population =Β 20.

Data needs to be modified in order to convert it into the correct format to create a SurvivalΒ curve.

Formatting theΒ Data

Adding one more column, D as β€œTime” showing unique Months of Maintenance. The first value should start withΒ 0.

By Author

Creating new columns asΒ required

New columns are createdΒ as

1. β€œMachine Breakdown”,

2. β€œMachine Available”,

3. β€œ1- (Machine Breakdown / Machine Available) β€œ which is also called as (1β€Šβ€”β€ŠHazard) where Hazard = (Machine Breakdown / Machine Available) and

4. β€œS(t)”(Survival Function).

The values in the column are filled using excel formulas.

The first value in the column, β€œS(t)” is 1 as at starting time (t = 0)Β , all Machines are considered to be Available and Working with Survival function asΒ 1.

By Author

Filling up individual columns with the required ExcelΒ Formulas

By Author

1. Starting with the β€œMachine Breakdown” column.

The first row is kept blank as there was no machine Breakdown at the time instanceΒ β€œ0”.

The value in the 2nd row of the β€œMachine Breakdown” column is calculated using theΒ formula

E3: =COUNTIFS($A$2:$A$21,D3,$B$2:$B$21,1)

The other rows of this column are filled by simply highlighting the range E3:E19 and pressing Ctrl-D. Fill in all of the other values in columns F through H using the sameΒ trick.

This counts the number of Machines that Brokedown at particular time instances.

2. For the β€œMachine Available” column, the formula used isΒ :

F2: =COUNTIF($A$2:$A$21, β€œ>”&D2–1)

This counts the number of Machines available at particular time instances after removing the machines which brokeΒ down.

3. For calculating the β€œ1β€Šβ€”β€Š(Machine Breakdown/Machine Available)” column, the formula used isΒ :

G3: =1-(E3/F3)

It represents Hazard which is given by (Ej/Fj) where j =Β 1…19

This is computed as it helps in computing the Survival Function, S(t) represented below.

Survival function in Survival Analysis is interpreted as the probability that a certain object of interest will survive beyond a certain time, t. The value of the function lies between 0 and 1(inclusive) and it is a non-increasing function.

4. For the β€œS(t) column, the formula used isΒ :

H3: =H2*G3

This computes the Survival Probability.

The value in the 1st row of this column is 1, as an instance (t = 0), all machines are available. There is no breakdown.

Now for creating the survival curve, we need to follow certainΒ steps.

Step 1: Values in columns D and H are copied into columns J andΒ K.

By Author

Step 2: Values in the range J3:J19 are copied to J20:J36. And then values in the range K2:K18 are copied toΒ K20:K36.

By Author

Step 3: The list of values in column L is the sequence of numbers as shown in the belowΒ table.

By Author

Step 4: Columns J through L are sorted from smallest to largest based on columnΒ L.

By Author

Step 5: Cells J2:K36 are highlighted to create a β€œScatter Plot with Straight Lines and Markers” option which is our final Survival Curve (Kaplan-Meier Curve).

Kaplan-Meier is a non-parametric analysis, also known as the product-limit method, used for estimating the survival function based on the time to the occurrence of theΒ event.

By Author

Survival Curve (Kaplan – MeierΒ Curve)

By Author

How to interpret this Survival Curve/Kaplan MeierΒ Curve

Kaplan-Meier curve is the visual representation of estimate of Survival function, and it shows what the probability of an event (for example, survival) is at a certain time interval.

From the above Survival Curve, it can be interpreted asΒ :

After a time period of 8 months, the survival probability of machines included in the population is close to 0.80 i.e.,Β 80%.

Similarly, after a time period of 17 months, the survival probability of machines included in the population is close to 0.60 i.e.,Β 60%.

And after a time period of 22 months, the survival probability of machines included in the population is close to 0.20 i.e.,Β 20%.

With this, I conclude.

Hope you enjoyed reading thisΒ article.

You can follow me on medium as wellΒ as

LinkedIn: SupriyaΒ Ghosh

And Twitter: @isupriyaghosh

This will motivate me to be more and more content forΒ you.


Survival Analysis can be done in excel too. was originally published in Towards AI on Medium, where people are continuing the conversation by highlighting and responding to this story.

Published via Towards AI

Feedback ↓