Pandas

lastmod 2026-05-24created 2015-12-1838 edits

pandas is an open source, BSD-licensed library providing high-performance, easy-to-use data structures and data analysis tools for the Python programming language.

How to iterate over data

Here are several ways to iterate over the rows and columns of a DataFrame

import pandas as pd

# 예시 데이터프레임
df = pd.DataFrame({
    'A': [1, 2, 3],
    'B': [4, 5, 6],
    'C': [7, 8, 9]
})

# 1. 행(row) 순회하기
# 1.1 iterrows() - index와 Series 반환
for index, row in df.iterrows():
    print(f"Index: {index}")
    print(f"Row: {row['A']}, {row['B']}, {row['C']}")

# 1.2 itertuples() - 각 행을 named tuple로 반환 (더 빠름)
for row in df.itertuples():
    print(f"Index: {row.Index}")
    print(f"Values: {row.A}, {row.B}, {row.C}")

# 1.3 index를 사용한 순회
for idx in range(len(df)):
    row = df.iloc[idx]
    print(f"Row {idx}: {row['A']}, {row['B']}, {row['C']}")

# 2. 열(column) 순회하기
# 2.1 기본 열 순회
for col in df.columns:
    print(f"Column {col}:")
    print(df[col].values)

# 2.2 items()를 사용한 열 순회 - (열 이름, Series) 반환
for col_name, col_data in df.items():
    print(f"Column {col_name}:")
    print(col_data.values)

# 3. 전체 셀 순회하기
# 3.1 이중 루프 사용
for i in range(len(df)):
    for col in df.columns:
        value = df.iloc[i][col]
        print(f"Row {i}, Col {col}: {value}")

# 3.2 numpy array로 변환 후 순회
for i in range(df.shape[0]):
    for j in range(df.shape[1]):
        value = df.iloc[i, j]
        print(f"Row {i}, Col {df.columns[j]}: {value}")

Characteristics of each method and notes on usage:

Iterating over rows:
- iterrows(): most intuitive but relatively slow
- itertuples(): the fastest method, recommended especially for large DataFrames
- iloc[]: useful when index-based access is needed
Iterating over columns:
- Using the columns attribute: simple and intuitive
- items(): useful when handling column names and data together
Performance considerations:
- For large data, itertuples() is the fastest
- Use vectorized operations where possible (instead of for loops)
- Converting to a numpy array before processing may be faster

# 성능 최적화 예시 (벡터화 연산)
# for loop 대신 이렇게 하는 것이 더 빠름
df['new_col'] = df['A'] * 2 + df['B']

How to add a column

Here are various ways to add a new column to a DataFrame:

import pandas as pd

# 예시 데이터프레임
df = pd.DataFrame({
    'A': [1, 2, 3],
    'B': [4, 5, 6]
})

# 1. 직접 할당
# 1.1 단일 값 할당
df['C'] = 10  # 모든 행에 동일한 값 할당

# 1.2 리스트나 배열 할당
df['D'] = [7, 8, 9]  # 각 행마다 다른 값 할당

# 1.3 다른 컬럼을 기반으로 계산
df['E'] = df['A'] + df['B']  # 컬럼 A와 B의 합

# 2. insert() 메서드 사용 - 특정 위치에 삽입
df.insert(loc=1, column='F', value=[10, 11, 12])

# 3. assign() 메서드 사용 - 체이닝 가능
df = df.assign(G=lambda x: x['A'] * 2,
               H=lambda x: x['B'] + 5)

# 4. 조건문으로 새 컬럼 생성
df['I'] = df['A'].apply(lambda x: 'High' if x > 2 else 'Low')

# 5. 여러 컬럼 한번에 추가
new_cols = {
    'J': [13, 14, 15],
    'K': [16, 17, 18]
}
df = pd.concat([df, pd.DataFrame(new_cols)], axis=1)

# 6. apply 함수로 복잡한 계산 후 추가
df['L'] = df.apply(lambda row: row['A'] * row['B'] + 2, axis=1)

# 7. 기존 컬럼 복사
df['M'] = df['A'].copy()

