I receive my data as:
ID Congressional District Call Date Disposition Q1\r
0 32539 9 2020-09-28 Answered Refused
1 162619 7 2020-10-02 Bad Number
2 77120 12 2020-09-29 Left Message
3 137498 12 2020-10-01 Bad Number
4 150741 6 2020-10-02 Left Message
... ... ... ... ... ...
4995 46976 12 2020-09-28 Left Message
4996 18607 1 2020-09-30 Wrong Number
4997 48573 13 2020-09-28 Left Message
4998 130080 8 2020-10-01 Do Not Call
4999 160957 7 2020-10-02 Left Message <NA>
I want to make a new DataFrame as this:
Total 2020-09-28 2020-09-29 2020-09-30 2020-10-01 2020-10-02
Answered 150 30 30 30 30 30
Do Not Call 60 13 13 13 11 10
Left Message 60 13 13 13 11 10
Bad Number 60 13 13 13 11 10
Wrong Number 60 13 13 13 11 10
I am currently receiving this..
Total 1 2 3 4 ... 6 7 8 9 10
Disposition ...
Answered 712 10.729730 11.205298 11.580000 3.533898 ... 40055.121622 71594.980132 97800.053333 116451.067797 148630.834483
Bad Number 883 11.532567 11.428571 11.926174 11.465753 ... 43221.344828 72720.093168 98930.865772 135084.890411 152487.602151
Do Not Call 8 3.333333 11.000000 13.000000 6.666667 ... 13382.000000 71322.000000 105554.000000 96946.000000 NaN
Left Message 2816 8.413842 10.499106 11.388889 4.002000 ... 32044.853107 68353.930233 95598.186508 121668.028000 152734.754128
Wrong Number 581 1.062500 1.171642 1.064103 0.991803 ... 4153.034722 13523.194030 18771.935897 23589.516393 27375.600000
Here is my code, where df is my dataframe.
print(df)
rc = df.groupby(by=['Disposition']).size()
rc1 = pd.pivot_table(df,index=['Disposition'],columns="Call Date")
rc = pd.concat([rc,rc1],ignore_index=True,axis=1)
result = pd.concat([rc], axis=0)
result.rename(columns={0:'Total'},inplace=True)
print(result)
I've tried playing around with a couple different things/answers including merge, but can't seem to find the solution. How can i combine two columns in the dataframe to make a new one?
