I load a data from csv to Python Pandas and try to make the columns to row (with duplicates dates)-CodePudding

I taking the confirm cases of data from here : https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv

I load the data in python using Pandas Dataframe .

my problem is : i am trying to make the columns of the date as rows , and the ' Country/Region' column as columns .

url_confirmed = 'https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv'

df = pd.read_csv(url_confirmed)

df = df.drop(columns=['Province/State','Lat','Long'],axis=1)

df_piv = pd.melt(df,id_vars=['Country/Region'],var_name='Date',value_name="Value")

I get until here and really don't know how to proceed

my final dataframe suppose to look like this :

   Date    Afghanistan    Albania   and so on
0  1/22/20    0              val  
1  1/23/20    300            val
3  1/24/20   4023            val
6  1/25/20    300            val
7  1/26/20   2000            val
8    ..       ..
.
.

**Thank You Very Much **

CodePudding user response：

I think a simple transpose with renaming a column should do it:

url_confirmed = 'https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv'

df = pd.read_csv(url_confirmed)

df = df.drop(columns=['Province/State','Lat','Long'],axis=1)

df = df.T.reset_index() # Transpose and reset index
df.columns = df.iloc[0] # Set first row as header
df = df[1:]
df.rename(columns = {'Country/Region' : 'Date'}, inplace=True)