# -*- coding: utf-8 -*-
"""IATI_DFID_DATA.ipynb

Automatically generated by Colaboratory.

Original file is located at
    https://colab.research.google.com/drive/1jkrOj2Ma_W14Tjh6bOuDaKVHfAQKZz8H

# This code pulls in a CSV file on DFID's work in Uganda, which has been produced using the IATI data standard, chooses the specific fields (columns) that I want to work on, and exports it to an Excel sheet for ease of use.

This is the the full pandas code. It's broken down into sections below.
"""

# this imports pandas and renames it as pd
import pandas as pd

# this fetches the CSV file from a URL and reads the contents
datadoc = pd.read_csv("https://raw.githubusercontent.com/sj21446/IATI_data/master/DFID_Uganda_current_activities_04062020.csv")

# this gives an idea of whether all the data we were expecting is in datadoc - is it the right size/shape?
print(datadoc.shape())

# this gives a readout of the first three lines of data, to check it's the right file.
print(datadoc.head(3))

# this gives a readout of the columns and the types of data in each
print(datadoc.dtypes())


# this brings back a list of implementing organisations currently funded by DFID in Uganda
# together with the value of the transaction, the title of the project, the IATI identifier for the project and the status code for the project.
implementers = pd.DataFrame(datadoc, columns=["participating-org (Implementing)", "transaction-value", "title", "activity-status-code", "iati-identifier"])

# this tests that the filter is correct
display(implementers)

# this exports the filtered data to a new Excel file, with headers and without the Index column
implementers.to_excel (r"DFID_Uganda_implementers.xlsx", index=False, header=True)

"""First thing to do is to import the pandas package. When you're working with Pandas, it's common to abbreviate it to pd."""

# this imports pandas and renames it as pd
import pandas as pd

"""Then we fetch the CSV file that we're going to be working on - this one happens to be on my Github repo. and create a DataFrame to hold the data, called datadoc"""

# this fetches the CSV file from a URL and reads the contents and creates a DataFrame called datadoc
datadoc = pd.read_csv("https://raw.githubusercontent.com/sj21446/IATI_data/master/DFID_Uganda_current_activities_04062020.csv")

"""This snippet gives us some feedback on the size and shape of datadoc, to see if its got all the data that we were expecting. The first number is the number of rows, and the second number is the number of columns."""

# this gives an idea of whether all the data we were expecting is in datadoc - is it the right size/shape?
print(datadoc.shape)

"""Then we can call up the first three lines of datadoc, just to check that the data looks like what we were expecting:"""

# this gives a readout of the first three lines of data, to check it's the right file.
print(datadoc.head(3))

"""We can also get a list of the column headings, and the type of data contained in each column in datadoc"""

# this gives a readout of the columns and the types of data in each
print(datadoc.dtypes)

"""If everything is looking like we were expecting, the next stage is to create a DataFrame (called implementers) which just includes the data that I want to work with, based on the column headings. I also ordered the columns in the order that I wanted, rather than the order that they were in, in the CSV."""

# this brings back a list of implementing organisations currently funded by DFID in Uganda
# together with the value of the transaction, the title of the project, the IATI identifier for the project and the status code for the project.
implementers = pd.DataFrame(datadoc, columns=["participating-org (Implementing)", "transaction-value", "title", "activity-status-code", "iati-identifier"])

"""And then I can check that this filtering has worked:"""

# this tests that the filter is correct
display(implementers)

"""If we've finished selecting the data, we can then export it in the file format we need - I've chosen Excel as I'm going to do further analysis in Excel or Power BI. This code exports the selected data to an Excel file called DFID_Uganda_implementers.xlsx. As Pandas treats each column as a series, each row of a column is also indexed (has a number attached to it, starting from 0). You can chose to include the Index number (index=True) as the first column or your Excel or CSV sheet, or not include it (index=False). You can also include the column headers by including header=True."""

# this exports the filtered data to a new Excel file, with headers and without the Index column
implementers.to_excel (r"DFID_Uganda_implementers.xlsx", index=False, header=True)