Pandas - Create Separate Columns in DataFrame Based on a Specific Column's Values

Question

Let's say I have a simple Pandas DataFrame where one column contains a country name and another column contains some value. For example:

# Import Python Libraries
import numpy as np
import pandas as pd

# Create Sample DataFrame
df = pd.DataFrame(data={'Country': ['United States','United States','United States','United States', \
                     'United States','United States','United States','United States', \
                     'United States','United States','United States','United States', \
                     'Canada','Canada','Canada','Canada','Canada','Canada','Mexico', \
                     'Mexico','Mexico','Mexico','England','England','England','England', \
                     'England','England','England','England','England','England','England', \
                     'England','England','England','France','France','France','Spain','Germany', \
                     'Germany','Germany','Germany','Germany','Germany','Germany','Germany', \
                     'Germany','Germany'], 'Value': np.random.randint(1000, size=50)})

Which generates:

print(df.head())

Index     Country     Value
  0    United States   943
  1    United States   567
  2    United States   534
  3    United States   700
  4    United States   470

My question is, what is the easiest way in Python to convert this DataFrame into one where each country has its own column and all the values of that country are listed in that column? In other words, how can I easily create a DataFrame where the number of columns is the unique count of countries in the 'Country' column, and that each column's length will vary depending on the number of times the corresponding country appears in the original DataFrame?

Here is sample code that provides a solution:

# Store Unique Country Names in Variable
columns = df['Country'].unique()

# Create Individual Country DataFrames
df_0 = df[df['Country'] == columns[0]]['Value'].values.tolist()
df_1 = df[df['Country'] == columns[1]]['Value'].values.tolist()
df_2 = df[df['Country'] == columns[2]]['Value'].values.tolist()
df_3 = df[df['Country'] == columns[3]]['Value'].values.tolist()
df_4 = df[df['Country'] == columns[4]]['Value'].values.tolist()
df_5 = df[df['Country'] == columns[5]]['Value'].values.tolist()
df_6 = df[df['Country'] == columns[6]]['Value'].values.tolist()

# Create Desired Output DataFrame
data_dict = {columns[0]: df_0, columns[1]: df_1, columns[2]: df_2, columns[3]: df_3, columns[4]: df_4, columns[5]: df_5, columns[6]: df_6}
new_df = pd.DataFrame({k:pd.Series(v[:len(df)]) for k,v in data_dict.items()})

Which generates:

print(new_df)

    United States   Canada  Mexico  England France  Spain   Germany
0   838.0           135.0   496.0   568.0   71.0    588.0   811.0
1   57.0            118.0   268.0   716.0   422.0   NaN     107.0
2   953.0           396.0   850.0   860.0   707.0   NaN     318.0
3   251.0           294.0   815.0   888.0   NaN     NaN     633.0
4   127.0           466.0   NaN     869.0   NaN     NaN     910.0
5   892.0           824.0   NaN     776.0   NaN     NaN     472.0
6   11.0            NaN     NaN     508.0   NaN     NaN     466.0
7   563.0           NaN     NaN     299.0   NaN     NaN     200.0
8   864.0           NaN     NaN     568.0   NaN     NaN     637.0
9   810.0           NaN     NaN     78.0    NaN     NaN     392.0
10  268.0           NaN     NaN     106.0   NaN     NaN     NaN
11  389.0           NaN     NaN     153.0   NaN     NaN     NaN
12  NaN             NaN     NaN     217.0   NaN     NaN     NaN
13  NaN             NaN     NaN     941.0   NaN     NaN     NaN

While the above code works, it's obviously not a tenable solution for larger data sets. What is the most efficient way of generating this result from the original DataFrame?

Thank you!

ALollz · Accepted Answer · 2018-09-03T19:43:51.540

Probably not the most performant solution out there, but it will get everything top justified.

df1 = df.groupby('Country').Value.agg(list).apply(pd.Series).T
df1.columns.name=None

Output: df1

    Canada  England  France  Germany  Mexico  Spain  United States
0    653.0    187.0   396.0    491.0   251.0  433.0          919.0
1    215.0    301.0    25.0    107.0   755.0    NaN          435.0
2    709.0    581.0   858.0    691.0   158.0    NaN          166.0
3    626.0    706.0     NaN    572.0   767.0    NaN          352.0
4    516.0    999.0     NaN    393.0     NaN    NaN          906.0
5    847.0    688.0     NaN    780.0     NaN    NaN          489.0
6      NaN    722.0     NaN     19.0     NaN    NaN          322.0
7      NaN    728.0     NaN    166.0     NaN    NaN          753.0
8      NaN    765.0     NaN    299.0     NaN    NaN          155.0
9      NaN    956.0     NaN    449.0     NaN    NaN          438.0
10     NaN     41.0     NaN      NaN     NaN    NaN          588.0
11     NaN     43.0     NaN      NaN     NaN    NaN          796.0
12     NaN    485.0     NaN      NaN     NaN    NaN            NaN
13     NaN    218.0     NaN      NaN     NaN    NaN            NaN