# 8. where 조건으로 추가
df['N'] = df['A'].where(df['A'] > 2, df['B'])

# 9. cut/qcut으로 범주형 컬럼 추가
df['O'] = pd.cut(df['A'], bins=2, labels=['Low', 'High'])

Characteristics of each method and notes on usage:

Direct assignment (df['new_col'] = value)
- The simplest and most intuitive method
- Can assign a single value, a list, or a Series
insert() method
- Useful for inserting a column at a specific position
- loc: index of the position to insert at
- column: new column name
- value: values for the column
assign() method
- Suited to method chaining
- Returns a new DataFrame without modifying the original DataFrame
- Can use lambda functions

Caveats regarding data types:

# 데이터 타입 지정하여 컬럼 추가
df['P'] = pd.Series([1, 2, 3], dtype='int32')

# 날짜 컬럼 추가
df['date'] = pd.date_range('2024-01-01', periods=len(df))

# 범주형(categorical) 컬럼 추가
df['category'] = pd.Categorical(['A', 'B', 'C'])

Performance optimization:

# 대용량 데이터의 경우 벡터화 연산 사용 권장
# Bad (느림):
df['new_col'] = df.apply(lambda row: row['A'] + row['B'], axis=1)

# Good (빠름):
df['new_col'] = df['A'] + df['B']

Choose the most suitable of these methods for your situation. In general, direct assignment is useful for simple operations, and the assign() or apply() methods for complex ones.

How to add a column to the row being iterated

Here is how to add new computed results to each row while iterating over a DataFrame:

import pandas as pd

# 예시 데이터프레임
df = pd.DataFrame({
    'A': [1, 2, 3],
    'B': [4, 5, 6]
})

# 1. iterrows() 사용 - 새 컬럼을 미리 만들고 값 할당
df['C'] = None  # 또는 df['C'] = 0
for idx, row in df.iterrows():
    # 현재 행의 값들을 이용해 계산
    result = row['A'] * row['B']
    df.at[idx, 'C'] = result

# 2. apply() 사용 (더 효율적)
def process_row(row):
    row['C'] = row['A'] * row['B']
    return row

df = df.apply(process_row, axis=1)

# 3. 여러 컬럼 한번에 추가
df[['C', 'D']] = None  # 먼저 빈 컬럼 생성
for idx, row in df.iterrows():
    df.at[idx, 'C'] = row['A'] * 2
    df.at[idx, 'D'] = row['B'] + 5

# 4. loc 사용
for idx in df.index:
    df.loc[idx, 'E'] = df.loc[idx, 'A'] + df.loc[idx, 'B']

# 5. 복잡한 조건부 계산
df['F'] = None
for idx, row in df.iterrows():
    if row['A'] > 2:
        df.at[idx, 'F'] = row['A'] * row['B']
    else:
        df.at[idx, 'F'] = row['A'] + row['B']

In most cases, it is better to use vectorized operations where possible.

Performance optimization

# 비효율적인 방법
for idx, row in df.iterrows():
    df.loc[idx, 'new_col'] = row['A'] * 2  # 느림

# 효율적인 방법 1: at 사용
for idx, row in df.iterrows():
    df.at[idx, 'new_col'] = row['A'] * 2  # 더 빠름

# 효율적인 방법 2: 벡터화 연산
df['new_col'] = df['A'] * 2  # 가장 빠름

For memory efficiency

# 큰 데이터셋의 경우
chunk_size = 1000
for i in range(0, len(df), chunk_size):
    chunk = df.iloc[i:i + chunk_size]
    for idx, row in chunk.iterrows():
        df.at[idx, 'new_col'] = row['A'] * 2

Updating multiple columns at once

# 효율적인 방법
for idx, row in df.iterrows():
    updates = {
        'C': row['A'] * 2,
        'D': row['B'] + 5,
        'E': row['A'] + row['B']
    }
    for col, value in updates.items():
        df.at[idx, col] = value

With conditional logic

def calculate_value(row):
    if row['A'] > 2:
        return row['A'] * row['B']
    elif row['B'] < 5:
        return row['A'] + row['B']
    else:
        return 0

