idxmin/idxmax in Deedle C#

Question

I have the following series: Series<new { string, DateTime }, double>. I wanna know when the drawdown starts and ends. Drawdown start date can be found by drawdown.Min().Index. Unfortunately, I don't find a similar method like .idxmin()/idxmax() from pandas. How do I find min and max indices?

var dataSeries = data.Select(e => KeyValue.Create(new { e.Pair, Date = e.CloseDate }, (double)e.ProfitPercentage)).ToSeries();

var cumSum = dataSeries.CumulativeSum();
var cumMax = cumSum.CumulativeMax();
var drawdown = cumSum - cumMax;

Extensions

/// <summary>
/// Utility extension methods for Deedle series/frames.
/// </summary>
public static class DeedleUtils
{
    /// <summary>
    /// Calculates the cumulative sum for the given series.
    /// </summary>
    /// <param name="input">Series to calculate cumulative sum for.</param>
    /// <returns>Cumulative sum in series form.</returns>
    public static Series<T, double> CumulativeSum<T>(this Series<T, double> input)
    {
        if (input.IsEmpty)
        {
            return input;
        }

        var prev = 0.0;

        return input.SelectValues(current =>
        {
            var sum = prev + current;
            prev = sum;

            return sum;
        });
    }

    /// <summary>
    /// Calculates the cumulative product of the series. This is equal to the python pandas method: `df.cumprod()`.
    /// </summary>
    /// <param name="input">Input series.</param>
    /// <returns>Cumulative product.</returns>
    public static Series<T, double> CumulativeProduct<T>(this Series<T, double> input)
    {
        if (input.IsEmpty)
        {
            return input;
        }

        var prev = 1.0;

        return input.SelectValues(current =>
        {
            var product = prev * current;
            prev = product;

            return product;
        });
    }

    /// <summary>
    /// Calculates the cumulative max of the series. This is equal to the python pandas method: `df.cummax()`.
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public static Series<T, double> CumulativeMax<T>(this Series<T, double> input)
    {
        if (input.IsEmpty)
        {
            return input;
        }

        var prevMax = double.NegativeInfinity;
        var values = new List<double>();

        foreach (var point in input.Values)
        {
            if (point > prevMax)
            {
                prevMax = point;
            }

            values.Add(prevMax);
        }

        return new Series<T, double>(input.Keys, values);
    }

    /// <summary>
    /// Calculates the percentage change from the previous value to the current.
    /// </summary>
    /// <param name="input">Series to calculate percentage change for.</param>
    /// <returns>Percentage change in series form.</returns>
    /// <remarks>Equivalent to `df.pct_change()`.</remarks>
    public static Series<T, double> PercentChange<T>(this Series<T, double> input)
    {
        if (input.IsEmpty)
        {
            return input;
        }

        var inputShifted = input.Shift(1);

        return (input - inputShifted) / inputShifted;
    }

    /// <summary>
    /// Calculates the cumulative returns series of the given input equity curve.
    /// </summary>
    /// <param name="input">Equity curve series.</param>
    /// <returns>Cumulative returns over time.</returns>
    public static Series<T, double> CumulativeReturns<T>(this Series<T, double> input)
    {
        if (input.IsEmpty)
        {
            return input;
        }

        return (input.PercentChange()
            .Where(kvp => !double.IsInfinity(kvp.Value)) + 1)
            .CumulativeProduct() - 1;
    }

    /// <summary>
    /// Calculates the total returns over a period of time for the given input.
    /// </summary>
    /// <param name="input">Equity curve series.</param>
    /// <returns>Total returns over time.</returns>
    public static double TotalReturns<T>(this Series<T, double> input)
    {
        var returns = input.CumulativeReturns();

        if (returns.IsEmpty)
        {
            return double.NaN;
        }

        return returns.LastValue();
    }
}

Edit

I'm trying to accomplish something like:

def calculate_max_drawdown(trades: pd.DataFrame, *, date_col: str = 'close_date',
                           value_col: str = 'profit_percent'
                           ) -> Tuple[float, pd.Timestamp, pd.Timestamp]:
    """
    Calculate max drawdown and the corresponding close dates
    :param trades: DataFrame containing trades (requires columns close_date and profit_percent)
    :param date_col: Column in DataFrame to use for dates (defaults to 'close_date')
    :param value_col: Column in DataFrame to use for values (defaults to 'profit_percent')
    :return: Tuple (float, highdate, lowdate) with absolute max drawdown, high and low time
    :raise: ValueError if trade-dataframe was found empty.
    """
    if len(trades) == 0:
        raise ValueError("Trade dataframe empty.")
    profit_results = trades.sort_values(date_col).reset_index(drop=True)
    max_drawdown_df = pd.DataFrame()
    max_drawdown_df['cumulative'] = profit_results[value_col].cumsum()
    max_drawdown_df['high_value'] = max_drawdown_df['cumulative'].cummax()
    max_drawdown_df['drawdown'] = max_drawdown_df['cumulative'] - max_drawdown_df['high_value']

    idxmin = max_drawdown_df['drawdown'].idxmin()
    if idxmin == 0:
        raise ValueError("No losing trade, therefore no drawdown.")
    high_date = profit_results.loc[max_drawdown_df.iloc[:idxmin]['high_value'].idxmax(), date_col]
    low_date = profit_results.loc[idxmin, date_col]
    return abs(min(max_drawdown_df['drawdown'])), high_date, low_date

