[Tips] Let's try merging Ishikawa Prefecture's bear sighting data using Python

Hello.
I am Kawada, a data scientist at the Secretariat of the Council for the Study of Mutual Aid Business Models (IUDC).At IUDC, we are working on analysis and visualization using data with the aim of visualizing and solving regional issues. During the recent "Bear x Coexistence Hackathon," it was a valuable time to think about the coexistence of bears and humans while looking at data with the participants.
Please refer to the following article for the analysis results introduced during the hackathon.

This time, I will introduce how to perform data merging using Python when conducting data analysis.

1. Let's look at the bear sighting data in Ishikawa Prefecture

First, let's see what kind of data is available in the Ishikawa Open Data Catalog.

For the years Reiwa 1 to Reiwa 6, sighting information seems to be summarized by various conditions.
Let's look at the sighting data from forests in Excel.

For example, the columns for "Sighting data from forests" are as follows.

Since time-series (sighting date/time) and spatial information (latitude/longitude) are available, it can be used in various ways, such as visualization on a map or trend analysis by time of day.
※Points to note
Looking at the data, there are several places that need to be cleansed for analysis, such as the sighting date being written as R〇 instead of the Western calendar, and the number of bears having a unit attached, which prevents it from being treated as a numerical value as is. This time, I performed processing to prepare for analysis using Tableau, which is introduced in the following article.

2. Towards integrating multiple CSVs

The data is divided into files by cause of sighting, and since each CSV does not have a column indicating the cause of sighting (forest, river, attractant, etc.), if you integrate them as they are, you will not know "which record corresponds to which cause of sighting."
Therefore, before integrating the data, it is necessary to add a column called "Sighting Category" to clearly indicate the cause of sighting for each file.
Merging procedure:

1. Download all data

2. Add a "Sighting Category" column to all data (work in Excel)

3. Data merging using Python

The following is Python code that reads multiple CSV files and integrates them into one CSV. I have also added a trick to record the file name as a column so that the original cause of sighting can be confirmed based on the file name.

import os
import pandas as pd

# === 設定 ===
folder_path = './'  # CSVファイルがあるフォルダのパス
output_file = 'merged_bear_incidents.csv'  # 統合後に保存するファイル名

# === ファイル統合処理 ===
# フォルダ内の 'bear_incidents' で始まるCSVファイルを対象とする
csv_files = [f for f in os.listdir(folder_path)
             if f.startswith('bear_incidents') and f.endswith('.csv')]

# 各CSVを読み込み、リストに格納
dataframes = []
for file in csv_files:
    df = pd.read_csv(os.path.join(folder_path, file))
    df['source_file'] = file  # オプション: 元ファイル名を列に追加
    dataframes.append(df)

# すべてのデータフレームを縦方向に連結（行方向にマージ）
merged_df = pd.concat(dataframes, ignore_index=True)

# 統合結果をCSVとして保存
merged_df.to_csv(os.path.join(folder_path, output_file), index=False)

print(f"{len(csv_files)} ファイルを統合しました: {output_file}")

Techniques such as "multiple file integration processing" as introduced this time can be widely applied not only to bear sighting information but also to other open data and data analysis sites handled in daily work.
For example, in situations where
multiple files with the same structure exist, such as local government statistical information, facility information, or survey results, you can efficiently organize data using the same method.Please try it out with themes or data you are interested in!