How to sample data from Pandas Dataframe based on value count from another column

user_5917787 0
Viewed 532

I have a dataframe of about 400,000 observations. I want to sample 50,000 observations based on the amount of each state that's in a 'state' column. So if there is 5% of all observations from TX, then 2,500 of the samples should be from TX, and so on.

I tried the following:

import pandas as pd
df.sample(n=50000, weights = 'state', random_state = 101)

That gave me this error.

TypeError: '<' not supported between instances of 'str' and 'int`

Is there a different way to do this?

pandas sample python
2 Answers

Weights modify the probability of any one row to be selected, but can’t provide strict guarantees on counts of given values, as you want. For that you would need .groupby('state'):

>>> rate = df['state'].value_counts(normalize=True)
>>> rate
TX    0.5
NY    0.3
CA    0.2
>>> df.groupby('state').apply(lambda s: s.sample(int(10 * rate[s.name]))).droplevel('state')
   state  val
69    CA   33
19    CA   99
37    NY   89
36    NY   63
75    NY    3
42    TX   42
53    TX   52
50    TX   68
72    TX   70
2     TX   18

Replace 10 with the number of samples you want, so 50_000. This gives slightly more flexibility than the more efficient answer by @Psidom.

user_13873460

You can use groupby.sample:

df.groupby('state').sample(frac=0.125, random_state=101)

weights parameter is different from groups, it expects list of numbers as sample probability which is used when you want non equal probability weighting for different rows.

For instance the following sample will always return a data frame from the first two rows since the last two rows have weights of 0 and will never get selected:

df = pd.DataFrame({'a': [1,2,3,4]})
df.sample(n=2, weights=[0.5,0.5,0,0])
   a
0  1
1  2
user_49834500
Related