Require monotonic trend of variables by iterating over rows in dataframe

Question

I want monotonic trend of a variables. Data looks like:

Output is required in the form of:

I have tried to iterate over rows using apply but got no luck. I have also tried is_monotonic_increasing and is_monotonic_decreasing from pandas.

Thanks in advance for your help.

Answer 1

pd.Series.is_monotonic_decreasing can work for your case.

I would first pivot the dataframe in order to get each varaible in a column, then for each column drop nans (otherwise is_monotonic_decreasing will always return False), apply both is_monotonic_decreasing and is_monotonic_increasing and collect the results:

df = pd.DataFrame(
    {
        "var_name": ["var1", "var1", "var1", "var1", "var2", "var2", "var2", "var2", "var2", "var2", "var3", "var3", "var3"],
        "Bins": [0, 1, 2, 3, 0, 1, 2, 3, 4, 5, 0, 1, 2],
        "values": [162.5, 122, -15, -92, 40, 87, -37, 16, -69, 102, 17, 52, 109]
    })

df = df.pivot(index="Bins", values="values", columns="var_name")

result = {}
for col in df.columns:
    if df[col].dropna.is_monotonic_decreasing:
        result[col] = "Decreasing"
    elif df[col].dropna.is_monotonic_increasing:
        result[col] = "Increasing"
    else:
        result[col] = "No trend"