3.1 Reading csv-files

df = pd.read_csv("df.csv")

df = pd.read_csv("path/to/file/df.csv")

df = pd.read_csv("df.csv", sep=";")

url = "https://raw.githubusercontent.com/MFuchs1989/Datasets-and-Miscellaneous/main/datasets/Iris_Data.csv" 

df_iris = pd.read_csv(url, error_bad_lines=False)
df_iris.head()

3.2 Reading json files

Another very popular format is json. This usually looks like this:

Also for this pandas offers a quite simple possibility. To illustrate this example, we use a kaggle record of data from twitter.

df = pd.read_json("twitter.json")
df

3.3 Read text files

From time to time it also happens that files are made available to you as text files as the below picture shows.

Looks a bit tricky at first (especially since this file was named as json format but really is not a real json). But this doesn’t matter. We can read this file in two ways.

f = open("some_TEXT_data_as_JSON.json", "r")
firstLine = 1
for x in f:
    y = json.loads(x)
    
    if firstLine == 1:
        df = pd.DataFrame([[y['timestamp_epoch'], y['device_id'], y['figures']]], columns=['timestamp_epoch', 'device_id', 'figures'])
        firstLine = 0
        continue
    
    df2 = pd.DataFrame([[y['timestamp_epoch'], y['device_id'], y['figures']]], columns=['timestamp_epoch', 'device_id', 'figures'])
    frames = [df, df2]
    df = pd.concat(frames,ignore_index=True, sort=False)

df.head()

df = pd.read_csv('some_TEXT_data_as_JSON.json', sep=":", header=None)
df.columns = ['to_delete', 'timestamp_epoch', 'device_id', 'figures']
df.drop(["to_delete"], axis = 1, inplace = True) 
df.timestamp_epoch = df.timestamp_epoch.map(lambda x: x.split(',')[0])
df.device_id = df.device_id.map(lambda x: x.split(',')[0])
df.figures = df.figures.map(lambda x: x.split('}')[0])


df.head()

df['timestamp_epoch2'] = df.timestamp_epoch.astype(float)
df['new_timestamp_epoch'] = df.timestamp_epoch2 / 1000
df['new_timestamp_epoch_round'] = df.new_timestamp_epoch.round()
df['new_timestamp_epoch_round'] = df.new_timestamp_epoch_round.astype(int)
df['final'] = df.new_timestamp_epoch_round.map(lambda x: datetime.datetime.utcfromtimestamp(x).strftime('%Y-%m-%d %H:%M:%S'))
df['DateTime'] = pd.to_datetime(df['final'])
df.drop(["timestamp_epoch2", "new_timestamp_epoch", "new_timestamp_epoch_round", "final"], axis = 1, inplace = True) 

df.head()

4.1 Write to csv

At this point, the question arises how the newly prepared data record can be easily stored. Almost every “read_from” has a “write_to” command. Let’s do so with our previously created dataframe:

df.to_csv('NewDataFrame.csv')

If you do not want to save the index as a new separate column, you can use the following addition:

df.to_csv('NewDataFrame.csv', index = False)

This data set is now saved to the previously set path or any one that can be integrated in this command.

4.2 Write to excel

The CSV format is the most common format in which data is saved, but it is still necessary / desired to save the data in Excel from time to time.

To do so I use the .to_excel function in combination with the xlsxwriter-engine. This is pretty much the same simple process as saving in a csv format. Therefore, this time we not only do the normal storage of the file but also assign it a special label.

Imagine our task is to create a report on financial data on a regular basis and to save it accordingly in excel after the date of creation and the respective quarter.

We do not want to name each time by hand. So we let python work for us.

Here is our new dataframe with financial data:

df = pd.DataFrame({'Transaction': ['46568454684', '89844548864', '90487651685'],
                   'Amount': [32,22,6200,],
                   'Currancy': ['EUR', 'CHF', 'THB']})
df

Well, in order to supplement the naming of the file with information at the current point in time, we have to have the current values displayed.

now = datetime.datetime.now()
now

Here we can also have individual elements output. For example year and month:

now.year

now.month

To get the current quarter, I wrote the following function:

def get_quarter(month):

    if (month <= 3):
        return 'Q1'
    
    elif (month <= 6) and (month > 3):
        return 'Q2'
              
    elif (month <= 9) and (month > 6):
        return 'Q3'

    elif (month <= 12) and (month > 9):
        return 'Q4' 

Let’s test the created function:

for i in range(1, 13):
    total = get_quarter(i)
    print(total)

Works fine!

Now we save the information that we want to use in the naming of the file in our own objects.

now_year = now.year
now_year

month = now.month

now_quarter = get_quarter(month)
now_quarter

Then we combine the two objects:

year_quarter = str(now_year) + now_quarter
year_quarter

Last but not least the current date:

now_date = now.strftime("%Y-%m-%d")
now_date

Now we save our data record in Excel and give this file our desired name with the automatically generated information at the time.

df.to_excel(year_quater + '_Financial_Data_'+ now_date + '.xlsx', sheet_name='Sheet1', engine='xlsxwriter')

The result looks like this:

Or you simply assign a name of your choice:

df.to_excel('My_DataFrame.xlsx', sheet_name='Sheet1', engine='xlsxwriter')

writer = pd.ExcelWriter('Name_of_Excel_File.xlsx', engine='xlsxwriter')

df1.to_excel(writer, sheet_name='Sheet1')
df2.to_excel(writer, sheet_name='Sheet2')
df3.to_excel(writer, sheet_name='Sheet3')

writer.save()