How to fill a DataFrame with incomplete data (zero values) by the mean or regression of the corresponding row?

user_14810812 0
Viewed 56

I have a Dataframe whose indices represent hourly data, in the example below at hour 4 and 5 there is incomplete or null data (0) for column '32'

                    0         16         32         50
        0   13.344711  13.258395  13.164437  13.018752
        1   13.157080  13.074128  12.984929  12.848975
        2   12.813633  12.760892  12.697085  12.567042
        3   12.588605  12.539565  12.479199  12.352412
        4   12.424297  12.376210   0.000000  12.286655
        5   12.579983  12.462042   0.000000  12.324916
        6   12.749257  12.629652  12.539759  12.414876
        7   13.141030  13.014785  12.908303  12.774708
        8   13.327788  13.191913  13.065706  12.927316
        9   12.914076  12.794186  12.676565  12.546907
        10  13.115845  12.985283  12.859995  12.722257
        11  13.347318  13.210629  13.073633  12.928296
        12  13.733890  13.586480  13.440671  13.281488
        13  13.832811  13.683618  13.536339  13.375693
        14  13.646698  13.500465  13.398737  13.294140
        15  13.473439  13.417434  13.363639  13.262998
        16  13.821724  13.742778  13.685399  13.578959
        17  30.401276  30.271469  30.139293  30.026059
        18  34.537367  34.491006  30.490172  30.336740
        19  30.399954  30.267124  30.134991  29.992922
        20  30.228463  30.099271  13.970973  13.814350
        21  13.733855  13.616331  13.481483  13.342555
        22  13.393572  13.284818  13.158583  13.028806
        23  13.137793  13.034095  12.912887  12.791949

There is some way to complete them with the mean, or some regression, of the adjacent columns for the same hours. Identifying and completing this data automatically.

Fordf['32'][4] use data from df['16'][4] and df['50'][4] and for df['32'][5] use data from df['16'][5] and df['50'][5]

pandas dataframe python-3.x
1 Answers

First replace 0 to missing values and then add forward and back filling values per axis=1 with divide by 2:

df = df.replace(0, np.nan)
df = df.ffill(axis=1).add(df.bfill(axis=1)).div(2)
print (df.head(10))
           0         16         32         50
0  13.344711  13.258395  13.164437  13.018752
1  13.157080  13.074128  12.984929  12.848975
2  12.813633  12.760892  12.697085  12.567042
3  12.588605  12.539565  12.479199  12.352412
4  12.424297  12.376210  12.331433  12.286655
5  12.579983  12.462042  12.393479  12.324916
6  12.749257  12.629652  12.539759  12.414876
7  13.141030  13.014785  12.908303  12.774708
8  13.327788  13.191913  13.065706  12.927316
9  12.914076  12.794186  12.676565  12.546907
user_29010020
Related