How to rearrange a CSV?

How to rearrange a CSV?

I’m trying to rearrange this data set into years so that:

country1 | price | year1
country2 | price | year1
country1 | price | year2
country2 | price | year2

becomes:

         | year1 | year2 
country1 | price | price
country2 | price | price

https://raw.githubusercontent.com/TheEconomist/big-mac-data/master/source-data/big-mac-source-data.csv

How can I do that?

Answer

Try (I used the data from your link and assumed which columns you need):

import pandas as pd

df = pd.read_csv('data.csv',
                 usecols=['name', 'local_price', 'date'],
                 index_col='name')
df.sort_values(by=['name', 'date'], inplace=True)
df = df.pivot(columns='date', values='local_price')
df.to_csv('data_out.csv')