I'm trying import data into database using python, but before that I need to manipulate it with pandas. I have a csv file that I read with reading = pd.read_csv("File.csv") and the data looks like this: Data from csv

I want it to look like this: Data final result

I have tried pandas melt() and pivot() but they cut up the data in vary strange ways. The probles is that there are like 150 countries in the headers as column names so I can't just parse them in usecols and so on. Does anyone have any suggetions on how to tackle this?

Thanks in advance.

CodePudding user response：

I think you need replace - to missing values, then transpose:

df = pd.read_csv("File.csv", na_values='-', index_col=['Date']).fillna(0).astype(int).T

Or:

df = pd.read_csv("File.csv", index_col=['Date']).replace('-', 0).astype(int).T