0

I have three pandas Dataframe with 3 features:

Date In, Date Out, Status

Date In/Out are timestamps. Status is a text field.

I want to know how many came in on every month and how many that went out in the same month.

I used:

d = df.resample('M', on='Date In').count()

This returns the count of Date Out matching every month of Date In's resample. However, it seems to remove the Date In column from the output, making it the index. This makes

d['Date In']

not possible to get the count of Date In for that month. Moreover, if I had more columns, this would also show the counts of those relative to Date In. This is what makes the .resample() method so effective.

I have recently updated pandas and I think this is one of the things they changed in the newer version.

eatkimchi
  • 13
  • 3

1 Answers1

0

you can try using groupby like this:

in_counts = df.groupby(pd.Grouper(key='Date In', freq='M')).size()
out_counts = df.groupby(pd.Grouper(key='Date Out', freq='M')).size()

counts_df = pd.DataFrame({'In Count': in_counts, 'Out Count': out_counts})
counts_df = counts_df.reset_index()
counts_df = counts_df.rename(columns={'Date In': 'Month'})

print(counts_df)

I hope this answers your requirement.

Phoenix
  • 1,343
  • 8
  • 10