# 방법 1: apply 사용
df['result'] = df.apply(calculate_value, axis=1)

# 방법 2: iterrows 사용
df['result'] = None
for idx, row in df.iterrows():
    df.at[idx, 'result'] = calculate_value(row)

Error handling

df['result'] = None
for idx, row in df.iterrows():
    try:
        df.at[idx, 'result'] = complex_calculation(row)
    except Exception as e:
        print(f"Error at index {idx}: {e}")
        df.at[idx, 'result'] = None  # 또는 다른 기본값

Insert a row

python - Insert a row to pandas dataframe - Stack Overflow

Testing a few of the answers makes it clear that using pd.concat() is more efficient for large DataFrames.

The ignore_index=True argument is an option that makes pandas ignore the index and create a new consecutive index when merging data or adding rows.

For example, setting ignore_index=True when you concat a DataFrame or reset_index ignores the existing index and assigns a new index starting from 0.

This is useful when you don't need to keep the existing index when concatenating DataFrames or resetting the index.

pd.concat() + list

df = pd.DataFrame(columns=['a', 'b'])
for i in range(10000):
    df = pd.concat([pd.DataFrame([[1,2]], columns=df.columns), df], ignore_index=True)

4.88 s ± 47.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

pd.append() + dict [removed as of v2.0.0]

df = pd.DataFrame(columns=['a', 'b'])
for i in range(10000):
    df = df.append({'a': 1, 'b': 2}, ignore_index=True)

10.2 s ± 41.4 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

pd.DataFrame().loc + index operations

df = pd.DataFrame(columns=['a','b'])
for i in range(10000):
    df.loc[-1] = [1,2]
    df.index = df.index + 1
    df = df.sort_index()

17.5 s ± 37.3 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Another concat example

import pandas as pd
import numpy as np

df1 = pd.DataFrame(np.array([[2, 3, 4]]), columns=['A', 'B', 'C'])
df2 = pd.DataFrame(np.array([[5, 6, 7]]), columns=df.columns)
df3 = pd.concat([df1, df2], ignore_index=True)

print(df3)

CSV usage example

import pandas as pd
import glob

# csv 파일들 목록
files = glob.glob("*.csv")

# csv 파일들 읽기
dfs = [pd.read_csv(f, encoding="cp949") for f in files]

# 모든 데이터 프레임(DataFrame) 병합
# `ignore_index=True`를 설정하면 dfs의 모든 DataFrame 인덱스를 무시하고 0부터 시작하는 연속된 인덱스를 생성합니다.
df = pd.concat(dfs, ignore_index=True)

# idx 기준으로 재 정렬
sorted_df = df.sort_values(by="idx", ascending=True)

# 결과 저장
sorted_df.to_csv("sorted_df.csv", index=False)

Reading SQLite3

import sqlite3
import pandas as pd

with sqlite3.connect("database.sqlite") as conn:
    query = "select * from my_table where value <= 0"
    df = pd.read_sql_query(query, conn)
    print(df)

How to specify data types

There are several ways to resolve mixed data types in a particular column.

Specifying the dtype option

When reading a CSV file, use the dtype argument to set the data type explicitly. For example:

df = pd.read_csv(filepath, dtype={'column_name': str})

column_name is the name of the column with the problem. Here it is set to string, but change it to a suitable type depending on the data.

Reading and analyzing the whole file at once

By default, Pandas reads CSV files in chunks to save memory. Setting low_memory=False reads and analyzes the whole file at once, which prevents the warning.

df = pd.read_csv(filepath, low_memory=False)

Inspecting the problematic column and unifying its data type

If the 5th column (Column 5) of the CSV file is the problem, it is a good idea to inspect that column's data and unify its data type.

df = pd.read_csv(filepath)
print(df.iloc[:, 5].apply(type).value_counts())

After checking the printed types, you can convert or fix the data:

df['column_name'] = df['column_name'].astype(str)  # 모든 값을 문자열로 변환

Preprocessing the CSV file

You can also fix the problem by modifying the CSV file itself. For example, unify the data format of the problematic column in Excel and save it again.

