3

INTRODUCTION: I have a CSV file (fossil-fuel-input.csv) that contains the data on fossil fuels utilization by country and year. The layout of data (order of rows and columns) in this CSV is not in a proper way.

DESCRIPTION: The layout in which I want the data is shown in the second CSV file (fossil-fuel-output.csv). This second CSV only contains few records from the first CSV. Actually, I want all records from fossil-fuel-input.csv to be present in fossil-fuel-output.csv. Manually doing copy-paste is not feasible as it will take a lot of time.

QUESTION: How can I achieve this? Feel free to use any tool like Excel, Python, etc. (Tried V-lookup and Transpose in Excel, but, it didn't work for me).

SySu
  • 619
  • 1
  • 7
  • 15

1 Answers1

2

You can do this with pandas by creating a pivot table:

import pandas as pd
df = pd.read_csv('https://gitlab.com/sysuin/datasets/-/raw/main/fossil-fuel-input.csv?inline=false')
df = df.pivot_table(values='Fossil Fuels (TWh)', index='Entity', columns='Year', aggfunc='first')
df.to_csv('output.csv')
RJ Adriaansen
  • 9,131
  • 2
  • 12
  • 26