How to partially transpose a CSV table in Python

transpose csv in python
python csv rows to columns
python3 csv transpose
python transpose 2d array
python transpose rows to columns
transpose matrix python without numpy
transpose of a matrix in python using numpy
pandas read csv transpose

I'm reading in a csv file with weekly sales data by store by product across many weeks and trying to partially transpose the data so that each row represents a weekly transaction by product, store, week

Going From This:

Product,Store,9/1/18,9/8/18,9/15/18,9/22/18
vacuum,123,1,5,3,3
toaster,456,5,7,4,10

To This:

Product,Store,Week,Sales
vacuum,123,9/1/18,1
vacuum,123,9/8/18,5
vacuum,123,9/15/18,3
vacuum,123,9/22/18,3
toaster,456,9/1/18,5
toaster,456,9/8/18,7
toaster,456,9/15/18,4
toaster,456,9/22/18,10...

I'm brand new to Python (2 days old) and I semi-understand a full transpose using zip_longest/reader/writer, but cannot figure out how to do a partial version

Also,

df.set_index(['Product','Store']).stack().reset_index()

Output:

   Product  Store  level_2   0
0   vacuum    123   9/1/18   1
1   vacuum    123   9/8/18   5
2   vacuum    123  9/15/18   3
3   vacuum    123  9/22/18   3
4  toaster    456   9/1/18   5
5  toaster    456   9/8/18   7
6  toaster    456  9/15/18   4
7  toaster    456  9/22/18  10

With cleaned up column naming,

(df.set_index(['Product','Store'])
   .rename_axis('Week', axis=1)
   .stack()
   .rename('Sales')
   .reset_index())

Output:

   Product  Store     Week  Sales
0   vacuum    123   9/1/18      1
1   vacuum    123   9/8/18      5
2   vacuum    123  9/15/18      3
3   vacuum    123  9/22/18      3
4  toaster    456   9/1/18      5
5  toaster    456   9/8/18      7
6  toaster    456  9/15/18      4
7  toaster    456  9/22/18     10

pandas: Transpose DataFrame (swap rows and columns), Also, df.set_index(['Product','Store']).stack().reset_index(). Output: Product Store level_2 0 0 vacuum 123 9/1/18 1 1 vacuum 123 9/8/18 5 2 vacuum 123 9/15/18  How to Transpose CSV/text File Python. If you want to transpose rows to columns in python of CSV or text file you can do it with method zip and two for loops. Let say that we have this file: And we want transposed output like: This can be achieved with this code example: First we are reading the file content row by row.

You can use melt to do this:

df.melt(id_vars=['Product', 'Store',],
                       value_vars=['9/1/18', '9/8/18','9/15/18','9/22/18'], 
                       var_name='week', value_name='Sales')

    Product Store   week    Sales
0   vacuum  123  9/1/18     1
1   toaster 456  9/1/18     5
2   vacuum  123  9/8/18     5
3   toaster 456  9/8/18     7
4   vacuum  123  9/15/18    3
5   toaster 456  9/15/18    4
6   vacuum  123  9/22/18    3
7   toaster 456  9/22/18    10

pandas - How to partially transpose a CSV table in Python, Here is what I did to achieve that: 1. Read the original csv file and convert it into a list of dicts 2. Transpose the list by grouping the name column  Additional keywords have no effect but might be accepted for compatibility with numpy. The transposed DataFrame. Permute the dimensions of a given array. Transposing a DataFrame with mixed dtypes will result in a homogeneous DataFrame with the object dtype. In such a case, a copy of the data is always made.

You can use df.pivot():

df.pivot(index='Product', columns='Store').stack(level=[1,0]).reset_index()

For example:

import pandas as pd

df = pd.read_csv('test.csv')

df = df.pivot(index='Product', columns='Store').stack(level=[1,0]).reset_index()

df.columns = ['Product','Store','Week','Sales']

Which gives:

   Product  Store     Week  Sales
0  toaster    456   9/1/18    5.0
1  toaster    456   9/8/18    7.0
2  toaster    456  9/15/18    4.0
3  toaster    456  9/22/18   10.0
4   vacuum    123   9/1/18    1.0
5   vacuum    123   9/8/18    5.0
6   vacuum    123  9/15/18    3.0
7   vacuum    123  9/22/18    3.0

Transposing csv data using Python, How to transpose a CSV table in Python. Transposing a CSV table switches the row and column indices. For example, the transpose of [[1, 2, 3], [4, 5, 6], [7, 8,  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).Note that depending on the data type dtype of each column, a view

How to transpose a CSV table in Python, What is the maximum possible value of an integer in Python ? Transpose a matrix in Single line in Python · Global and Local Variables in Python · Partial Functions​  CSV parsing is not easily done with POSIX tools only, unless you are using a simplified CSV variant with no quoting (so that commas can't appear in a field). Even then this task doesn't seem easy to do with awk or other text processing to tool. You can use Perl with Text::CSV, Python with csv, R with read.csv, Ruby with CSV, … (All of these

Transpose a matrix in Single line in Python, A CSV (Comma Separated Values) format is one of the most simple and common ways to store tabular data. To represent a CSV file, it must be saved with the  You can now export this back out as a csv and you’re done. Unpivoting Data With Python and pandas There are a few ways of achieving the desired result in Python but my current favorite is to use the “melt” function (in pandas 0.12) from the reshape module in Pandas. This is very similar to melt in the R reshape library.

Python CSV: Read and Write CSV files, DataFrame. to_csv (path_or_buf=None, sep=', ', na_rep='', float_format=None, columns=None, Write DataFrame to a comma-separated values (csv) file  I have a gene expression dataset in Excel/CSV file with 200 columns and 22,000 rows. What will be the best way to switch columns and rows. (This cannot be done in Excel as it supports maximum ~ 16,000 columns) I want my output as CSV file so that I can load it in Weka .

Comments
  • The 0 following 123 looks out of place...
  • Thanks rahlf23. Fixed that
  • I'm just reviewing first questions and don't know python but perhaps you can explain what you mean by a partial transpose, do you only want items? Or do you only want limit the number of rows? It feels like that extra bit of info may help someone better help you. Good Luck.
  • amazing answer :)
  • I have a suggestion, df.melt(id_vars=['Product', 'Store'], value_vars=df.columns[-4:], var_name='week', value_name='Sales')
  • @Yuca Great improvement not to type in the column names
  • You can replace .stack().stack() with stack(level=[1,0]) using level parameter for selecting levels in a multiindex header. And, add a "Waahkaa" (Bruce Lee) sound at the end.
  • I knew there was a way to avoid stack() twice. Passed the list but was missing the level keyword. Much appreciated, edited my answer.