Time series resampling with column of type object

user_13070210 0
Viewed 46

Good evening,

I want to resample on an irregular time series with column of type object but it does not work

Here is my sample data:

Actual start date   Ingredients                 NumberShortage
2002-01-01          LEVOBUNOLOL HYDROCHLORIDE   1
2006-07-30          LEVETIRACETAM               1
2008-03-19          FLAVOXATE HYDROCHLORIDE     1
2010-01-01          LEVOTHYROXINE SODIUM        1
2011-04-01          BIMATOPROST                 1

I tried to re-sample my data frame daily but it does not work with my code which is as follows:

df3 =  df1.resample('D', on='Actual start date').sum()

and here is what it gives:

Actual start date   NumberShortage
2002-01-01          1
2002-01-02          0
2002-01-03          0
2002-01-04          0
2002-01-05          0

and what I want as a result:

 Actual start date  Ingredients                 NumberShortage
    2002-01-01      LEVOBUNOLOL HYDROCHLORIDE   1
    2002-01-02      NAN                         0
    2002-01-03      NAN                         0
    2002-01-04      NAN                         0
    2002-01-05      NAN                         0

Any ideas?

details on the data

So I use an excel file which contains several attributes before it is a csv file (this file can be downloaded from this site web https://www.drugshortagescanada.ca/search?perform=0 ) then I group by 'Actual start date' and 'Ingredients'to obtain 'NumberShortage'

and here is the source code:

import pandas as pd

df = pd.read_excel("Data/Data.xlsx")

df = df.dropna(how='any')   

df = df.groupby(['Actual start date','Ingredients']).size().reset_index(name='NumberShortage')

finally after having applied your source code here is the eureur which gives me :

enter image description here

and here is the sample excel file :

        Brand name             Company Name  Ingredients Actual start date 
        ACETAMINOPHEN          PHARMASCIENCE INC ACETAMINOPHEN CODEINE 2017-03-23
PMS-METHYLPHENIDATE ER         PHARMASCIENCE INC METHYLPHENIDATE 2017-03-28
pandas time-series resampling python-3.x
1 Answers

You rather need to reindex using date_range as a source of new dates, and the time series as temporary index:

df['Actual start date'] = pd.to_datetime(df['Actual start date'])

(df
 .set_index('Actual start date')
 .reindex(pd.date_range(df['Actual start date'].min(),
                        df['Actual start date'].max(), freq='D'))
 .fillna({'NumberShortage': 0}, downcast='infer')
 .reset_index()
)

output:

          index                Ingredients  NumberShortage
0    2002-01-01  LEVOBUNOLOL HYDROCHLORIDE               1
1    2002-01-02                        NaN               0
2    2002-01-03                        NaN               0
3    2002-01-04                        NaN               0
4    2002-01-05                        NaN               0
...         ...                        ...             ...
3373 2011-03-28                        NaN               0
3374 2011-03-29                        NaN               0
3375 2011-03-30                        NaN               0
3376 2011-03-31                        NaN               0
3377 2011-04-01                BIMATOPROST               1

[3378 rows x 3 columns]
user_163434640
Related