使用SAS Proc Transpose转置数据集时避免金额重复计数的问题

Got it, let's fix this transposition issue so each account only shows its own amount without duplication—this way your totals stay accurate even with large datasets. Here are solutions for common tools you might be using:

SQL Solution #

If you're working with a database (like PostgreSQL, MySQL, SQL Server), this approach splits your original table into two separate sets of records (one for each account column) then combines them, filling the unused amount column with 0.

-- Replace 'your_table' with your actual table name
SELECT 
    Account1Amount AS Amount1,
    0 AS Amount2,
    Account1 AS Col1
FROM your_table
UNION ALL
SELECT 
    0 AS Amount1,
    Account2Amount AS Amount2,
    Account2 AS Col1
FROM your_table
ORDER BY Col1;

How this works: #

• The first SELECT pulls all records from Account 1 and their corresponding amounts, setting Amount2 to 0.
• The second SELECT does the same for Account 2, setting Amount1 to 0.
• UNION ALL combines both result sets (use UNION only if you need to remove duplicates, but UNION ALL is faster for large data).
• The ORDER BY sorts the final result by the account name (Col1) for readability.

Python Pandas Solution #

For large datasets processed in Python, Pandas makes it easy to split, transform, and combine the data:

import pandas as pd

# Sample input data (replace with your actual DataFrame)
df = pd.DataFrame({
    'Account 1': ['A', 'F'],
    'Account 2': ['B', 'D'],
    'Account1Amount': [25, 10],
    'Account2Amount': [55, 70]
})

# Split into two separate DataFrames for each account type
df_account1 = df[['Account 1', 'Account1Amount']].rename(
    columns={'Account 1': 'Col1', 'Account1Amount': 'Amount1'}
)
df_account1['Amount2'] = 0

df_account2 = df[['Account 2', 'Account2Amount']].rename(
    columns={'Account 2': 'Col1', 'Account2Amount': 'Amount2'}
)
df_account2['Amount1'] = 0

# Combine and clean up the result
final_df = pd.concat([df_account1, df_account2]).sort_values('Col1').reset_index(drop=True)

print(final_df)

Output: #

Col1  Amount1  Amount2
0    A       25        0
1    B        0       55
2    D        0       70
3    F       10        0

Excel Solution #

If you prefer using Excel (especially for smaller datasets or manual workflows):

Manual Approach: #

1. Create a new table with columns Amount1, Amount2, Col1.
2. Copy all values from Account 1 into Col1, their corresponding Account1Amount into Amount1, and fill Amount2 with 0.
3. Below those rows, copy all values from Account 2 into Col1, their corresponding Account2Amount into Amount2, and fill Amount1 with 0.
4. Sort the table by Col1 to organize the accounts.

Automated Power Query Approach (for large datasets): #

1. Load your data into Excel Power Query (Data > Get Data > From Table/Range).
2. Duplicate the query twice (right-click the query > Duplicate).
3. For the first duplicate:
   • Keep only Account 1 and Account1Amount columns.
   • Rename columns to Col1 and Amount1.
   • Add a custom column Amount2 with value 0.
4. For the second duplicate:
   • Keep only Account 2 and Account2Amount columns.
   • Rename columns to Col1 and Amount2.
   • Add a custom column Amount1 with value 0.
5. Merge the two queries (Home > Append Queries > Append Queries as New).
6. Sort the appended table by Col1, then load it back into Excel.

内容的提问来源于stack exchange，提问作者mustafghan