Python converting the lists of lists to a dataframe with common columns and index

Question

My program generates data in each loop of a for loop function. I append to a list and finally I try to convert to the data into a single dataframe with common index and columns aligning automatically. However, I could not achieve this. My code:

biglist = []
# output of for loop in first iteration 
x1 = pd.DataFrame({'A':[11,22],'B':[33,44]},index=['x1','y1'])
biglist.append(x1)
# output of for loop in second iteration 
x2 = pd.DataFrame({'A':[110,220],'B':[330,440]},index=['x2','y2'])
biglist.append(x2)
# Now loop is over. Convert the biglist into a dataframe
df = pd.concat(biglist,axis=1)
print(df)

Present output:
      A     B      A      B
x1  11.0  33.0    NaN    NaN
y1  22.0  44.0    NaN    NaN
x2   NaN   NaN  110.0  330.0
y2   NaN   NaN  220.0  440.0
 
Expected output:
      A     B
x1  11.0   110.0
y1  22.0   220.0
x2  33.0   330.0
y2  44.0   440.0

UPDATE: My real problem is bigger. When I do pd.concat(biglist,axis=1), the output file size is [8 rows x 192 columns]. The 8 rows are perfect but 192 columns are repeated. But, when I do pd.concat(biglist,axis=0), the output file size is [64 rows x 24columns]. The 24 columns are perfect but 64 rows are repeated. Finally, the size of dataframe I am looking for is 8 rows x 24 columns. Here is the columns list.

You don't need to do biglist stuff and can just do: df = pd.concat([x1, x2], axis=1) — David Erickson
– David Erickson, Commented Nov 9, 2020 at 22:50
Does this answer your question? Pandas: append dataframe to another df — AMC
– AMC, Commented Nov 9, 2020 at 23:28

Marat · Accepted Answer · 2020-11-09 22:49:25Z

2

By default, pd.concat will stack dataframes vertically. Pass axis=1 to do it horizontally:

df = pd.concat(biglist, axis=1)

answered Nov 9, 2020 at 22:49

Marat

15.8k3 gold badges44 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mainland Over a year ago

This worked to some extent. You know what. Now I have unique index (like x,y in above in your solution) but repeatitive columns (A,B,A,B). This created a huge NaNs. My actual dataframe is big. So, what could be wrong here.

Marat Over a year ago

@Mainland do you have overlapping column names in the concatenated dataframes, and if (likely) so, what is the desired result?

AMC Over a year ago

@Mainland Please provide a proper minimal reproducible example that shows this.

Mainland Over a year ago

@AMC Now I have modified my qn with a better example to illustrate the exact problem I am facing.

Marat Over a year ago

@Mainland this is not really an MRE. It seems you have eight 8x24 dataframes and you want to get a single 8x24 dataframe as a result. So, each result cell has to combine 8 input cells, how do you want to do that?

|

Omni · Accepted Answer · 2020-11-09 22:49:45Z

1

You have to concatenate it along axis 1 by

 df = pd.concat(biglist, axis=1)
 print(df)

    A    B
 x  11  110
 y  22  220

answered Nov 9, 2020 at 22:49

Omni

1,0226 silver badges12 bronze badges

1 Comment

Mainland Over a year ago

This worked to some extent. You know what. Now I have unique index (like x,y in above in your solution) but repeatitive columns (A,B,A,B). This created a huge NaNs. My actual dataframe is big. So, what could be wrong here.

Collectives™ on Stack Overflow

Python converting the lists of lists to a dataframe with common columns and index

2 Answers 2

6 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related