0

I'm trying to analyse a covid data set and kind of at a loss on how to fix the data via pandas. The data set looks like the following:

enter image description here

I'm trying to make it look like this:

              April 2                        | April 3                       | April 4 
unique_tests  total unique tests for april 2 | total unique tests for april 3|total unique tests for april 4 
positive      total positive for april 2     | total positive for april 3    |total positive for april 4 
negative      total negative for april 2     | total negative for april 3    |total negative for april 4 
remaining      total remaining for april 2   | total remaining for april 3   |total remaining for april 4

I have dates up to april 24.

Any ideas on how i can implement this? I can't make it work with pivot table in pandas

pancakes
  • 159
  • 1
  • 2
  • 14
  • Welcome to StackOverflow. Please take the time to read this post on [how to provide a great pandas example](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) as well as how to provide a [minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) and revise your question accordingly. These tips on how to ask a good question may also be useful. – yatu Apr 24 '20 at 09:03

1 Answers1

1

Use:

#convert columns to numeric and date to datetimes
df = pd.read_csv(file, thousands=',', parse_dates=['date'])
#create custom format of datetimes and aggregate sum, last transpose
df1 = df.groupby(df['date'].dt.strftime('%d-%b')).sum().T

Or is possible reassign column date filled by new format of datetimes:

df1 = df.assign(date = df['date'].dt.strftime('%d-%b')).groupby('date').sum().T
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • hi sorry it didn't work for me. I made changes to my intended output. I just want to convert the date to a column and get the total for each category so i could look at them per date – pancakes Apr 24 '20 at 09:11
  • @pancakes - Can you test now? – jezrael Apr 24 '20 at 09:15
  • it did the trick! thank you! would you mind explaining to me how the code worked? – pancakes Apr 24 '20 at 09:20
  • i was just wondering how the columns like 'unique_tests' and 'positive' and the others became rows – pancakes Apr 24 '20 at 09:22
  • @pancakes - Sure, it is by `T` for transpose - [`DataFrame.T`](http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.T.html) – jezrael Apr 24 '20 at 09:22