0
$\begingroup$

I have a data set (pandas dataframe) with a variable that corresponds to the country for each sample. I have to take the samples that corresponds with the countries that appears the most.

thanks

$\endgroup$
  • $\begingroup$ To precise the question, my data frame has a feature 'country' (categorical variable) and this has a value for every sample. The dataset is huge, so I'm trying to reduce it using just the samples which has as 'country' the ones that are more present. In this case I want to take the samples of the 5 most repeated countries. $\endgroup$ – Manuel Gijón Aug 6 '19 at 20:18
0
$\begingroup$

Let's say you have a dataframe df:

import pandas as pd
from faker import Faker
import random

fake = Faker()
n = 10000
names = [fake.name() for i in range(n)]
countries = [fake.country() for i in range(n)]
ages = [random.randint(18,99) for i in range(n)]

df = pd.DataFrame({'name':names, 'age':ages, 'country':countries})

If you want to extract the top 5 countries, you can simply use value_counts on you Series:

df.country.value_counts()[0:5]

Then extracting a sample of data for the top 5 countries becomes as simple as making a call to the pandas built-in sample function after having filtered to keep the countries you wanted:

def is_top5_country(x, top5):
    if x in top5:
        return True
    return False

mask = df.country.apply(lambda x: is_top5_country(x, list(df.country.value_counts()[0:5].index)))
df[mask].sample(frac=0.5)
| improve this answer | |
$\endgroup$
  • $\begingroup$ Evaluating value_counts inside of apply is likely going to be very slow. $\endgroup$ – Stephen Rauch Aug 7 '19 at 13:20
  • $\begingroup$ Indeed! I believe Manuel will find a way to fix that ;-) $\endgroup$ – 0asa Aug 7 '19 at 13:23
0
$\begingroup$

If I understand your question correctly you can break this problem down into two parts: 1. Figuring out which country occurs most frequently and then 2. Subsetting the pandas dataframe to that country

import pandas as pd
from scipy.stats import mode

# 1
mock_df = pd.DataFrame([{'country': 'a'}, {'country': 'b'}, {'country': 'a'}])
print(mock_df)
#   country
# 0       a
# 1       b
# 2       a
mode_country, _ = mode(mock_df.country)

# 2
mock_df[mock_df.country == mode_country[0]]

#   country
# 0       a
# 2       a
```
| improve this answer | |
$\endgroup$
  • $\begingroup$ no, I'm going to modify the question to be more precise. But thanks. $\endgroup$ – Manuel Gijón Aug 6 '19 at 20:13
0
$\begingroup$

So, you want to get the 5 most frequent values of a column and then filter the whole dataset with just those 5 values.

First, let's find those 5 frequent values of the column country

freq_countries = df["country"].value_counts().index.tolist()[:5]
  • value_counts() does what it says. Returns how many times each value appears in your column. It's descending sorted by default.
  • index: With this, we get the actual name of the value and not the frequency.
  • tolist(): We convert it to list so we can use it later
  • [:5]: We get the top 5 as it comes sorted

Then let's filter the dataframe with only those 5 values

df[df['country].isin(freq_countries)]

This will return only the rows that the column country has one of the 5 values.

NOTE: If you want to keep a representative dataset and your only problem is the size of it, I would suggest getting a stratified sample instead. A stratified sample makes it sure that the distribution of a column is the same before and after sampling.

from sklearn.model_selection import train_test_split
df_sample, df_drop_it = train_test_split(df, train_size =0.2, stratify=df['country'])

With the above, you will get two dataframes. The first will be 20% of the whole dataset. The second will be the rest that you can drop it since you won't use it.

The variable train_size handles the size of the sample you want. The parameter stratify takes as input the column that you want to keep the same distribution before and after sampling.

| improve this answer | |
$\endgroup$

Not the answer you're looking for? Browse other questions tagged or ask your own question.