Edit2

using Deedle;
using Microsoft.FSharp.Core;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;

namespace Resample
{
    class Program
    {
        public class JsonTimestampConverter : DateTimeConverterBase
        {
            public override bool CanConvert(Type objectType)
            {
                return objectType == typeof(long) || objectType == typeof(string);
            }

            public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
            {
                long milliseconds;

                if (reader.TokenType == JsonToken.Integer)
                {
                    milliseconds = (long)reader.Value!;
                }
                else if (reader.TokenType == JsonToken.String)
                {
                    if (!long.TryParse((string)reader.Value!, out milliseconds))
                    {
                        throw new JsonSerializationException($"Cannot convert invalid value to {objectType}.");
                    }
                }
                else
                {
                    throw new JsonSerializationException($"Unexpected token parsing date. Expected Integer or String, got {reader.TokenType}.");
                }

                return DateTimeOffset.FromUnixTimeMilliseconds(milliseconds).DateTime;
            }

            public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
            {
                DateTime utcTime;

                if (value is DateTime dateTime)
                {
                    utcTime = DateTime.SpecifyKind(dateTime, DateTimeKind.Utc);
                }
                else
                {
                    throw new JsonSerializationException("Expected date object value.");
                }

                writer.WriteRawValue($"{((DateTimeOffset)utcTime).ToUnixTimeMilliseconds()}");
            }
        }

        public class BResult
        {
            [JsonProperty("pair")]
            public string Pair { get; set; }

            [JsonProperty("profit_percent")]
            public decimal ProfitPercentage { get; set; }

            [JsonProperty("profit_abs")]
            public decimal ProfitAbs { get; set; }

            [JsonProperty("open_rate")]
            public decimal OpenRate { get; set; }

            [JsonProperty("close_rate")]
            public decimal CloseRate { get; set; }

            [JsonProperty("open_date")]
            [JsonConverter(typeof(JsonTimestampConverter))]
            public DateTime OpenDate { get; set; }

            [JsonProperty("close_date")]
            [JsonConverter(typeof(JsonTimestampConverter))]
            public DateTime CloseDate { get; set; }

            [JsonProperty("open_fee")]
            public decimal OpenFee { get; set; }

            [JsonProperty("close_fee")]
            public decimal CloseFee { get; set; }

            [JsonProperty("amount")]
            public decimal Amount { get; set; }

            [JsonProperty("trade_duration")]
            public decimal TradeDuration { get; set; }

            [JsonProperty("open_at_end")]
            public bool OpenAtEnd { get; set; }

            [JsonProperty("sell_reason")]
            public string SellReason { get; set; }
        }

        static void Main(string[] args)
        {
            // Take JSON data from pastebin
            using var webClient = new WebClient();
            var json = webClient.DownloadString("https://pastebin.com/raw/0bASqR47");

            // Deserialize the data
            var data = JsonConvert.DeserializeObject<List<BResult>>(json);

            // Summary
            foreach (var result in data.GroupBy(e => e.Pair)
                                       .Select(e => new { Pair = e.Key, Count = e.Count(), Value = e }))
            {
                var pairsCount = 1;

                var key = result.Pair;
                var trades = result.Count;

                var profitSum = result.Value.Sum(e => e.ProfitPercentage);
                var profitSumPercentage = profitSum * 100;

                var profitTotal = profitSum / pairsCount;
                var profitTotalPercentage = profitTotal * 100;

                Console.WriteLine($"Cumulative Profit %: {profitSumPercentage:f2}% | Total Profit %: {profitTotalPercentage:f2}%");
            }

            // Create series
            var series = data.Select(e => KeyValue.Create(e.CloseDate, e)).ToSeries();

            // Resample data

            // daily_profit = results.resample('1d', on = 'close_date')['profit_percent'].sum()
            var dailyProfit = series.ResampleEquivalence(dt => new DateTime(dt.Year, dt.Month, dt.Day, 0, 0, 0, DateTimeKind.Utc), group => group.SelectValues(g => g.Profi

Answer 1

You are correct that there is no built-in function like idmin and idmax. This would be a useful addition, so if you can post this as a suggestion on Deedle GitHub (or perhaps even help implementing it), that would be fantastic.

In the meantime, I think the easiest solution is to access the Observations of the series (which gives you IEnumerable<KeyValuePair<K, V>>) and use the standard LINQ Min or Max method on this:

var minKvp = dataSeries.Observations.Min(kvp => kvp.Value);
var maxKvp = dataSeries.Observations.Max(kvp => kvp.Value);

You can then get the key using minKvp.Key and the minimal/maximal value using minKvp.Value.