Adapt a dataframe to a range of dates

Question

Adapt a dataframe to a range of dates

Navigation

#1 by (1 votes)

1

my doubt:

I have a df (with Fecha as index and another column Cantidad ) with records every 3rd day from 2016-05-03 until 2016-05-20, example:

df.index [
  2016-05-03,
  2016-05-05,
  2016-05-07 ...]

With: df = df.asfreq(freq='D') I have left:

[2016-05-03,
  2016-05-04,
  2016-05-05,
  2016-05-06,
  2016-05-07 ...]

And with df = df.fillna(0) filled with zeros in the column Cantidad the new records.

But what I need is that I generate it from May 1 to the last day of May. Already having it fill all the other days that are generated equal with 0's.

Thank you.

python python-2.7 date pandas

asked by Jaime Hernandez 05.05.2017 в 18:16

source

1 answer

How to cast from a struct to a void * and vice versa in C? Create new excel document with asp.net from a template

score 1 · Accepted Answer

If I understood what you want, a simple way to do both at the same time is to use reindex .

So that it can be played by other users, let's imagine that we have the following csv:

Date, Quantity
  2016-05-03,1
  2016-05-05,2
  2016-05-07,3
  2016-05-09,4

We will load it in dataframe to generate data similar to yours and then we will expand the index so that there is a column for each day of the month so that the new days that are added will have a Cantidad of 0.

import pandas as pd
from datetime import date

csv = 'datos.csv'
df = pd.read_csv(csv, index_col=0, parse_dates=True)

#Las dos lineas siguientes son las que te interesan:
ix = pd.DatetimeIndex(name=df.index.name, start=date(2016,5,1), end=date(2016,5,31), freq='D')
df = df.reindex(ix, fill_value= 0)

print(df)

Exit:

             Cantidad
Fecha               
2016-05-01          0
2016-05-02          0
2016-05-03          1
2016-05-04          0
2016-05-05          2
2016-05-06          0
2016-05-07          3
2016-05-08          0
2016-05-09          4
2016-05-10          0
...
2016-05-30          0
2016-05-31          0

The date range of the new index is created with pd.DatetimeIndex() , passing the initial date with the parameter start and the final one with the parameter end .