Writing DataFrames to multiple sheets in a single Excel file

import pandas as pd
import sqlite3

# A.sqlite 처리
with open("2410_report.sql", "r", encoding="utf-8") as file:
    sql_query = file.read()

# A.sqlite 연결 및 데이터 읽기
conn_a = sqlite3.connect("A.sqlite")
df_a = pd.read_sql_query(sql_query, conn_a)
conn_a.close()

# B.sqlite 연결 및 데이터 읽기
conn_b = sqlite3.connect("B.sqlite")
df_b = pd.read_sql_query(sql_query, conn_b)
conn_b.close()

# Excel에 쓰기 (두 시트를 각각 저장)
output_excel = "output.xlsx"
with pd.ExcelWriter(output_excel, engine="openpyxl") as writer:
    df_a.to_excel(writer, sheet_name="A", index=False)
    df_b.to_excel(writer, sheet_name="B", index=False)

print(f"데이터가 {output_excel} 파일로 저장되었습니다. (A 시트: A.sqlite, B 시트: B.sqlite)")

Writing with pandas to a Workbook already opened with openpyxl

import pandas as pd
from openpyxl import Workbook

# 이미 열려 있는 Workbook 가정
wb = Workbook()  # 또는 openpyxl.load_workbook()으로 기존 파일 불러오기
ws = wb.active  # 활성화된 Sheet 선택

# Pandas DataFrame 생성
data = {
    "Name": ["Alice", "Bob", "Charlie"],
    "Age": [25, 30, 35],
    "City": ["New York", "Los Angeles", "Chicago"]
}
df = pd.DataFrame(data)

# DataFrame 데이터를 openpyxl Worksheet에 쓰기
for r_idx, row in enumerate(df.itertuples(index=False), start=1):
    for c_idx, value in enumerate(row, start=1):
        ws.cell(row=r_idx, column=c_idx, value=value)

# 저장하거나 작업 계속 진행
wb.save("example.xlsx")  # 저장 (필요 시)

Adding directly to Excel cells

# 헤더 추가
for c_idx, column in enumerate(df.columns, start=1):
    ws.cell(row=1, column=c_idx, value=column)

# 데이터 추가 (헤더 아래부터 시작)
for r_idx, row in enumerate(df.itertuples(index=False), start=2):
    for c_idx, value in enumerate(row, start=1):
        ws.cell(row=r_idx, column=c_idx, value=value)

Adding to an openpyxl WorkSheet

from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd

# 샘플 DataFrame 생성
data = {
    "Name": ["Alice", "Bob", "Charlie"],
    "Age": [25, 30, 35],
    "City": ["New York", "Los Angeles", "Chicago"]
}
df = pd.DataFrame(data)

# Workbook 및 Worksheet 생성
wb = Workbook()
ws = wb.active
ws.title = "Sample Data"

# DataFrame 데이터를 Worksheet에 쓰기
# header=True: 컬럼명을 포함
# index=False: 인덱스를 포함하지 않음
for row in dataframe_to_rows(df, index=False, header=True):
    ws.append(row)

# 엑셀 파일 저장
wb.save("example.xlsx")

How to merge two DataFrames

import pandas as pd

# 데이터 읽기
df = pd.read_excel("data.xlsx")

# 상부와 측면 쌍으로 병합
df_upper = df[df['Column7'] == '상부']
df_side = df[df['Column7'] == '측면']

# Key 열 기준으로 병합
merged_df = pd.merge(df_upper, df_side, on=['Column1', 'Column2', 'Column3'], suffixes=('_상부', '_측면'))

# 결과 저장
merged_df.to_excel("merged_data.xlsx", index=False)

How to replace all data in a specific column of a DataFrame with the result of passing it to a function

import pandas as pd

# 예제 DataFrame
data = {'col1': [1, 2, 3], 'col2': ['a', 'b', 'c']}
df = pd.DataFrame(data)

# 사용자 정의 함수
def my_text_append(val):
    return f"Appended_{val}"

# 특정 열(col2)의 데이터를 함수 결과로 대체
df['col2'] = df['col2'].apply(my_text_append)

