Mastering Data Manipulation: Filter and Sort Data with Multiple Column & Row Criteria

As a data enthusiast, you know that working with large datasets can be a daunting task. Filtering and sorting data is an essential skill to have in your toolkit, but what happens when you need to filter and sort based on multiple column and row criteria? Don’t worry, we’ve got you covered! In this article, we’ll dive into the world of advanced data manipulation and show you how to filter and sort data with ease, using flexible return arrays to match corresponding data.

Understanding the Problem

Imagine you’re working with a dataset that contains information about customers, including their names, emails, locations, and purchase history. You need to filter the data to show only customers who:

• Live in the United States or Canada
• Haven’t made a purchase in the last 6 months
• Have spent more than $1000 in total

And, you need to sort the resulting data by the customer’s last name in ascending order. Sounds like a challenge, right?

Setting Up the Data

Before we dive into the solution, let’s set up a sample dataset. We’ll use an array of objects to represent our customers:

const customers = [
  { id: 1, name: 'John Doe', email: '[email protected]', location: 'US', lastPurchase: '2022-01-01', totalSpent: 500 },
  { id: 2, name: 'Jane Smith', email: '[email protected]', location: 'CA', lastPurchase: '2021-12-15', totalSpent: 2000 },
  { id: 3, name: 'Bob Johnson', email: '[email protected]', location: 'US', lastPurchase: '2022-06-01', totalSpent: 1500 },
  { id: 4, name: 'Alice Brown', email: '[email protected]', location: 'UK', lastPurchase: '2022-03-01', totalSpent: 800 },
  { id: 5, name: 'Mike Davis', email: '[email protected]', location: 'CA', lastPurchase: '2021-09-01', totalSpent: 1200 },
  // ...
];

Filtering with Multiple Criteria

To filter the data, we’ll use the `filter()` method, which returns a new array with all elements that pass the test implemented by the provided function. We’ll create a function that checks if each customer meets our criteria:

const filteredCustomers = customers.filter(customer => {
  return (
    (customer.location === 'US' || customer.location === 'CA') &&
    (customer.lastPurchase <= '2022-06-01') &&
    (customer.totalSpent > 1000)
  );
});

This code filters the data by checking if the customer’s location is either ‘US’ or ‘CA’, their last purchase date is before or equal to ‘2022-06-01’, and their total spent is greater than 1000.

Sorting with Multiple Columns

Now that we have our filtered data, we need to sort it by the customer’s last name in ascending order. We’ll use the `sort()` method, which sorts the elements of an array in place:

filteredCustomers.sort((a, b) => {
  return a.name.split(' ')[1].localeCompare(b.name.split(' ')[1]);
});

This code splits the customer’s name into an array using the space character as the separator, takes the last element of the array (the last name), and uses the `localeCompare()` method to compare the last names. The `localeCompare()` method returns a negative value if the first string comes before the second, a positive value if the first string comes after the second, and 0 if the strings are equal.

Flexible Return Arrays

Sometimes, you might want to return an array with specific columns or manipulate the data before returning it. We can achieve this by using the `map()` method, which creates a new array with the results of applying the provided function to each element:

const result = filteredCustomers.map(customer => {
  return {
    name: customer.name,
    email: customer.email,
    location: customer.location,
    totalSpent: customer.totalSpent.toFixed(2)
  };
});

This code creates a new array with objects containing only the `name`, `email`, `location`, and `totalSpent` properties. The `totalSpent` value is also formatted to display two decimal places using the `toFixed()` method.

Putting it all Together

Let’s combine the filtering, sorting, and mapping steps into a single function:

function filterAndSortData(customers) {
  const filteredCustomers = customers.filter(customer => {
    return (
      (customer.location === 'US' || customer.location === 'CA') &&
      (customer.lastPurchase <= '2022-06-01') &&
      (customer.totalSpent > 1000)
    );
  });

  filteredCustomers.sort((a, b) => {
    return a.name.split(' ')[1].localeCompare(b.name.split(' ')[1]);
  });

  return filteredCustomers.map(customer => {
    return {
      name: customer.name,
      email: customer.email,
      location: customer.location,
      totalSpent: customer.totalSpent.toFixed(2)
    };
  });
}

const result = filterAndSortData(customers);
console.log(result);

This function takes an array of customers as an input, filters and sorts the data, and returns a new array with the desired columns and formats.

Conclusion

In this article, we’ve covered the process of filtering and sorting data with multiple column and row criteria, as well as returning flexible arrays with specific columns and formats. By mastering these techniques, you’ll be able to manipulate and analyze large datasets with ease, unlocking valuable insights and making data-driven decisions a breeze.

Remember, practice makes perfect. Experiment with different filtering and sorting criteria, and don’t be afraid to get creative with your return arrays. Happy coding!

Customer Name	Email	Location	Total Spent
Jane Smith	[email protected]	CA	$2000.00
Mike Davis	[email protected]	CA	$1200.00

The resulting table displays the filtered and sorted data, with the customer’s name, email, location, and total spent.

Frequently Asked Question

Got questions about filtering and sorting data with multiple column and row criteria? We’ve got you covered! Check out our FAQs below for answers to your burning questions.