<jupyter_start><jupyter_text>Merging multiple excel files or csv filesThis tip is especially useful when the data from the database span a long period of time (say, 20 years). In that case, it is impossible to download data with tens or hundreds of variables in one single file; hence, it is more practical to download data into several files. That is why we need a simple code to concatenate all these files into one for further cleaning and analysis.Requirements for data: Basically, the files are from the same database, so they have the same format.Files must have the same number of columnsColumns have the same name and order<jupyter_code># import necessary packages

import pandas as pd
import os
import glob
# define the extension of the files to be concatenated

extension = 'csv' # can be changed to 'xlsx'
all_filenames = [i for i in glob.glob('*.{}'.format(extension))]
# combine all files in the list

combined_file = pd.concat([pd.read_csv(f, skipfooter = 4, engine = "python") for f in all_filenames])

# skipfooter parameter: to skip a certain number of lines at the end of the file. In this case, since the data files have four irrelevant lines at the end, skipping them is necessary. 
# engine parameter: Parser engine to use. The C engine is faster while the python engine is currently more feature-complete.<jupyter_output><empty_output><jupyter_text>That's it. The "combined_file" can be further analysed in python or exported to local machine to analyse using excel.<jupyter_code># export to local machine

combined_file.to_csv("combined_file.csv")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.058125, 'fraction_numerical': 0.001875, 'mean_word_length': 5.08745247148289, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 9, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '472939', 'license': 'MIT', 'n_tokens_mistral': 435, 'n_tokens_neox': 420, 'n_words': 229, 'path': 'merging_multiple_files/merging_multiple_files.ipynb', 'repo_name': 'AnhVu3011/arranging_data_into_panel', 'size': '1600'}