# How to take samples from a DataFrame that have an specific mean

**URL:** <https://discourse.julialang.org/t/how-to-take-samples-from-a-dataframe-that-have-an-specific-mean/91427>\
**Category:** New to Julia\
**Tags:** statistics, dataframes\
**Created:** [December 8, 2022, 2:43pm UTC](https://discourse.julialang.org/t/how-to-take-samples-from-a-dataframe-that-have-an-specific-mean/91427 "2022-12-08T14:43:00Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Juan\_Mac\_Donagh](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/juan_mac_donagh/32/31798_2.png) [@Juan\_Mac\_Donagh](https://discourse.julialang.org/u/Juan_Mac_Donagh)\
**Post date:** [December 8, 2022, 2:43pm UTC](https://discourse.julialang.org/t/how-to-take-samples-from-a-dataframe-that-have-an-specific-mean/91427/1 "2022-12-08T14:43:00Z")

</div>

Hi all. I was wondering what was the most efficient way to perform this task:

I have a big `DataFrame`, and I need to take random samples (this is the easy part). But I need that the columns of the samples match the mean of another `DataFrame` column. I am not really familiar with bootstrapping, is this something that could be solved with either [DependentBootstrap](https://github.com/colintbowers/DependentBootstrap.jl) or [Bootstrap](https://github.com/juliangehring/Bootstrap.jl)?

If not, I thought about making a function or a while loop, like this:

```julia

sample_function(df::AbstractDataFrame, size) =
   df[sample(axes(df, 1), size), :]

   rows_sample = false
   while rows_sample == false
   	sample_function(df, nrow(df2)) # rows from the other DataFrame
   	
   	if mean(df.c1) == mean(df2.c1) && mean(df.c2) == mean(df2.c2)  
   		rows_sample == true 
   	end
   end

```

But I am not enterally sure that this will work (or if there is a better way of doing it).

Thanks in advance!  
Cheers,

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [December 8, 2022, 3:27pm UTC](https://discourse.julialang.org/t/how-to-take-samples-from-a-dataframe-that-have-an-specific-mean/91427/2 "2022-12-08T15:27:03Z")

</div>

This is an hard question algorithmically. In general, unless your data has some specific structure it might even be the case that such a sample does not exist. What is exactly the structure of your data frames. Also note that what you write `mean.(df.c1)` is most likely not what you want:

```julia
julia> df = DataFrame(c1=1:5)
5×1 DataFrame
 Row │ c1
     │ Int64
─────┼───────
   1 │ 1
   2 │ 2
   3 │ 3
   4 │ 4
   5 │ 5

julia> mean.(df.c1)
5-element Vector{Float64}:
 1.0
 2.0
 3.0
 4.0
 5.0

```

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [December 8, 2022, 3:40pm UTC](https://discourse.julialang.org/t/how-to-take-samples-from-a-dataframe-that-have-an-specific-mean/91427/3 "2022-12-08T15:40:10Z")

</div>

Give a sense of what is the contents of `df.c1`, `df.c2`, `df2.c1`, `df2.c2`. It might be technically impossible. For example if `df` has just large numbers in `c1` and `c2` and `df2` has smaller numbers in these columns.

---

<div class="post-metadata">

**Author:** ![Juan\_Mac\_Donagh](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/juan_mac_donagh/32/31798_2.png) [@Juan\_Mac\_Donagh](https://discourse.julialang.org/u/Juan_Mac_Donagh)\
**Post date:** [December 8, 2022, 3:48pm UTC](https://discourse.julialang.org/t/how-to-take-samples-from-a-dataframe-that-have-an-specific-mean/91427/4 "2022-12-08T15:48:40Z")

</div>

Ups yes it was `mean(df.c1)`, with out vectorizing it. My bad!

The DataFrame that I want to sample from has 16218 rows in total, without missing values.  
Basicly looks like this:

```julia
|id|c1|c2|
|--|--|--|
|a | 5| 2|
|b |10| 1|
|c |11| 5|
|d | 2| 3|
|e |16| 4|

```

And so on for a lot more rows.  
The other dataframe, from which I want to match the mean values of C1 and C2, has only 889 columns (without missings, of course). The mean values of both columns are 3.75 and 849.55, and, the whole df1 DataFrame (with 16218 rows) has mean values of 5.66 and 548.65, respectively.

So… it might be possible! I think.

---

<div class="post-metadata">

**Author:** ![oxinabox](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/oxinabox/32/206603_2.png) [@oxinabox](https://discourse.julialang.org/u/oxinabox)\
**Post date:** [December 8, 2022, 3:52pm UTC](https://discourse.julialang.org/t/how-to-take-samples-from-a-dataframe-that-have-an-specific-mean/91427/5 "2022-12-08T15:52:17Z")

</div>

This is a [Knapsack Problem](https://en.wikipedia.org/wiki/Knapsack_problem).  
It is NP-Complete  
Proving this is equivalent to a knapsack problem is a little fiddly, but I am pretty confident it is.  
In particular it is at least as hard as the [subset sum problem](https://en.wikipedia.org/wiki/Subset_sum_problem)

Proof sketch:

1. Consider trying to solve the Subset Sum problem. Consider [multiset](https://en.wikipedia.org/wiki/Multiset) S of integers and a target-sum T. And we are trying to find out if there exists as subset of S, call it P such that `sum(P)==T`
2. Create a dataframe, `df_S = DataFrame((s=s, t=T) for s in S)`. This has `mean(df_S.t)==T`
3. Run your sampling algorithm to find the subset of rows call it `df_P` that has `mean(df_P.t) == mean(df_S.t)`.
4. If step 3 return an answer (rather than a failure), then the answer to subset sum is true, else false

If you can run the whole thing in polynamial time then congratulations, you have proved [P=NP](https://en.wikipedia.org/wiki/P_versus_NP_problem).

Its a bit more fiddly to show this still applies if you allow a tolerance, but we can just set that tolerance to zero and it still holds.

* * *

Anyway, just because it is NP complete doesn’t mean it is impossible  
You can write a JuMP MIP program to select rows.  
and solve it with HiGHs or Cbc.  
As long as you don’t have too many rows.

Or you can make a heuristic solution like a some kind of greedy search.

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [December 8, 2022, 4:08pm UTC](https://discourse.julialang.org/t/how-to-take-samples-from-a-dataframe-that-have-an-specific-mean/91427/6 "2022-12-08T16:08:48Z")

</div>

> [@oxinabox](#):
>
> Or you can make a heuristic solution like a some kind of greedy search

Yep, feels like a heuristic solution will work well here.

On the other hand:

> [@Juan\_Mac\_Donagh](#):
>
> 16218 rows

> [@Juan\_Mac\_Donagh](#):
>
> df1 DataFrame (with 16218 columns)

> [@Juan\_Mac\_Donagh](#):
>
> it was `mean(df.c1)`, with out vectorizing it.

can we get a clearer picture of `df.c1` , `df.c2` , `df2.c1` , `df2.c2` without the excessive editing, because this is crucial for this problem. For example, are the values integers? what are the ranges of values?

---

<div class="post-metadata">

**Author:** ![Juan\_Mac\_Donagh](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/juan_mac_donagh/32/31798_2.png) [@Juan\_Mac\_Donagh](https://discourse.julialang.org/u/Juan_Mac_Donagh)\
**Post date:** [December 8, 2022, 4:51pm UTC](https://discourse.julialang.org/t/how-to-take-samples-from-a-dataframe-that-have-an-specific-mean/91427/7 "2022-12-08T16:51:12Z")

</div>

Heya sorry, didn’t notice that one. Rows, the big dataframe that I want to same from has 16218 rows.  
Loos like this:

```julia
16218×3 DataFrame
   Row │ uniprot_id length mobi_th50_dis_fraction 
       │ String15 Int64 Float64                        
───────┼────────────────────────────────────────────────────
     1 │ P01189 267 0.007
     2 │ P01241 217 0.018
     3 │ P01275 180 0.0
     4 │ P06850 196 0.0
     5 │ P09681 153 0.0
   ⋮ │ ⋮ ⋮ ⋮
 16215 │ Q9Y6X8 837 0.002
 16216 │ Q9Y6Y8 1000 0.0
 16217 │ Q9Y6Y9 160 0.0
 16218 │ Q9Y6Z7 277 0.0
                                          16209 rows omitted

```

c1 and c2 would be the columns that have Ints and Floats, the other one is not needed (uniprot\_id).

The other dataframe, the one that I want to compare the sample from the one above, looks like this:

```julia
889×3 DataFrame
 Row │ uniprot_id length mobi_th50_dis_fraction 
     │ String15 Int64 Float64                
─────┼────────────────────────────────────────────
   1 │ A0A6E1VY70 674 0.068
   2 │ A0AVF1 554 0.058
   3 │ A0JP26 581 0.384
   4 │ A1KZ92 1463 0.088
   5 │ A2A3L6 582 0.29
  ⋮ │ ⋮ ⋮ ⋮
 886 │ Q9Y6X3 613 0.062
 887 │ Q9Y6X6 1858 0.343
 888 │ Q9Y6Y0 642 0.0
 889 │ Q9Y6Y1 1673 0.361
                                  880 rows omitt

```

Please let me know if this si better!

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [December 8, 2022, 4:52pm UTC](https://discourse.julialang.org/t/how-to-take-samples-from-a-dataframe-that-have-an-specific-mean/91427/8 "2022-12-08T16:52:43Z")

</div>

I agree with all the previous posters about a pragmatic approach, but I’m wondering about this part of your OP:

> I need to take random samples

If you solve a Knapsack problem to select observations, the resulting sample will not be a random sample so standard statistical inference will likely be off. Whether this is a problem of course depends on your application, but if this is about representativeness of the sample in some sense you might want to look at the causal inference literature addressing this stuff, things like matching estimators, IPW or doubly robust estimators, or synthetic controls.

---

<div class="post-metadata">

**Author:** ![Juan\_Mac\_Donagh](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/juan_mac_donagh/32/31798_2.png) [@Juan\_Mac\_Donagh](https://discourse.julialang.org/u/Juan_Mac_Donagh)\
**Post date:** [December 8, 2022, 4:55pm UTC](https://discourse.julialang.org/t/how-to-take-samples-from-a-dataframe-that-have-an-specific-mean/91427/9 "2022-12-08T16:55:32Z")

</div>

Well… my degree is in molecular biology, so I do not have a lot of background on statistics. Given that we are on this topics, are there any papers / articles or books recommendations on the topics that you all reckon that could help? Besides de Knapsack problem, that I should read more about.

---

<div class="post-metadata">

**Author:** ![Juan\_Mac\_Donagh](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/juan_mac_donagh/32/31798_2.png) [@Juan\_Mac\_Donagh](https://discourse.julialang.org/u/Juan_Mac_Donagh)\
**Post date:** [December 8, 2022, 4:59pm UTC](https://discourse.julialang.org/t/how-to-take-samples-from-a-dataframe-that-have-an-specific-mean/91427/10 "2022-12-08T16:59:04Z")

</div>

Good, I will look into them. The sample is not going to be representative of the data where it came from (for examples, the mean and media is going to be way off), but it’s going to match the one from the other data points.

Basically, to compare both data sets, I need to “control” certain parameters so the comparison is valid. If I simply take the big data set and compare to the smaller one, the share weight of the sample is going to generate a bias on my test. That is way a need a sample that, not being the original one, “behaves” like the original one. I think.

---

<div class="post-metadata">

**Author:** ![Juan\_Mac\_Donagh](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/juan_mac_donagh/32/31798_2.png) [@Juan\_Mac\_Donagh](https://discourse.julialang.org/u/Juan_Mac_Donagh)\
**Post date:** [December 8, 2022, 5:10pm UTC](https://discourse.julialang.org/t/how-to-take-samples-from-a-dataframe-that-have-an-specific-mean/91427/11 "2022-12-08T17:10:24Z")

</div>

One thing that I did was filtering the df for rows that had similar values to the mean value of the dataframe, like this:

```julia
filter!(row -> (row.c1>= 700 && row.c1<= 900) && (row.c2>= 3 && row.c2<= 4), df_t2)

```

But the thing is that I am “forcing” the system, and not getting a random population that happens to have the same mean value, so I think is not ok to do it.

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [December 8, 2022, 5:26pm UTC](https://discourse.julialang.org/t/how-to-take-samples-from-a-dataframe-that-have-an-specific-mean/91427/12 "2022-12-08T17:26:18Z")

</div>

> [@bkamins](#):
>
> This is an hard question algorithmically.

For the reasons that others expanded over I said that this problem is hard. In general you would need to enumerate all solutions to a NP-hard problem and randomly pick one of them.

Given your description I assume you are happy with approximate solution.  
For this you could use the following procedure:

- For each row in a reference table find rows in the original table that are similar to it (this should be doable assuming the data frame is not too wide; you can do it in several ways - either use distance cutoff or some specified number of neighbors)
- Then sample for each row in a reference table one of its neighbors.

Doing this you should get not only approximate matching of the mean, but approximate matching of the whole joint distribution of the variables on which you match.

An alternative would be to build some propensity model of row being included in the sample and perform [propensity score matching](https://en.wikipedia.org/wiki/Propensity_score_matching).

---

<div class="post-metadata">

**Author:** ![gustaphe](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/gustaphe/32/18174_2.png) [@gustaphe](https://discourse.julialang.org/u/gustaphe)\
**Post date:** [December 8, 2022, 7:48pm UTC](https://discourse.julialang.org/t/how-to-take-samples-from-a-dataframe-that-have-an-specific-mean/91427/13 "2022-12-08T19:48:10Z")

</div>

I’m spitballing here, this isn’t guaranteed to solve the problem but at least _feels_ like what you mean:

To pick random samples from `x` with a mean and variance similar to those of `y`, form `p = exp.(-(x.-mean(y)).^2./variance(y))` and use that as your weight vector for sampling. You are likely to pick samples within `mean(y)±std(y)`, but you are guaranteed they are all from `x`.