in Education by
Pandas tidy data, spread variables from one column, gather from another My Problem I need to turn the dataframe below into a tidy format, where each row will be a unique ['GEOG_CODE','COUNTRY'] - 'YEAR' pairing, and there are two variables, defined by Group1. Using Hadley Wickham's notation for tidy data: The observations are defined by the Location-Time pairings. The variables are defined by the column Group1 The values are currently stored for different Years in columns ['2016' '2017' '2018']. In R I would want to: gather the values from the columns ['2016' '2017' '2018']. spread the values from Group1. see Garrett Grolemund's explanation here For my problem: Location is defined by the ['GEOG_CODE','COUNTRY']. Values at different times are defined in the columns ['2016' '2017' '2018']. Variables are defined by Group1 == A or Group1 == B. I want to have each row as a Location-Time pair, with two variables. One for Group1 = A, one for Group1 = B I have this toy_data = { 'GEOG_CODE':['123','234','567','901'], 'COUNTRY':['England' for _ in range(4)], 'Group1':['A','A','B','B'], '2016':np.arange(0,4), '2017':np.arange(0,4), '2018':np.arange(0,4), } in_df = pd.DataFrame(toy_data) in_df Out[]: GEOG_CODE COUNTRY Group1 2016 2017 2018 0 123 England A 0 0 0 1 234 England A 1 1 1 2 567 England B 2 2 2 3 901 England B 3 3 3 I want this So I want the output to look like the dataframe below with columns for each of the values in 'Group1' outcome_data = { 'GEOG_CODE': np.tile(['123','234','567','901'],3), 'COUNTRY':['England' for _ in range(4*3)], 'year':np.tile([2016,2017,2018],4), 'low_A':np.tile(np.arange(0,4),3), 'low_B':np.tile(np.arange(0,4),3), } out = pd.DataFrame(outcome_data) out Out[]: GEOG_CODE COUNTRY year low_A low_B 0 123 England 2016 0 0 1 234 England 2017 1 1 2 567 England 2018 2 2 3 901 England 2016 3 3 4 123 England 2017 0 0 5 234 England 2018 1 1 6 567 England 2016 2 2 7 901 England 2017 3 3 8 123 England 2018 0 0 9 234 England 2016 1 1 10 567 England 2017 2 2 11 901 England 2018 3 3 I tried df.melt() I managed to get the data half of the way by using the melt functionality but then I don't know how to turn the groups into rows. id_vars = ['GEOG_CODE', 'COUNTRY', 'Group1'] value_vars = ['2016', '2017', '2018'] var_name = 'Year' value_name = 'low_Value' melt = in_df.melt(id_vars=id_vars,value_vars=value_vars,var_name=var_name, value_name=value_name) melt Out[]: GEOG_CODE COUNTRY Group1 Year low_Value 0 123 England A 2016 0 1 234 England A 2016 1 2 567 England B 2016 2 3 901 England B 2016 3 4 123 England A 2017 0 5 234 England A 2017 1 6 567 England B 2017 2 7 901 England B 2017 3 8 123 England A 2018 0 9 234 England A 2018 1 10 567 England B 2018 2 11 901 England B 2018 3 JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

1 Answer

0 votes
by
Perhaps you're looking for stack instead of melt: (df.set_index(['GEOG_CODE', 'COUNTRY', 'Group1']) .stack() .unstack(-2) .ffill(axis=1) .bfill(axis=1, downcast='infer') .add_prefix('low_') .reset_index() .rename({'level_2': 'year'}, axis=1)) Group1 GEOG_CODE COUNTRY year low_A low_B 0 123 England 2016 0 0 1 123 England 2017 0 0 2 123 England 2018 0 0 3 234 England 2016 1 1 4 234 England 2017 1 1 5 234 England 2018 1 1 6 567 England 2016 2 2 7 567 England 2017 2 2 8 567 England 2018 2 2 9 901 England 2016 3 3 10 901 England 2017 3 3 11 901 England 2018 3 3

Related questions

0 votes
    Pandas tidy data, spread variables from one column, gather from another My Problem I need to turn the ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 12, 2022 in Education by JackTerrance
0 votes
    Pandas tidy data, spread variables from one column, gather from another My Problem I need to turn the ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 10, 2022 in Education by JackTerrance
0 votes
    I want to log the user session. Currently the code is as follows (setting formatter and handlers is omitted ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Jul 11, 2022 in Education by JackTerrance
0 votes
    I want to log the user session. Currently the code is as follows (setting formatter and handlers is omitted ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Jun 30, 2022 in Education by JackTerrance
0 votes
    I want to log the user session. Currently the code is as follows (setting formatter and handlers is omitted ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Jun 23, 2022 in Education by JackTerrance
0 votes
    I want to log the user session. Currently the code is as follows (setting formatter and handlers is omitted ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Jun 12, 2022 in Education by JackTerrance
0 votes
    I have made a chatroom where users can messages to each other but I want to add a upload file ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 20, 2022 in Education by JackTerrance
0 votes
    I have a legit situation like this where I need to implement a method that changes the incoming object ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 16, 2022 in Education by JackTerrance
0 votes
    In a script where I create many figures with fix, ax = plt.subplots(...), I get the warning ... , JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 13, 2022 in Education by JackTerrance
0 votes
    I have a custom PyPi package. It is installed under Pyhon\Python38\Lib\site-packages\myCustomPackage. In the ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 7, 2022 in Education by JackTerrance
0 votes
    Is there a way to specify the color a text is printed within Idle for Python 3.2? I'm looking ... , JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Feb 25, 2022 in Education by JackTerrance
0 votes
    Is there a way to specify the color a text is printed within Idle for Python 3.2? I'm looking ... , JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Feb 25, 2022 in Education by JackTerrance
0 votes
    I have multiple xlsx files with data in it that i want to import to separate dataframes in Python. ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Jun 3, 2022 in Education by JackTerrance
0 votes
    I have multiple xlsx files with data in it that i want to import to separate dataframes in Python. ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked May 7, 2022 in Education by JackTerrance
0 votes
    I have a dataset, say: DateJoined Name Number DateLeft 11/03/2015 Tom 001199 11/03/2019 11/03/ ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 16, 2022 in Education by JackTerrance
...