Python make 5 row table with 5 columns a one row table with 11 rows

Question

I have a dataframe with 5 rows and 5 columns that I would like to make a new dataframe with 1 row. I have tried working with pivots but I do not need the agg and I dont know what to try next.

I used Scott Boston's answer from this link to get this done. How to combine multiple rows into a single row with python pandas based on the values of multiple columns?

Starting table

month	ID	state	sales	customers
0	1234	MN	44	23
1	1234	MN	45	25
2	1234	MN	51	27
3	1234	MN	47	29
4	1234	MN	44	29
0	5555	NY	441	231
1	5555	NY	451	251
2	5555	NY	511	271
3	5555	NY	471	291
4	5555	NY	441	291

data = [['0', '1234','MN',44,23],['1', '1234','MN',45,25],['2', '1234','MN',51,27],['3', '1234','MN',47,29],['4', '1234','MN',44,29],['0', '5555','NY',441,231],['1', '5555','NY',451,251],['2', '5555','NY',511,27],['3', '5555','NY',471,291],['4', '5555','NY',441,291],]

t1 = pd.DataFrame(data, columns=['month', 'ID', 'state', 'sales', 'customers'])

Ideal Final table

ID	state	sales_0	customer_0	sales_1	customer_1	sales_2	customer_2	sales_3	customer_3	sales_4	customer_4
1234	MN	44	23	45	25	51	27	47	29	44	29
5555	NY	441	231	451	251	511	271	471	291	441	291

I would need this to work for millions of IDs and months up to 50

You can use`df.pivot_table( index = ['month','ID'], columns=['state'], aggfunc='first').reset_index()`. — pythondumb, Dec 29 '20 at 18:32

Chiheb Nexus · Answer 1 · 2020-12-29T18:43:01.100

0

You can use itertools.chain module to flatten your data input then you can drop the columns you don't need.

Here is an example:

from itertools import chain
import pandas as pd


data = [
  ['0', '1234','MN',44,23],
  ['1', '1234','MN',45,25],
  ['2', '1234','MN',51,27],
  ['3', '1234','MN',47,29],
  ['4', '1234','MN',44,29]
]
columns = ['month', 'ID', 'state', 'sales', 'customer']

flattened = chain.from_iterable(data)
cols = columns + [
  f"{j}_{k}" for k in range(len(columns) -1) for j in columns
]
df = pd.DataFrame([list(flattened)], columns=cols)

columns_to_drop = ['month'] + [
  f"{j}_{k}" for k in range(len(columns) - 1) for j in ['ID', 'month']
]
df.drop(columns=columns_to_drop, inplace=True)
print(df)

Output:

     ID state  sales  customer state_0  sales_0  customer_0 state_1  sales_1  customer_1 state_2  sales_2  customer_2 state_3  sales_3  customer_3
0  1234    MN     44        23      MN       45          25      MN       51          27      MN       47          29      MN       44          29

edited Dec 29 '20 at 18:43

answered Dec 29 '20 at 18:32

Chiheb Nexus

9,104
4
30
43

This is really cool but if there is a 5 million IDs and 50 months for each the data list is going to be huge. – Jeffkrop Dec 29 '20 at 18:42
@Jeffkrop you're reading your data from a file or do you generate it under your process ? – Chiheb Nexus Dec 29 '20 at 19:56
Reading it from a text file output of a SQL query – Jeffkrop Dec 29 '20 at 20:01
Then you need to consider using `dask` – Chiheb Nexus Dec 29 '20 at 20:05
So this works with one ID and if the months always start at zero but testing it with more than one ID and it is looking for more columns not added the new ID to the next row. – Jeffkrop Dec 29 '20 at 21:06
@Jeffkrop This is another question for another answer. – Chiheb Nexus Dec 29 '20 at 21:11

Python make 5 row table with 5 columns a one row table with 11 rows

1 Answers1