如何从现有Pandas DataFrame生成新DataFrame：将分组列转为行并重复Age列值

Got it, let's fix this problem step by step. You need to reshape your wide DataFrame into a long format where each pair of cityX and countryX becomes a separate row, with the corresponding Age duplicated twice. First, let's break down where your original approach went wrong:

• Your loop only extracted data from the first group (city1 & country1) and completely missed the second group (city2 & country2), which is why your list r was half the length you needed.
• Trying to append the entire r list as a single row to your res DataFrame caused a column mismatch because res only had an Age column, while r contained paired city/country values.

Here are two straightforward, working methods to get your desired result:

Method 1: Using pd.concat (Explicit, Easy to Follow) #

We'll split the original DataFrame into two separate DataFrames (one for each city-country pair), rename their columns to match your target format, then concatenate them and sort to keep paired rows together.

import pandas as pd
import numpy as np

# Original DataFrame setup
data = {'Age': [20, 30, 19, 21],
        'city1':['ny','london',np.nan,np.nan],
        'country1':['usa','usa','usa','usa'],
        'city2':['london','edinburg',np.nan,'tampa'], 
        'country2':['usa','uk',np.nan,np.nan]}
df1 = pd.DataFrame(data)

# Extract and rename the first city-country group
group1 = df1[['Age', 'city1', 'country1']].rename(columns={'city1': 'city', 'country1': 'country'})

# Extract and rename the second city-country group
group2 = df1[['Age', 'city2', 'country2']].rename(columns={'city2': 'city', 'country2': 'country'})

# Combine groups and sort to keep each original row's pairs together
result = pd.concat([group1, group2], ignore_index=False).sort_index().reset_index(drop=True)

print(result)

This will output exactly the DataFrame you're expecting:

Age    city country
0   20     ny     usa
1   20  london     usa
2   30  london     usa
3   30 edinburg      uk
4   19    NaN     usa
5   19    NaN      NaN
6   21    NaN     usa
7   21   tampa      NaN

Method 2: Using pd.wide_to_long (Cleaner, Built for This Scenario) #

Pandas has a dedicated function wide_to_long made for converting wide-format DataFrames with paired columns (like city1/country1, city2/country2) into long format. This is more efficient if you ever need to handle more than two groups.

import pandas as pd
import numpy as np

# Original DataFrame setup
data = {'Age': [20, 30, 19, 21],
        'city1':['ny','london',np.nan,np.nan],
        'country1':['usa','usa','usa','usa'],
        'city2':['london','edinburg',np.nan,'tampa'], 
        'country2':['usa','uk',np.nan,np.nan]}
df1 = pd.DataFrame(data)

# Add an identifier for original rows to pair data correctly
df1['original_id'] = df1.index

# Reshape using wide_to_long
long_df = pd.wide_to_long(
    df1,
    stubnames=['city', 'country'],  # Base names of your paired columns
    i=['original_id', 'Age'],       # Columns to keep as identifiers
    j='group',                      # Temporary column to track group number (1/2)
    sep='',                         # No separator between base name and number
    suffix='\\d+'                   # Match numbers at the end of column names
)

# Clean up the result: drop temporary columns and reorder to match your target
final_df = long_df.reset_index().drop(columns=['original_id', 'group'])[['Age', 'city', 'country']]

print(final_df)

This will produce the exact same desired output as the first method.

Both approaches work perfectly—use pd.concat if you want to see the explicit grouping logic, or pd.wide_to_long for a more concise, scalable solution.

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