how to achieve pandas dataframe in certain manner-CodePudding

current data frame

desired format

is there any possible way to convert python Pandas DataFrame to 2nd image mention. what i am trying to do is take country column and group by values of sell and buy and create new two columns based on country.

a=[('uk', 'Balaji B',Decimal('48998'), Decimal('41843')),('uk', 'Balaji B',Decimal('48998'), Decimal('41843')),('uk', 'Balaji B',Decimal('48998'), Decimal('41843')),('uk', 'Balaji B',Decimal('48998'), Decimal('41843')),('uk', 'Balaji B',Decimal('48998'), Decimal('41843')),('uk', 'Balaji B',Decimal('48998'), Decimal('41843')),('uk', 'Balaji B',Decimal('48998'), Decimal('41843')),('india', 'Balaji B',Decimal('48998'), Decimal('41843')),('india', 'Balaji B',Decimal('48998'), Decimal('41843')),('india', 'Balaji B',Decimal('48998'), Decimal('41843')),('india', 'Balaji B',Decimal('48998'), Decimal('41843')),('usa', 'Balaji B',Decimal('48998'), Decimal('41843')),('usa', 'Balaji B',Decimal('48998'), Decimal('41843'))]
pd.DataFrame(a)

CodePudding user response：

Try with pivot_table:

output = df.pivot_table(["sale", "buy"], "users", "country") \
           .swaplevel(axis=1) \
           .sort_values("country", axis=1)           

>>> output

country       india               ...         usa             
                buy         sale  ...         buy         sale
users                             ...                         
abc      22724826.0  133418510.0  ...         NaN          NaN
as       34036460.0  538978781.0  ...         NaN          NaN
ghi       8412213.0   44774045.0  ...         NaN          NaN
gth       2449408.0    5797027.0  ...         NaN          NaN
oiuy     23618387.0  188875085.0  ...         NaN          NaN
qwe      16142525.0   30675496.0  ...   1603671.0    1603671.0
qwer            NaN          NaN  ...  33201345.0   66810216.0
reww            NaN          NaN  ... -10309654.0  415017600.0
sad             NaN          NaN  ...         NaN          NaN
yu        3191686.0   18612977.0  ...         NaN          NaN

Input df:

   country   users         sale         buy
0    india     abc  133418510.0  22724826.0
1    india     def          NaN         NaN
2    india     ghi   44774045.0   8412213.0
3    india     hil          NaN         NaN
4    india      as  538978781.0  34036460.0
5    india     gth    5797027.0   2449408.0
6    india    oiuy  188875085.0  23618387.0
7    india      yu   18612977.0   3191686.0
8    india     qwe   30675496.0  16142525.0
9      usa    qwer   66810216.0  33201345.0
10     usa     qwe          NaN         NaN
11     usa     qwe    1603671.0   1603671.0
12     usa    reww  415017600.0 -10309654.0
13      uk     sad  142834632.0  20545881.0
14      uk  asdasd          NaN         NaN