I have a datafram with interval days and count number of days:
| Day | Count |
|---|---|
| 88 days | 51 |
| 54 days | 48 |
| 94 days | 47 |
| 292 days | 43 |
| 291 days | 43 |
| 428 days | 1 |
| 406 days | 1 |
| 419 days | 1 |
| 397 days | 1 |
So I want create a new table in range days like example:
| 450 days - 350 days | 350 days - 250 days | 250 - 150 days | 150 days - 50 days | 50 days-0 days |
|---|---|---|---|---|
| 4 | 86 | 0 | 146 | 0 |
i tried this code but error:
df.index = pd.to_timedelta(df.index.astype(str))
df = df.resample('100 days').sum()
ValueError: Invalid frequency: 100 days
Try:
df["Day"] = df["Day"].str.split().str[0].astype(int)
tmp = pd.cut(df["Day"], [0, 50, 150, 250, 350, 450])
x = df.groupby(tmp)["Count"].sum().to_frame().T.reset_index(drop=True)
x.columns = [f"{c.right} days - {c.left} days" for c in x.columns]
print(x[x.columns[::-1]])
Prints:
450 days - 350 days 350 days - 250 days 250 days - 150 days 150 days - 50 days 50 days - 0 days
0 4 86 0 146 0
The frequency that you specify is invalid. You should use '30D' instead of '30 days'.
For instance:
import pandas as pd
mydata = {'Day':['88 days','54 days','94 days','292 days','291 days','428 days','406 days','419 days','397 days'],'Count': [51, 48, 47, 43, 43, 1, 1, 1, 1] }
df=pd.DataFrame(data=mydata)
df.index = pd.to_timedelta(df['Day'].astype(str))
df.resample('30D', axis=0).sum()