CODEWITHSUNDEEP
pythonexcel
Jun Dalisay
Jun Dalisay

Reputation: 1265

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?

Upvotes: 0

Views: 107

Answers (3)

Timus
Timus

Reputation: 11321

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')

Upvotes: 1

IoaTzimas
IoaTzimas

Reputation: 10624

You can do it with pandas pivot_table:

import pandas as pd
pd.pivot_table(index='Country', columns='Year', values='Price')

Output:

  year    year1  year2
country
country1  price  price
country2  price  price

Upvotes: 0

Vaziri-Mahmoud
Vaziri-Mahmoud

Reputation: 150

Try df.pivot():

df.pivot(index='Country', columns='Year', values='Price')

Upvotes: 0

Related Questions