print(df)

How to sort

# idx 기준으로 재 정렬
sorted_df = df.sort_values(by="idx", ascending=True)

# "UP_WORK_DT", "WORK_SEQ" 두 개의 기준으로 재 정렬
self._df = self._df.sort_values(by=["UP_WORK_DT", "WORK_SEQ"], ascending=True)

Note that when re-sorting as above, the index keeps the original index, so you need to reset it (if necessary).

self._df = self._df.reset_index()

Grouping identical values of a specific column

grouped = df.groupby("heat_text")
dfgs = {str(name): group for name, group in grouped}

Assigning a group index at the point where an increasing number resets, then adding a column

1, 2, 3, ..., 9, 10, 11, 1, 2, 3, ..., 16, 17, 18, 1, 2, 3, ...

If there is a column like this that increases by 1 and resets to 1 at certain points,

here is how to bind each increasing run into the same group index

import pandas as pd

# 예제 데이터 생성
data = {
    "장입순서": [1] * 28 + [2] * 7,
    "그라플순서": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 1, 2, 3, 4, 5, 6, 7],
}

df = pd.DataFrame(data)

# "그라플순서"가 초기화되는 순간을 감지
df['그라플그룹'] = (df['그라플순서'] < df['그라플순서'].shift(1)).cumsum() + 1

print(df)

shift(1) - gets the value of the previous row.
- This checks whether the current row's "그라플순서" (grapple order) is smaller than the previous value.
< operation - returns True if "그라플순서" (grapple order) is smaller than the previous value.
cumsum() - accumulates True values to build group numbers.
+1 - makes group numbers start from 1.

This way you can add new group numbers based on the moments when each "그라플순서" (grapple order) resets.

How to store the order (Index) as-is in a separate column

Simply copies the current index into a new column. The existing index is kept as-is.

# 기존 index를 'ORI_IDX' 컬럼으로 저장하는 방법
df['ORI_IDX'] = df.index

Converts the index into the 'ORI_IDX' column. A new numeric index (starting from 0) is created. The more official method.

# 또는 reset_index() 사용
df = df.reset_index(names=['ORI_IDX'])

Temporarily changing display options

python - How can I display full (non-truncated) dataframe information in HTML when converting from Pandas dataframe to HTML? - Stack Overflow

import pandas as pd

# ...

with pd.option_context('display.max_colwidth', None,
                       'display.max_columns', None,
                       'display.max_rows', None):
    display(df)

Saving to Excel

R, a friend of Python analysis and programming (by R Friend) :: (Python pandas) Exporting a DataFrame to Excel (write DataFrame to Excel): pd.DataFrame.to_excel()

write an object to an Excel sheet using pd.DataFrame.to_excel()

df.to_excel(
    xlxs_dir,  # directory and file name to write
    sheet_name="Sheet1",
    na_rep="NaN",
    float_format="%.2f",
    header=True,
    # columns=["group", "value_1", "value_2"], # if header is False
    index=True,
    index_label="id",
    startrow=1,
    startcol=1,
    # engine="xlsxwriter",
    freeze_panes=(2, 0),
)

Troubelshooting

DtypeWarning: Columns (5) have mixed types

/home/your/Project/steel-reports/steelreports/apps/csv_to_sqlite/app.py:56: DtypeWarning: Columns (5) have mixed types. Specify dtype option on import or set low_memory=False.
  df = pd.read_csv(filepath)

This warning occurs when a particular column has mixed data types (the 5th one in the warning above). See #How to specify data types.

A value is trying to be set on a copy of a DataFrame or Series through chained assignment using an inplace method

An error roughly like the following is printed:

/home/your/Project/vrs/vrs/assets/_append_recommendation_rate.py:29: FutureWarning: A value is trying to be set on a copy of a DataFrame or Series through chained assignment using an inplace method.
The behavior will change in pandas 3.0. This inplace method will never work because the intermediate object on which we are setting values always behaves as a copy.

For example, when doing 'df[col].method(value, inplace=True)', try using 'df.method({col: value}, inplace=True)' or df[col] = df[col].method(value) instead, to perform the operation inplace on the original object.


  df[COL_RECOMMENDATION_RATE].fillna(0.0, inplace=True)

