How to prevent pandas resample from resampling id columns

user_2981639 0
Viewed 698

I have a dataframe with id columns (site_id,type_id,equipment_id), a timestamp and a value as below.

>>>print(df.head())
site_id type_id equipment_id    timestamp                           value 
47      9       332859965468    2018-07-04  10:30:04.052000+10:00   23.000000
47      9       332859965468    2018-07-04  10:30:04.064000+10:00   22.050505
47      9       332859965468    2018-07-04  10:30:04.090000+10:00   26.046154
47      9       332859965468    2018-07-04  10:30:04.101000+10:00   22.000000
47      9       332859965468    2018-07-04  10:30:04.113000+10:00   191.989868

I'm trying to resample within each (site_id,type_id,equipment_id) group using the following code

>>> df = df \
...     .set_index(['timestamp']) \
...     .sort_values(['site_id','type_id','equipment_id','timestamp']) \
...     .groupby(['site_id','type_id','equipment_id']) \
...     .resample('15T') \
...     .mean()

I'm getting unexpected results, all of the id values from the index have been duplicated. It seems to be using the dtype rather than whether the column is in the index or not to perform the aggregation? Am I doning something wrong?

                                                            site_id type_id equipment_id    value
site_id type_id equipment_id    timestamp
47      9       332859965468    2018-07-04 10:30:00+10:00   47.0    9.0     3.328600e+11    58.718625
                                2018-07-04 10:45:00+10:00   47.0    9.0     3.328600e+11    59.175833
                                2018-07-04 11:00:00+10:00   47.0    9.0     3.328600e+11    59.238318
                                2018-07-04 11:15:00+10:00   47.0    9.0     3.328600e+11    58.982763

Edit: I've noticed adding .reset_index(drop=True) removes the duplicate columns - but the issue now is the integer id columns have been converted to floats?

pandas-groupby pandas
1 Answers

This happens to a MultiIndex if the index isn't sorted. If you'd like to have the index looking "clean" again, you could do:

df.sort_index(inplace=True)

For instance,

df = pd.DataFrame(
    data=np.random.rand(5, 4),
    index=pd.MultiIndex.from_tuples([(i, j) for i, j in zip(np.random.choice(['a', 'b'], 5), np.random.choice(['x', 'y'], 5))])
)
print(df)
print(df.sort_index())

which produces:

            0         1         2         3
a x  0.198659  0.616800  0.438903  0.830216
  y  0.649111  0.860940  0.440068  0.044067
b x  0.178537  0.601514  0.898179  0.140358
  y  0.444738  0.393664  0.877928  0.913228
a x  0.369067  0.944636  0.740877  0.751681
            0         1         2         3
a x  0.198659  0.616800  0.438903  0.830216
  x  0.369067  0.944636  0.740877  0.751681
  y  0.649111  0.860940  0.440068  0.044067
b x  0.178537  0.601514  0.898179  0.140358
  y  0.444738  0.393664  0.877928  0.913228
user_21235550
Related