4

In a dataset like this one (CSV format), where there are several columns with values, how can I use fillna alongside df.groupby("DateSent") to fill in all desired columns with min()/3 of the group?

In [5]: df.head()
Out[5]: 
  ID  DateAcquired  DateSent         data   value  measurement    values
0  1      20210518  20220110  6358.434713   556.0   317.869897  3.565781
1  1      20210719  20220210  6508.458382  1468.0   774.337509  5.565384
2  1      20210719  20220310  6508.466246     1.0    40.837533  1.278085
3  1      20200420  20220410  6507.664194    48.0    64.335047  1.604183
4  1      20210328  20220510  6508.451227     0.0    40.337486  1.270236

According to this other thread on SO, one way of doing it would be one by one:

df["data"]        = df.groupby("DateSent")["data"].transform(lambda x: x.fillna(x.min()/3))
df["value"]       = df.groupby("DateSent")["value"].transform(lambda x: x.fillna(x.min()/3))
df["measurement"] = df.groupby("DateSent")["measurement"].transform(lambda x: x.fillna(x.min()/3))
df["values"]      = df.groupby("DateSent")["values"].transform(lambda x: x.fillna(x.min()/3))

In my original dataset where I have 100000 such columns, I can technically loop over all desired column names. But is there a better/faster way of doing this? Perhaps something already implemented in pandas?

paropunam
  • 488
  • 2
  • 11

1 Answers1

2

One way you could do this is to get all the columns you want to impute in a list - I will assume that you want all the numerical columns (except ID, DateAcquired, DataSent)

fti = [i for i in df.iloc[:,3:].columns if df[i].dtypes != 'object'] # features to impute

Then, you can create a new df, with only the imputed values:

imputed = df.groupby("DateSent")[fti].transform(lambda x: x.fillna(x.min()/3))

imputed.head(5)
          data   value  measurement    values
0  6358.434713   556.0   317.869897  3.565781
1  6508.458382  1468.0   774.337509  5.565384
2  6508.466246     1.0    40.837533  1.278085
3  6507.664194    48.0    64.335047  1.604183
4  6508.451227     0.0    40.337486  1.270236

Lastly you can concat:

res = pd.concat([df[df.columns.symmetric_difference(imputed.columns)],imputed],axis=1)

res.head(15)

    DateAcquired  DateSent ID         data   value  measurement    values
0       20210518  20220110  1  6358.434713   556.0   317.869897  3.565781
1       20210719  20220210  1  6508.458382  1468.0   774.337509  5.565384
2       20210719  20220310  1  6508.466246     1.0    40.837533  1.278085
3       20200420  20220410  1  6507.664194    48.0    64.335047  1.604183
4       20210328  20220510  1  6508.451227     0.0    40.337486  1.270236
5       20210518  20220610  1  6508.474031     3.0    15.000000  0.774597
6       20210108  20220110  2  6508.402472   897.0   488.837335  4.421933
7       20210110  20220210  2  6508.410493    52.0   111.000000  2.107131
8       20210119  20220310  2  6508.419065   800.0   440.337387  4.196844
9       20210108  20220410  2  6508.426063    89.0    84.837408  1.842144
10      20200109  20220510  2  6507.647600   978.0   529.334996  4.601456
11      20210919  20220610  2  6508.505563  1566.0   823.337655  5.738772
12      20211214  20220612  2  6508.528918   152.0   500.000000  4.472136
13      20210812  20220620  2  6508.497936   668.0   374.337631  3.869561
14      20210909  20220630  2  6508.506350   489.0   284.837657  3.375427
sophocles
  • 13,593
  • 3
  • 14
  • 33