Separate Dataframe Into Multiple New Dataframes And Bulk Retructure The New Dfs

I have a big set of data with 100+ columns of data structured like: country_a country_b year variable1 variable2 ...... varaible100 The goal is to have the 100 variables separated

Solution 1:

Instead of Pivot , use pd.melt , it is effective in your case

Solution 2:

We could use DataFrame.pivot_table then we could join with country column using DataFrame.filter.

new_df = (df.filter(regex='country')
            .join(df.pivot_table(index=df.index, columns='year', values='var_a'))
         )
print(new_df)

Output

  country_a country_b  2018201920200        aa        xx   1.0NaNNaN1        bb        yy   NaN0.0NaN2        cc        zz   NaNNaN1.0

If you can't filter countries using DataFrame.filter then you can select the columns using:

list_columns_names = ['spain', 'england',..]
df[list_columns_names].join(df.pivot_table(...))

if the columns of the countries are together in the dataframe it may be easier to use iloc

num_countries = 10
df.iloc[:,:num_countries].join(df.pivot_table(...))

Another options is set_index + unstack:

new_df = (df.filter(regex='country')
            .join(df.set_index('year', append=True)['var_a'].unstack('year'))
         )

Solution 3:

Here is a way to re-shape the original data frame (using melt, unstack and reset_index), followed by exporting each of var_a, var_b, ..., to its own CSV file:

df_new = (
    df.melt(id_vars=['country_a', 'country_b', 'year'], 
            var_name='variable', 
            value_name='value')
    .set_index(['country_a', 'country_b', 'year', 'variable'])
    .sort_index()
    .squeeze()
    .unstack(level='year')
    .fillna(0)              # for display purposes
    .astype(int)            # also for display purposes
    .reset_index(level=['country_a', 'country_b'])
)
print(df_new)

year     country_a country_b  201820192020
variable                                      
var_a           aa        xx     100
var_b           aa        xx     200
var_c           aa        xx     000
var_a           bb        yy     000
var_b           bb        yy     010
var_c           bb        yy     010
var_a           cc        zz     001
var_b           cc        zz     002
var_c           cc        zz     002

Now export each variable to its own CSV file:

foridxindf_new.index.unique():filename=f'{idx}.csv'withopen(filename,'wt')as handle:#df_new.loc[idx].to_csv(handle)      # <- un-comment this line in your codeprint(filename)print(df_new.loc[idx])print()var_a.csvyearcountry_acountry_b2018  2019  2020variablevar_aaaxx100var_abbyy000var_acczz001var_b.csvyearcountry_acountry_b2018  2019  2020variablevar_baaxx200var_bbbyy010var_bcczz002var_c.csvyearcountry_acountry_b2018  2019  2020variablevar_caaxx000var_cbbyy010var_ccczz002