The other option is to make use of Coldspeed's justify function and Yuca's pivot output:

import numpy as np

df2 = df.pivot(index=None, columns='Country', values='Value')
df2 = pd.DataFrame(
          justify(df2.values, invalid_val=np.NaN, axis=0, side='up'), 
          columns=df2.columns
          ).dropna(0, 'all')
df2.columns.name=None

Output: df2

   Canada England France Germany Mexico Spain United States
0     653     187    396     491    251   433           919
1     215     301     25     107    755   NaN           435
2     709     581    858     691    158   NaN           166
3     626     706    NaN     572    767   NaN           352
4     516     999    NaN     393    NaN   NaN           906
5     847     688    NaN     780    NaN   NaN           489
6     NaN     722    NaN      19    NaN   NaN           322
7     NaN     728    NaN     166    NaN   NaN           753
8     NaN     765    NaN     299    NaN   NaN           155
9     NaN     956    NaN     449    NaN   NaN           438
10    NaN      41    NaN     NaN    NaN   NaN           588
11    NaN      43    NaN     NaN    NaN   NaN           796
12    NaN     485    NaN     NaN    NaN   NaN           NaN
13    NaN     218    NaN     NaN    NaN   NaN           NaN

@Alollz I'm getting an error with your first solution 'type' object is not iterable? — ronster1000, Sep 04 '18 at 09:02
That shouldn't happen given your input. Perhaps break up the code to see exactly which part is breaking on your real data. `df1 = df.groupby('Country').Value.agg(list)` then apply Series, then Transpose. — ALollz, Sep 04 '18 at 14:05

score 2 · Answer 2 · answered Sep 03 '18 at 19:50

Use groupby, cumcount, and unstack with T:

df.set_index(['Country',df.groupby('Country').cumcount()])['Value'].unstack().T

Output:

Country  Canada  England  France  Germany  Mexico  Spain  United States
0         535.0    666.0   545.0    522.0   581.0  525.0          394.0
1         917.0    130.0    76.0    882.0   563.0    NaN          936.0
2         344.0    376.0   960.0    442.0   247.0    NaN          819.0
3         760.0    272.0     NaN    604.0   976.0    NaN          975.0
4         745.0    199.0     NaN    512.0     NaN    NaN          123.0
5         654.0    102.0     NaN    114.0     NaN    NaN          690.0
6           NaN    570.0     NaN    318.0     NaN    NaN          568.0
7           NaN    807.0     NaN    523.0     NaN    NaN          385.0
8           NaN     18.0     NaN    890.0     NaN    NaN          451.0
9           NaN     26.0     NaN    635.0     NaN    NaN          282.0
10          NaN    871.0     NaN      NaN     NaN    NaN          771.0
11          NaN    122.0     NaN      NaN     NaN    NaN          505.0
12          NaN      0.0     NaN      NaN     NaN    NaN            NaN
13          NaN    578.0     NaN      NaN     NaN    NaN            NaN

score 0 · Answer 3 · answered Sep 03 '18 at 19:14

pd.pivot takes you halfway there, the issue here is that your index have no information so your non NaN values are not at the top of the df

df.pivot(index=None, columns='Country', values = 'Value')

Country  Canada  England  France      ...        Mexico  Spain  United States
0           NaN      NaN     NaN      ...           NaN    NaN          992.0
1           NaN      NaN     NaN      ...           NaN    NaN          814.0
2           NaN      NaN     NaN      ...           NaN    NaN          489.0
3           NaN      NaN     NaN      ...           NaN    NaN          943.0
4           NaN      NaN     NaN      ...           NaN    NaN          574.0
5           NaN      NaN     NaN      ...           NaN    NaN          428.0
6           NaN      NaN     NaN      ...           NaN    NaN          907.0
7           NaN      NaN     NaN      ...           NaN    NaN          899.0
8           NaN      NaN     NaN      ...           NaN    NaN          379.0
9           NaN      NaN     NaN      ...           NaN    NaN          130.0

You could add a column with the index-like information you'd need to pivot on, e.g. `df["num"] = df.groupby("Country").cumcount()`. After that, using it as the index in pivot should work fine. — DSM, Sep 03 '18 at 19:41

Pandas - Create Separate Columns in DataFrame Based on a Specific Column's Values

3 Answers3