Pandas DataFrame manipulation and transformation

user_17140853 0
Viewed 51

I'm trying to 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 problem 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 suggestions on how to tackle this?

pandas dataframe csv python-3.x python
1 Answers

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
user_29010020
Related