How can I combine two single-column datasets into a single Pandas data frame?

Question

I'd like to import the Rotten Tomatoes Movie Review dataset into a single data frame. How can I combine two datasets that are 1-column strings into a text, label shape?

Here's where I'm at so far (you can duplicate in Google Colab) :

import os
import pandas as pd

# Reset
!rm -rf "rt-polarity.csv"

def fetch_rt_polarity_data():
# Fetch Data
if not os.path.isfile("rt-polaritydata.tar.gz"):
    !wget -q http://www.cs.cornell.edu/people/pabo/movie-review-data/rt-polaritydata.tar.gz
    !tar -xzf rt-polaritydata.tar.gz
    !mv rt-polaritydata/rt-polarity.pos rt-polarity.pos
    !mv rt-polaritydata/rt-polarity.neg rt-polarity.neg
    !rm -rf rt-*

# Format Data
df_pos = pd.read_csv("rt-polarity.pos", encoding='latin-1', sep="\t", lineterminator="\n")
df_pos = df_pos.reset_index(drop=True)
df_pos.columns = ['text']
df_pos['label'] = 1

df_neg = pd.read_csv("rt-polarity.neg", encoding='latin-1', sep="\t", lineterminator="\n")
df_neg = df_neg.reset_index(drop=True)
df_neg.columns = ['text']
df_neg['label'] = 0

# Combine
df = pd.concat([df_pos, df_neg], ignore_index=True)
df.head(10)

df.to_csv("rt-polarity.csv")

df = pd.read_csv("rt-polarity.csv", encoding='latin-1', sep="\t", lineterminator="\n")
return df

df = fetch_rt_polarity_data();
df.head(5)

Answer 1

I would import the datasets in pandas separately, mold them as you please, and then you can use the pd.concat function. This will assume that the instances are aligned by the automatically assigned index in pandas. If there is more data in one list than the other, the missing values will be NaN.

df1 = pd.DataFrame(data=[1,2,3])
df2 = pd.DataFrame(data=['a','b','c','d'])
dfs = pd.concat([df1, df2], axis=1)

If you have an index to link the text to the labels, then you can use the pd.merge function.

df1 = pd.DataFrame(data=[1,2,3])
df2 = pd.DataFrame(data=['a','b','c','d'])
dfs = df1.merge(df2, on='index')