Is there a function in pandas that look for "gaps" of some data in dataframe rows?

user_11341931 0
Viewed 83

I have a problem with looking for gaps in my pandas dataframe.

So I have this data in my df:

date name score
2020-01-01 FEAT_1 0.64
2020-01-01 FEAT_2 0.17
2020-01-01 FEAT_3 0.09
2020-01-01 FEAT_4 0.07
2020-01-01 FEAT_5 0.03
2020-01-02 FEAT_1 0.90
2020-01-02 FEAT_2 0.30
2020-01-02 FEAT_3 0.20
2020-01-02 FEAT_4 0.10
2020-01-02 FEAT_6 0.02

And what I want to do is to fill rows for dates that miss some FEAT_N. So, for example 2020-01-01 has feat1,2,3,4,5 but doesn't have feat_6 and I would like to put there new row with same date, feat_6 as name and score = 0. FEAT_N are .unique() values of column feature_name

I don't know how to deal with this problem. My main problem is that I don't know how to find if certain date in date column has FEAT_N value.

Thanks guys.

pandas python
3 Answers

You can easily do this with by using a MultiIndex in pandas.

  1. Set the index to a MultiIndex of "date" and "name"
  2. Create a new MultiIndex that is the cartesian product of the current MultiIndex
  3. reindex your data filling with 0
df = df.set_index(["date", "name"])
new_index = pd.MultiIndex.from_product(df.index.levels)
new_df = df.reindex(new_index, fill_value=0)
print(new_df)
                   score
date       name
2020-01-01 FEAT_1   0.64
           FEAT_2   0.17
           FEAT_3   0.09
           FEAT_4   0.07
           FEAT_5   0.03
           FEAT_6   0.00
2020-01-02 FEAT_1   0.90
           FEAT_2   0.30
           FEAT_3   0.20
           FEAT_4   0.10
           FEAT_5   0.00
           FEAT_6   0.02

Below is the exact same approach, but intended to please the method chaining addicts:

new_df = (
    df.set_index(["date", "name"])
    .pipe(lambda df:
        df.reindex(
            pd.MultiIndex.from_product(df.index.levels), 
            fill_value=0
        )
    )
)

print(new_df)
                   score
date       name
2020-01-01 FEAT_1   0.64
           FEAT_2   0.17
           FEAT_3   0.09
           FEAT_4   0.07
           FEAT_5   0.03
           FEAT_6   0.00
2020-01-02 FEAT_1   0.90
           FEAT_2   0.30
           FEAT_3   0.20
           FEAT_4   0.10
           FEAT_5   0.00
           FEAT_6   0.02
user_142784480

One possible approach is to create a "grid" dataframe containing all the column pairs you want, outer-join it with your data frame, then set the missing values to your default (0):

import pandas as pd

keys1 = df['date'].unique()
keys2 = df['name'].unique()
# get all possible date-name pairs
default = [[k1, k2] for k1 in keys1 for k2 in keys2]
df_default = pd.DataFrame(default, columns=['date', 'name'])

# outer merge
df_complete = pd.merge(df, df_default, on=['date', 'name'], how='outer')
# fill in the missing value with a reasonable default
df_complete['score'] = df_complete['score'].fillna(0)
user_134187050

If these values are required, I'd suggest maybe turning them into columns, e.g. date | FEAT_1_score | FEAT_2_score, etc.

Then, you can fill those columns with either the scores in your current data, or the 0 value.

Here's a rough approximation of the code:

import pandas as pd

data = {'date':['2020-01-01','2020-01-01'], 'name':["FEAT_1", "FEAT_3"], 'score':['0.5', '0.6']}

df = pd.DataFrame(data)

def index_value_or_zero(x, df, feat):
    index_value = df.loc[(df["date"] == x.date) & (df["name"] == feat)]["score"]
    if len(index_value) == 0:
        return 0
    else:
        return index_value

df["FEAT_1_score"] = df.apply(lambda x: index_value_or_zero(x, df, "FEAT_1"), axis=1)
df["FEAT_2_score"] = df.apply(lambda x: index_value_or_zero(x, df, "FEAT_2"), axis=1)
df["FEAT_3_score"] = df.apply(lambda x: index_value_or_zero(x, df, "FEAT_3"), axis=1)

df = df.drop("score", axis=1).drop("name", axis=1)
df.drop_duplicates()

df

returns:

    date        FEAT_1_score    FEAT_2_score    FEAT_3_score
0   2020-01-01  0.5             0               0.6
user_12504730
Related