Fix it as follows:

df[COL_RECOMMENDATION_RATE] = df[COL_RECOMMENDATION_RATE].fillna(0.0)

numpy.dtype size changed, may indicate binary incompatibility. Expected 96 from C header, got 88 from PyObject

As soon as pandas is imported with import pandas, the following error is printed:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/your/Project/your-api/.venv/lib/python3.11/site-packages/pandas/__init__.py", line 22, in <module>
    from pandas.compat import is_numpy_dev as _is_numpy_dev  # pyright: ignore # noqa:F401
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/your/Project/your-api/.venv/lib/python3.11/site-packages/pandas/compat/__init__.py", line 18, in <module>
    from pandas.compat.numpy import (
  File "/home/your/Project/your-api/.venv/lib/python3.11/site-packages/pandas/compat/numpy/__init__.py", line 4, in <module>
    from pandas.util.version import Version
  File "/home/your/Project/your-api/.venv/lib/python3.11/site-packages/pandas/util/__init__.py", line 2, in <module>
    from pandas.util._decorators import (  # noqa:F401
  File "/home/your/Project/your-api/.venv/lib/python3.11/site-packages/pandas/util/_decorators.py", line 14, in <module>
    from pandas._libs.properties import cache_readonly
  File "/home/your/Project/your-api/.venv/lib/python3.11/site-packages/pandas/_libs/__init__.py", line 13, in <module>
    from pandas._libs.interval import Interval
  File "pandas/_libs/interval.pyx", line 1, in init pandas._libs.interval
ValueError: numpy.dtype size changed, may indicate binary incompatibility. Expected 96 from C header, got 88 from PyObject

If your numpy version is 2.x, try downgrading to 1.x.

FutureWarning: ... Don't supply a list with a single grouper to avoid this warning.

/home/your/Project/steel-reports/steelreports/apps/report/app.py:344: FutureWarning: In a future version of pandas, a length 1 tuple will be returned when iterating over a groupby with a grouper equal to a list of length 1. Don't supply a list with a single grouper to avoid this warning.
  for key, g0 in groups0:

This warning tells you that the behavior of the groupby method will change in a future version of Pandas.

The problem occurs when you use a list as the grouping key when calling groupby. In particular, this warning appears when the grouping key is a list but contains only a single element.

groups0 = df.groupby(['column_name'])
for key, g0 in groups0:
    pass

After the fix:

groups0 = df.groupby('column_name')  # 리스트 대신 단일 값 전달
for key, g0 in groups0:
    pass

SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df[k.header.group] = is_new_group.cumsum() + 1
/home/your/Project/steel-reports/steelreports/apps/report/app.py:247: SettingWithCopyWarning:
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

SettingWithCopyWarning is a warning raised in Pandas when you try to modify values in a slice of a DataFrame. It indicates that it is unclear whether the data is a copy or the original. This can lead to unintended behavior, so it is a good idea to fix it.

Original code:

    @staticmethod
    def push_grapple_group(df: DataFrame) -> DataFrame:
        is_new_group = df[k.header.grapple] <= df[k.header.grapple].shift(1)
        assert isinstance(is_new_group, Series)
        df[k.header.group] = is_new_group.cumsum() + 1
        return df

Use .loc.

    @staticmethod
    def push_grapple_group(df: DataFrame) -> DataFrame:
        is_new_group = df[k.header.grapple] <= df[k.header.grapple].shift(1)
        assert isinstance(is_new_group, Series)
        df.loc[:, k.header.group] = is_new_group.cumsum() + 1
        return df

But when I used the method above, the warning was still printed. So I copied the object with .copy() and returned it:

    @staticmethod
    def push_grapple_group(df: DataFrame) -> DataFrame:
        df = df.copy()
        is_new_group = df[k.header.grapple] <= df[k.header.grapple].shift(1)
        assert isinstance(is_new_group, Series)
        df[k.header.group] = is_new_group.cumsum() + 1
        return df

Favorite site

author Zer0 sitemap