Menu
df1 = pd.DataFrame({'foo': ['one', 'one', 'one', 'two', 'two', 'two'],
'bar': ['A', 'B', 'C', 'A', 'B', 'C']})
| foo | bar | |
|---|---|---|
| 0 | one | A |
| 1 | one | B |
| 2 | one | C |
| 3 | two | A |
| 4 | two | B |
| 5 | two | C |
I would like to convert this to
| foo | val1 | val2 | val3 |
|---|---|---|---|
| One | A | B | C |
| Two | A | B | C |
And the code I tried is:
pd.pivot_table(df1,index='foo',aggfunc=['first'])
But the above code is returning only the first value
472
Use the T attribute or the transpose() method to swap (= transpose) the rows and columns of pandas. DataFrame . Neither method changes the original object but returns a new object with the rows and columns swapped (= transposed object).
Pandas DataFrame: transpose() function The transpose() function is used to transpose index and columns. Reflect the DataFrame over its main diagonal by writing rows as columns and vice-versa. If True, the underlying data is copied. Otherwise (default), no copy is made if possible.
By using the Concept of to_numpy(). tolist method we can easily convert Pandas DataFrame into a list of 2d lists, by converting either each row or column. To do this first we have to create a list of tuples and then create a dataframe object 'new_val'.
Method #2: Using pivot() method. In order to convert a column to row name/index in dataframe, Pandas has a built-in function Pivot. Now, let's say we want Result to be the rows/index, and columns be name in our dataframe, to achieve this pandas has provided a method called Pivot.
Another approach to convert two column values into a dictionary is to first set the column values we need as keys to be index for the dataframe and then use Pandas’ to_dict () function to convert it a dictionary. This creates a dictionary for all columns in the dataframe. Therefore, we select the column we need from the “big” dictionary.
Stacking Horizontally : We can stack 2 Pandas series horizontally by passing them in the pandas.concat () with the parameter axis = 1. Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.
Frequency table of column in pandas for State column can be created using crosstab () function as shown below. crosstab () function takes up the column name as argument counts the frequency of occurrence of its values 1 ### frequency table using crosstab ()function 2
You can use the following syntax to transpose Pandas DataFrame: Let’s see how to apply the above syntax by reviewing 3 cases of: To start with a simple example, let’s create a DataFrame with 3 columns: Run the code in Python, and you’ll get the following DataFrame (with a default numeric index that starts from 0 as highlighted in yellow):
We can enumerate groups with groupby cumcount and use those as the pivot columns then add_prefix to the numerical values and reset_index to return the 'foo' values to the columns:
new_df = (
df1.pivot_table(index='foo',
columns=df1.groupby('foo').cumcount() + 1,
values='bar',
aggfunc='first')
.add_prefix('val')
.reset_index()
)
foo val1 val2 val3
0 one A B C
1 two A B C
See how df1.groupby('foo').cumcount() + 1 makes the columns:
foo columns
0 one 1 # First instance of "one"
1 one 2 # Second instance of "one"
2 one 3 # Third instance of "one"
3 two 1
4 two 2
5 two 3
Code to generate the above DataFrame:
demo_df = pd.DataFrame({
'foo': df1['foo'],
'columns': df1.groupby('foo').cumcount() + 1
})
Try:
df1.groupby([df1.groupby('foo').cumcount() + 1,
'foo']).first()['bar'].unstack(0).add_prefix('val').reset_index()
Output:
foo val1 val2 val3
0 one A B C
1 two A B C
Another solution:
x = df1.pivot("foo", "bar", "bar")
x.columns = [f"var{i}" for i in range(1, len(x.columns) + 1)]
x = x.reset_index()
print(x)
Prints:
foo var1 var2 var3
0 one A B C
1 two A B C
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
